script that deciphers formulas

[darno]

Ok... I got a weird calculation problem. I'm not sure if this is the right place to post, so sorry if it bothers someone. In addition, it is very tricky to explain...

I have a table where many "slots" are predefined, which are identified with a four digit number as primary key. Some are meant to be associated to values manually entered, which are stored in a related table. Some of the slots require a value to be associated and for the others, it's either optionnal or "forbidden". Each "Slot" has a field that mentions it by value list within a field of the "slots" table itself.

The slots that are "forbidden" are also meant to be associated with values the same way, but these values are determined by a calculation of the other values manually entered, and sometimes (for some of the optionnal ones), a calculation of the values manually associated with its own slot ID and other slots' values.

Can you copy? Doesn't matter... the following is the important part:

The problem is that these calculations can change, so can the slots' codes... so I don't want to create and update a new script for every "calculation slot". So I managed to create "instructions" for every slot: I added a "Formula" field inside the slots table which determines how the slot is filled.

As an example, the slot 2101' formula looks like that: (note: it's a text field)

(4503 + 3402 - 6111) / 5190

Now I was planning on creating one single script that would search every slot's formula (one by one, from the beginning), find the appropriate terms (search the values table that have the appropriate four digit code as foreign key) and enter the result into a new associated value. (the operators are only "+", "-", "*", and "/")

As an example, when the scripts would scan the slot 2101, it would have go see what values are associated with the slot 4503, add the values associated with 3402....... (Of course I do not have circular calculations)

Do you think it is possible that a script could decipher the for digit codes and the operators and know what to do with it ?

As I don't know how, I thought I could add an associated "calculation steps" set of related tables from the slots insteat of the formula field (example: another table would contain 4 numbered records with 2101 as foreign key linked to slots code, the first having a "step order" value of 1 and a "step instruction" of "+4503"...... and so on.

So the single script I would have to create would search every calculation "step instruction" for every slot. would it really be more simple? I am afraid it will take forever before I get a result as the slots list gets longer and longer... and the calculations need to be updated quite often.

Any advice or similar example will really be appreciated.

Thanks

Darno

[mz123]

for something like

(4503 + 3402 - 6111) / 5190

you can use the " Evaluate ( text ) " function which will do the math

so that

Evaluate ( Slot2101 ) would end up being the result of the math.

Is that what you're looking for?

[darno]

No, this would just do the math. I do not need to calculate the result as if the four digit codes used as keys were the numbers...

so the formula:

(4503 + 3402 - 6111) / 5190

would mean:

"the sum of values that have "4503" as a foreing key, plus the sum of values that have "3402" as a foreign key, minus the sum of values that have "6111" as a foreign key, all divided by the sum of all the values that have "5190" as a foreign key.

so that when the script is executed, the sum of values that have 2101 as a foreing key would be replaced by the result (by deleting all values referenced as 2101 and replacing them with the calculation result)

Edited August 1, 2006 by Guest

[Mikhail Edoshin]

You probably won't be able to use a simple related table of calculation steps, because you'll have no way to enter parenthesis. You can go without them if you use prefix or postfix notation (i.e. if you place the operation after its arguments, that is (4503 + 3402 - 6111) / 5190 would be 4503 3402 + 6111 - 5190 /, but this would look highly unnatural to most people.

I'd try to leverage the Evaluate() function anyway. The general outline would be that:

* Assume we need to calculate the formula for one record. We know that the formula contains only IDs (no numerical constants) and mathematical signs and that all signs are valid for the Evaluate() function. If we can replace the IDs with their respective values, we can use the Evaluate() function to get the result.

* So the task now becomes that: how to replace IDs of records in the formula with their values? We can extract the IDs one by one, find a value of each and replace the ID with the value.

* You probably won't be able to use nicely-looking functions like WordCount() or other word-related functions; instead you'll have to loop through a string character by character and find position and size of each ID. Once you have them, you can get the ID's value. Once you have the value, you can Replace( the formula, position of the ID, size of the ID, the value ). When you finished the last ID, you can Evaluate() your formula and cache the result.

* We assumed that we had to calculate only one record. If you want to recalculate all the records, you should probably empty all caches and when you see that the related ID's cache is empty (which means it's also has a formula and the formula hasn't been evaluated yet), you'll have to evaluate that related record first and then somehow return back to the first record. Since such a chain of evaluations can have several steps, you need to be careful here.

[darno]

You probably won't be able to use a simple related table of calculation steps, because you'll have no way to enter parenthesis.

That's why I managed to separate them in NUMBERED calculation steps...

You can go without them if you use prefix or postfix notation (i.e. if you place the operation after its arguments, that is (4503 + 3402 - 6111) / 5190 would be 4503 3402 + 6111 - 5190 /, but this would look highly unnatural to most people.

What I did is that I added a field "operator" to the calculation steps table, so that the codes don't have any prefix...

If we can replace the IDs with their respective values, we can use the Evaluate() function to get the result.

Wow! that said, I think I got it! This gave me an idea: From every Calculation Step entry, I can get the associated slots' sum just with an aggregate function... this means I can match every equation's code with its value. I just have to put it in a field of the calculation Step table and.... mmmmh not so simple;

So the task now becomes that: how to replace IDs of records in the formula with their values?

Actually, the task now becomes to "rebuild" an "evaluable" equation from the sets of calculation steps...

From what I have, I can probably easily build an equation that looks like the one you showed, but with the actual values instead of the codes

(How? I'm not sure... I am gonna think about it tomorrow! so if you have an idea by that time...)

And that would leave me with something like:

568+ 79956- 78+ 4/

(or: +568 -79956 +78 /4, keeping in mind that the parenthesis are missing, so it can't be evaluated the same way... and I don't care if they are unnatural for most people as long as I can get my result in a field somewhere!)

As the terms would always be in the right sequence, I'm sure there is a way to evaluate it... any Idea?

Will I have to build a custom function or is it possible via a normal calculation?

Finally, I will probably have no need of a script. It looks like everything is done by calculation, which is great because it always updates...

Edited August 1, 2006 by Guest

[Mikhail Edoshin]

You won't be able to handle all possible formula with a related table containing a number and an operator. Consider the following simple formula:

(a + * (c + d)

This happens because such formulas can only be represented by a tree, i.e. a structure that has one root node, containing some child nodes, each of which can also contain other nodes or be a leaf. Here leafs contain values and nodes are operators. The tree of the formula above is that:

*

 +

  a

  b

 +

  c

  d

Technically you can _write_ the tree in a line using the postfix notation so it won't look very much like a tree, but will still be a tree. The _postfix notation_ for the tree is that:

a b + c d + *

You can see this is essentially the tree written backwards from bottom to top (with maybe only the order of values mixed, but this is because I write it manually). Note that it differs from what you write. No matter how hard you try you cannot use unbreakable pairs of number & operator (i.e. records in your hypothetical table) to represent all possible calculation trees.

If you still want to represent a formula using a related table, the best idea would be to make it tree-like, i.e. hierarchical. Hierarchy itself isn't difficult to create, but it's quite difficult to create a usable hierarchy you won't hate after a week of data entry. Besides, this seems to be totally inefficient, because you already have a very good parser & calculator that handles all the common mathematical operations and lots of other operations too: the Evaluate() function. The only problem is that you cannot supply correct values to the parser.

To me the best tactics is to leverage the Evaluate() function. As far as I understand the related table does just one thing: it calculates “the value of an ID”. Ok, leave it there, but use it only as a list of "variables".

Here's your formula:

(4503 + 3402 - 6111) / 5190

Let's rewrite it using letters instead of numbers:

(A + B - C) / D

and provide an explanation (the related table)

Name: A, Value: 2123123 (sum or whatever of these "A" records)

Name: B, Value: 12

Name: C, etc.

Now look; you can make a calculated field in the related table:

"["" & Name & ""; " & Value & "]"

then get list of these values using either ValueListItems() or List() (FM8.5) or even GetNthRecord(). Then:

Let( dict = Substitute( ValueListItems( Get( FileName ); "Variable::Definition" ); "¶"; "; " );

Evaluate( Evaluate( "Substitute( Formula; " & dict & ")" ) ) )

and you're done.

Here's a sample: http://www.mytempdir.com/839808

[darno]

wow... feels like I'm getting close to a working result!

Did you build this sample just for me? And why do you set it to "unstore" ? And Why do you want the calculation result to be text and not number?

Ok, I get another little problem: when I build the value list, I get this error message:

"this value list will not work because the field "Definitions" cannot be indexed. Proceed anyway?"

What's wrong?

And another little problem I anticipate:

For now, all the calculations referred to the same Financial State (every sum of values is related to a Slot, but also to a corresponding Financial State; the Slots contains a global field that identify the "current" F/S), but in some of the calculations, the codes will refer to the previous financial state... so I thought I could identify this by appending a "p" to the code in the formula... is it a good idea?

Anyways, it's not really the issue that is discussed here....

Edited August 1, 2006 by Guest

[darno]

Aaaah!

This would work like a charm... but in your example, the values are inserted manually so you can make the value list... mine are summary field results so the value list does not work....

I can't see how to make it work!

Edited August 1, 2006 by Guest

[darno]

.... Actually, I do get a valid looking value list... Maybe it's another problem, but I did exactly the same and it returns "?"

here, I send you the whole thing... if you like, you can try to figure out what the problem is

calculationproblems.zip

Edited August 1, 2006 by Guest

[Mikhail Edoshin]

http://www.mytempdir.com/843784

There were two errors: the related "testfield" calc type was "number "(changed to "text") and the name of the value list was incorrect. You need to specify the name of the value list, not the related field. I often use identical names for both, but in your file the value list was named differently.

You might also want to check the following article about "dynamic substitution" by John Mark Osborne in the recent issue of FileMaker Advisor:

http://filemakeradvisor.com/doc/17898

This article is available for all readers, not just subscribers.

[darno]

Mmmmh! I should have seen these errors at first glance... instead, I was once again wondering from the left to the right what was wrong in my databases........ Thank you

I never used custom functions before... Will it work in the runtime application generated by Developper tools?

[Mikhail Edoshin]

You're welcome : Yes, custom functions will work.

[darno]

Yet another problem....

I can only get the result in the codes table. I would need the result to be in the sommes table since there is one result per code, per financial state... but it doesn't work and I still can't get why!

--> I just copied the result field to the sommes table... and it returns "?". So I changed "formule" for "Codes::Formule" in the calculation... but it still returns "?"

Do you want me to send you the file once again?

I think I am about to give it all up!

Edited August 7, 2006 by Guest