How can I update one table's field through a calculation based on another table without using exact values?
I'm currently using FileMaker 11.
There are 4 tables relevant to the issue: Order, Line Item, Part, and Pricing.
Order and Line Item are related through Order's serial number, __kp_Order and _kf_Order. Line Item is related to Part through _kf_Part and __kp_Part. Line Item is also related to Pricing through _kf_Part and _kf_Part.
Part contains fields for the part Name and Cost.
Pricing contains fields for the part's Cost, a Suggested Price, and a Ranking based on that suggested price. Pricing is in a portal inside Part's layout.
Line Item has fields for the part Name, Cost, Price, and Ranking. Line Item is in a portal inside Order's layout.
Each part will have several Rankings in its Pricing. For example, let's say Part A costs $1. If it's sold for the suggested price of $5 or more, it would be at ranking 1. If it's sold for $4 or more but less than $5, it would be at rank 2. If it's sold at $3 or more but less than $4, it would be at rank 5. If it's sold for less than $3, it would be rank 4. No, those ranks aren't typos; they're purposely not in order. Also, rank 4 is left unlisted in Pricing; it's simply known if you don't meet the requirements for rank 5, it's a rank 4.
What I want to happen is a Price will be entered in the Line Item and the Ranking will be automatically updated according to the values in Pricing.
I've tried setting up Ranking in Line Item to be an auto-enter calc like:
Price >= Pricing::Suggested Price; Pricing::Ranking;
In order for this to work, I had to add the additional relationship between Line Item and Pricing of Price >= Suggested Price. Even still, rank 4 never showed up because it wasn't there in the relationship, and if the Rankings were originally entered out of order in Pricing, it would skip one in the middle. I have several rankings entered out of order.
I've also tried something like:
Pricing::Ranking = 1 and Price >= Pricing::Suggested Price; 1;
Pricing::Ranking = 2 and Price >= Pricing::Suggested Price; 2;
Pricing::Ranking = 5 and Price >= Pricing::Suggested Price; 5;
This just returns either rank 1 or 4. The in-between ranks don't work.
I understand why these don't work, but I don't know how to set up something like "When Pricing::Ranking = 1 and Price >= Pricing::Suggested Price; 1"
I've done several permutations of things like this and tried several terms in Google but have come up short. Is there a way to do this?