After getting temporarily desperate and researching and testing around the internet different solution I've found this to save the day.
Create a custom function like this:
myList = 0 & ¶ & myList & ¶ & 0 ;
it = Substitute( myList ; [ ¶ ; ";" ] ; [ ";;" ; ";" ] ) ;
result = Evaluate( "Sum(" & it & ")" )
It will sum up the values listed in the single field.
You can find more details here: http://www.briandunning.com/cf/1309
Yet what you have entered is very vulnerable to user data entry errors and overly complicated.
Simply setting up a system were each value can be entered into a different field of a related record will reduce the chance of data entry errors and results of ? when the data entered has an error and evaluate can't evaluate the resulting expression.
Hi Phil, thank you for replying.
Actually the fields you see in the screenshot are accessible only by me as admin. The values are normally placed by a script, users cannot manually access these fields.
Users input data only using a dialog box with a global field and the script place a note of their input in that field (first on the left). It's just to keep a simple history of inserted data and is not normally used for calculations.
The problem is that users are supposed to input one number at the time using the dialog box, but for some reason they started to input the whole line of numbers in the dialog box (eg.: 1+180+840-1).
At every input, the script add the quantity number to the total quantity field. That's why when users typed "1+180+840-1" in one time the calculation failed.
The above custom function worked well after I've managed to list the values in a temporary field (using "replace" and calculations, nothing by hand). But it was just a 1 time correction.
Now I need to find a way to let the first dialog box script to check the inserted number to be ONLY one number (or one negative number).
eg: "10" or "-10" only. Not "10+20+30-5".
I appreciate any help on this.
It is still a needlessly complex format and if your users mistype the expression to produce an invalid expression, you'll have a problem. Another problem that arises is that this format makes it very difficult to audit the data entered for possible user errors. (That's one of reason that bookkeeping ledgers were invented hundreds of years ago...)
You can set up a wide range of data entry forms for data entry--including the creation of your own form a custom dialog for data entry and each value entered should be stored--whether by script or by direct user entry into a series of individual records. The values can readily be summed via expressions such as Sum ( RelatedTable::NumberField) and your custom designed dialog can include validation expressions or a validation script that catches and rejects user errors such as entering multiple values into a single text field.
I understand your point and you are right. That's why I don't want them to type any expression in the dialog box (never meant to be for expressions).
I need to find a way to validate the custom dialog only if the input field has one number (which can be also negative) and proceed with the script.
Do you have any hint on this please?