6 Replies Latest reply on Apr 5, 2011 7:29 AM by BrianSwitzer

    stumbling with a relationship

    BrianSwitzer

      Title

      stumbling with a relationship

      Post

      I've been trying to figure out how to make this work.  I've looked around and I can't.  Here's the scenario.

      I have two tables.  The first is called "Properties" and details property insurance.  The fields of importance in this table are:

      1. Coverage Limit - a dollar value defining the limit of coverage for the policy
      2. k_propertyID - key value

      Each policy can have a variety of deductions applied to it.  These are all defined in another table called "_Property Deductions".  This table has the following fields:

      1. Description - what the user selects to apply the deduction
      2. Discount - a percentage discount to be applied to the premium
      3. High Value - works in conjunction with Low Value to determine which deduction is applicable (ie. "Properties::Coverage Limit" must be less than this number)
      4. Low Value - works with High Value (ie. "Properties::Coverage Limit" must be greater than or equal to this number)
      5. k_propertyDiscountID - key value

      The relationship needs to be many-to-many.  I've been trying to figure out how to use a join table to make this work.  My stumbling block is making the "Coverage Limit" field assist in determining which discount applies.  Ideally I'd like to set up a portal to the join table within which a user can select the "_Property Deductions::Description" they'd like to apply.  It's the relationship from the join table to the "_Property Deductions" table that requires a reference back to the parent of the join table to fetch "Properties::Coverage Limit".

      I could really use some assistance with this.  It's probably just something I'm not seeing.  Thanks to anybody who can help me out.

      Brian.

        • 1. Re: stumbling with a relationship
          philmodjunk

          I think this is what you need here:

          Properties---<PropertyDeduction>----Deductions

          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.

          • 2. Re: stumbling with a relationship
            BrianSwitzer

            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.

            Thanks.

            Brian.

            • 3. Re: stumbling with a relationship
              philmodjunk

              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.

              • 4. Re: stumbling with a relationship
                BrianSwitzer

                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.

                Brian.

                • 5. Re: stumbling with a relationship
                  philmodjunk

                  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:

                  Freeze Window
                  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 ]
                  End If

                  • 6. Re: stumbling with a relationship
                    BrianSwitzer

                    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.

                    Brian.