One-to-many logic problem...

[moonunit]

Hello,

I'm stuck and need some pointers please.

Here's an outline...

I have two tables.

Table 1 details Tax rules and percentages, by country.

Table 2 is a series of records detailing income and on which some calcs are performed on the basis of table 1 info.

The tables are related by a short-form selectable country-code; table 1 country-code is defined as unique, so the relationship is one-to-many.

[Table 1 code = data specific to this code]

[Table 2 = includes multiple records based on occasionally the same codes, utilising the same rules.]

Two problems - at least...

1) I need to store the country-code specific formulae in text form [table 1] so I can see how the rule is contructed. [relatively easy]

2) and I want the (table 2) calc to reside here [table 1] as well [very difficult!]

Why?

Firstly, my table 2 calcs are getting extremely complicated so I'd prefer to locate them off-table for easier management.

Difficult? So far I can perform a look-up (from table 2) which gets the correct value in record 1, table 2, but not record 2, where the same record 1 answer appears when the country-code is the same...

I suspect that placing the table 2 calc in table 1 makes the calc happen once and the look-up only get's the first value...

My ideal solution is to somehow convert my text based table 1 calc into a useable calc in table 2...

It's possible that my logic is shot - and it's equally possible that I've explained myself badly. Sorry!

All help appreciated!

MU

[comment]

You could keep the FORMULA in the countries table, and have the calculation field in the transactions table evaluate it. It will be rather slow, though.

[moonunit]

Not sure that I've understood you correctly, Comment.

At the moment a calculation is in the transactions table... But the calc is becoming extremely bloated with multiple 'case' steps...

Finding some way of placing the calcs off-piste would certainly help their construction...

[comment]

See attached example.

Eval.fp7.zip

[moonunit]

Thank you Comment, evaluate appears to be the answer.

I need to have a play around and see what get's thrown-up as I attempt to employ it, but first go it looks great!

[i've never used "evaluate" before!]

MU