Jump to content
Sign in to follow this  

One-to-many logic problem...

Recommended Posts


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


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!


Share this post

Link to post
Share on other sites

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.

Share this post

Link to post
Share on other sites

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

Share this post

Link to post
Share on other sites

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


Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  


Important Information

By using this site, you agree to our Terms of Use.