I think this is what you need here:
Properties::k_propertyID = PropertyDeduction::k_propertyID
Deductions::k_PropertyDiscountID = PropertyDeduction::k_PropertyDiscountID
But only if you can select multiple deductions for a given insurance policy. Please confirm this.
If so, then you should be able to place a portal to the join table, PropertyDeductions, to get what you want here.
Yes, I can have multiple discounts per policy. My stumbling block is the "Coverage Limit".
For example, one discount is called "Select Discount". The "_Property Deductions" table has two rows in it with this as a description because the amount of the deduction depends on the Coverage Limit. If 100,000 <= Coverage Limit < 130,000 then the discount is 5%. If Coverage Limit >= 130,000 then the discount is 10%. Pulling the Coverage Limit from the Properties table is what I'm having trouble with. The user should only have to specify the type of discount without worrying about the rule surrounding the amount of the discount.
Here's a trick that may solve the issue:
Define a calculation field in the Join table that references the Property table and returns the current value of its coverage limit field. You can then include this unstored calculation field in a relationship linking the join table to the deductions table to match only to the deduction option correct for that coverage limit.
If that doesn't work, we'll need to dig a little deeper so that I can give a more specific example of this trick. Please describe with some sample date how the two or more of these deduction records are entered in its table.
OK, I now have a small database which demonstrates this problem. I've tried the calculation trick above. It sort of half works.
Here's a link to my test database: TestDiscounts.fp7
Hopefully this will demonstrate what I'm attempting to accomplish.
Thanks for your assistance.
Hmmm, it appears the combination of multi-field relationships and lookups is not evaluating in the correct order. Didn't expect that here. I found I could get it to work if I added a script performed by an OnObjectExit script trigger attached to the Discount Type dropdown to force the update:
If [ not IsEmpty ( Properties_Property Discounts::Description ) ]
Go to Related Record [ From table: “Properties_Property Discounts”; Using layout: “Properties_Property Discounts” (Properties_Property Discounts) ] [ Show only related records ]
Set Field [ Properties_Property Discounts::kf_propertyID; Properties_Property Discounts::kf_propertyID ]
Set Field [ Properties_Property Discounts::Description; Properties_Property Discounts::Description ]
Go to Layout [ original layout ]
Hey, that's better. The only other thing I had to do was also add an OnObjectExit script trigger to the Coverage Limit entry as well so that if that number was changed the discount percentage would update. That is working very nicely.
Thank you very much for your assistance. I appreciate it.