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

[woleev]

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.

[Ratslinger]

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.

[woleev]

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]

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

[Ratslinger]

@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]

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]

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.

[woleev]

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?

[Ratslinger]

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

[woleev]

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!