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

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 close merge delete

Sort by » oldest newest most voted

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.

more

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...

( 2018-01-09 00:50:34 +0100 )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

( 2018-01-09 00:54:08 +0100 )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....

( 2018-01-09 01:39:14 +0100 )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.

( 2018-01-09 01:41:23 +0100 )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.

( 2018-01-09 02:22:13 +0100 )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

pointing on :

lValue = oEvent.Source.Value

( 2018-01-09 22:29:12 +0100 )edit

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

( 2018-01-09 23:22:19 +0100 )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!

( 2018-01-10 00:24:06 +0100 )edit