Lookup Table or script generating value based on conditions in four separate fields
Posted on 2006-04-12
I'm building a database in which one key function needs to generate a cost (in this case the cost of an advertisement). The cost is based on four distinct fields, "Region", "Size," Color", "Frequency". So for example, one might select "Northeast", "8x10", "4-Color" and "1X", which would return the rate of an advertisement for that unique selection. There are over 200 possible combinations. So far I've built a basic "if/then" script to calculate each possible option, but this method is cumbersome and prone to error, as the costs for ads are updated often and it would require going into the script and changing all the costs there, which would then change all the previous costs in the entire database.
I need a way to generate a script and a lookup table that can take these four determining values and go out and grab from a lookup table the correct cost; whereby it would add the cost to the field, but when the rates are updated not change those costs already assigned to clients. Make sense?
The script I wrote works well, but I can foresee it causing problems down the road when the database is up and running. I'd love to have Filemake access an external text file with the rates which would be the only thing the administrator would need to update, rather than have to start editing the values in the actual scripts.
Any advice?? Can Filemaker do this?