moonunit Posted February 9, 2009 Share Posted February 9, 2009 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 Link to comment Share on other sites More sharing options...
comment Posted February 9, 2009 Share Posted February 9, 2009 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. Link to comment Share on other sites More sharing options...
moonunit Posted February 9, 2009 Author Share Posted February 9, 2009 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... Link to comment Share on other sites More sharing options...
comment Posted February 9, 2009 Share Posted February 9, 2009 See attached example. Eval.fp7.zip Link to comment Share on other sites More sharing options...
moonunit Posted February 9, 2009 Author Share Posted February 9, 2009 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 Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 5553 days old. Please don't post here. Open a new topic instead.
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 accountSign in
Already have an account? Sign in here.
Sign In Now