Finding Records using Operators
I am relatively new to Filemaker (FMP10). I work for a financial services company and we have a datbase of mortgage lenders and the relevant rate details.
I have two tables in the database - one called 'Rates' and one called 'AIP'. The idea is for the advisor to complete the relevant AIP fields and then Filemaker finds the relevant products that match on the 'Rates' table.
The problem that I am having is that I have worked out how to find the records that match on both tables (for example: the 'Country' and 'Currency' fields on the 'AIP' table matching 'Country' and 'Currency' fields on the 'Rates' table) but I cannot work out how to find records that satisfy a find using an operator (for example: 'Loan Amount Required' needs to be greater than'Minimum Loan Amount' and less than 'Maximum Loan Amount').
These are the script entries for the matching fields:
- Set Variable ($SearchCurrency; Value:AIP::Currency)
- Set Field (Rates::Currency; $SearchCurrency)
I have managed to work out the rest other searches etc - but I'm totally stuck on this one! If I manually do a find looking for say, products with min loan value < 100000 and max loan value > 100000 then I come up with the correct finds, but it just doesn't work with inputting the 100000 in the loan field on the 'AIP' table!!
Any help would be really really appreciated (please let me know if you need more information)