Locating correct client price

[Simon UK]

I have created a std price table that contains a matrix of prices for each client as follows:

'ID/HouseType_BedroomsID/House Type/Bedrooms/Cleaning Price' for each client

When setting up a cleaning job I indicate house size and bedrooms and then wish for the std price to be automatically added to the job.

I'm thinking I need to ID how many std prices there are in the 'std price' table for the client in question and then loop through them checking each one to the corresponding unique

'housetype_bedrooms' ID in the job table, until I find correct one and then use 'set field' to the show correct price in the cleaning job table.

Would appreciate a starting hint for the script. I'll probably be able to work my way to completion but havbing trouble starting.

Many thanks,

Simon

[David Jondreau]

This isn't usually done by script but by relationship. Say you have two fields that determine the price, House Size and Number of Rooms. The most "correct" way to do this is to have a table of House Sizes, a table of Number of Rooms and a table of Prices. The Prices table would have a record for each combination of House Size and Number of Rooms records and the price of each.

In the Quote table, users would select a House Size and a Number of Rooms and you would use a relationship between the Quote table and the Prices table to filter out the correct Price.