Ask Your Question
0

[base] Record as negative the (absolute) input value

asked 2018-01-06 21:10:56 +0200

woleev gravatar image

updated 2018-01-06 21:16:09 +0200

Hi everybody!

In an input Form, is there a way to have a numerical field which records as negative the given value ?

In the Table "Stock", I have "ID", "ProductID" and "Amount" columns.

In the Form "Delivered" I put the delivered quantity of a selected product.

In the Form "Consumed" I would like a Field where to give the absolute value of the dedicated Product, which will be recorded as negative.

It is important for me NOT to have to type the minus. I am thinking of having 2 columns in the Table (Amount IN, Amount OUT), but this solution doesn't really satisfies me.

After 2 days of (kind of intensive :) search I could not find it out... Although it seems to be simple.

Any idea? What am I missing?

Thanks, Wo.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2018-01-07 03:52:55 +0200

Ratslinger gravatar image

updated 2018-01-07 21:46:09 +0200

Hello,

Yes this is easier than you may have thought except for the fact it doesn't work correctly (may be a bug). You should be able to use a Pattern field to do this easily. While this works in most cases, if the literal is first in the field (as needed here) it requires you to 'right arrow' one position to start entry and this, based upon your requirements, defeats the purpose. Just for completeness, the Edit Mask would be LNNNN (based upon four digits) and the Literal Mask is -____. Again it works but requires the extra keystroke.

The other solution I can offer is a simple macro to just multiply the entry by -1 if the entry is positive. Here is the code:

Sub ChangeValueToNeg
    Dim oForm As Object
    Dim oField As Object
    Dim lValue As Long
    oForm = ThisComponent.Drawpage.Forms.getByName("YOUR_FORM_NAME") 'Get Form'
    oField = oForm.getByName("YOUR_FIELD_NAME")
    lValue = oField.Value
    if lValue > 0 Then
        oField.Value = lValue * -1
    EndIf
    oField.commit()
End Sub

You can use this with a numeric field and attach to the fields' Text Modified event. Then anytime something is changed in the field it will result in a negative value.

If this answers your question please tick the ✔ (upper left area of answer). It helps others to know there was an accepted answer.

edit flag offensive delete link more

Comments

Hello Ratslinger,

Thanks for your prompt answer ! The patterned field option is effectively not fitting my needs for two reasons : the one you mentioned (which I could live with, as the purpose of this field is to avoid "forgetting" to make the entry negative), and the fixed length of the number. (I could have once 200, 2000 the next time). Note : define a value max to 0 turns a positive value to 0 without any error dialog, which could be overseen in a certain rush...

woleev gravatar imagewoleev ( 2018-01-09 00:50:34 +0200 )edit

As I am an absolute newnewbie with macros, I need more time and research to get yours running. A quick try didn't had success :

BASIC runtime error. An exception occurred Type: com.sun.star.container.NoSuchElementException Message: ..

I will edit asa I get that on the rail, or not!

Note 2 : in the form are actually 7 to 10 such fields, and in the base 7 of such forms, what leads to an amount of +- 60 scripts. Is that ok for the health of the base?

Regards

woleev gravatar imagewoleev ( 2018-01-09 00:54:08 +0200 )edit

@woleev As for the pattern field, the number of digits is flexible. The problem is the cursor movement to get around the negative literal.

As for the macro, the problem is you didn't replace the Form and Field names in the macro. The number of macros you specified is not significant. However, I have re-written the macro to make it much easier for you. With this macro ALL fields needing this can point to the same macro without problems. See next comment for replacement....

Ratslinger gravatar imageRatslinger ( 2018-01-09 01:39:14 +0200 )edit

New Sub:

    Sub ChangeValueToNeg(oEvent)
       Dim lValue As Long
        lValue = oEvent.Source.Value
        if lValue > 0 Then
            oEvent.Source.Value = lValue * -1
        EndIf
        oEvent.Source.Model.commit()
    End Sub

Just attach to Text Modified event as before & for all needed numeric fields on all forms in same Base file.

Ratslinger gravatar imageRatslinger ( 2018-01-09 01:41:23 +0200 )edit

Also noted you comment as to max value. Had me confused since you were talking of the pattern field. It seems you suddenly jumped to a different subject. Please keep in mind, when either party is dealing with these messages (comments, answers) it is only known what is in print. Too many times there are other factors involved which the other party thinks is something already known. A good example is your comment about the number of fields involved. If known from the start then no re-write needed.

Ratslinger gravatar imageRatslinger ( 2018-01-09 02:22:13 +0200 )edit

Sorry for that, I will work on more precision from now. The second Sub you offered seems to be exactly what i need, but i get a

BASIC runtime error. Property or method not found: Value.

pointing on :

lValue = oEvent.Source.Value

Do you have an idea? Need more infos?

woleev gravatar imagewoleev ( 2018-01-09 22:29:12 +0200 )edit

Are you using a Numeric field as stated in my answer?

Ratslinger gravatar imageRatslinger ( 2018-01-09 23:22:19 +0200 )edit

Uj, I was not! And now it works! A huge THANK! Being convinced that a field is "numeric" does not make it "numeric". Feeling embarrassed and liberated at the same time... Regards!

woleev gravatar imagewoleev ( 2018-01-10 00:24:06 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-01-06 21:10:56 +0200

Seen: 74 times

Last updated: Jan 07 '18