1 Reply Latest reply on Nov 27, 2016 10:15 PM by philmodjunk

    Issue in trying to capture values from Lookup Field

    Raymond

      I have a database called Shuttle which uses lookups to set values.

       

      First lookup is where the sum insured is selected from a drop down list  (List “0, 5000, 6000, 7000, 8000 and so on 30,000)

       

      This lookup sets the Sum insured in a Policy.

       

      The following lookup fields being LU Premium Field and LU Payable field selects the relevant premium and Payable figures for the selected Sums Insured.

       

      Example selecting a sum insured of $ 5,000 gives LU  Premium = $100 and LU Payable = $163

       

      General Information: An insurance policy has a start date (Inception)  and finish date (Expiry) and an effective date which is the date that the policy starts and is the date there is an alteration to the policy.

       

      To handle alterations,  I have introduce 2 new number fields “premium” and “payable”. These fields need to capture the value in the corresponding Look Ups fields.

       

      Unfortunately they break down when the sum insured of a policy is set to Zero. The below table shows the issue when the policy is Cancelled.

       

       

      Process

      Sum Insured

      Set Sums Insured

      Premium LU

      Payable LU

      Premium

      Payable

      Expiry

       

       

       

       

       

       

       

       

      $0

       

       

      $0.00

      $0.00

      $0.00

      $0.00

       

      New Policy created

      15/8/16

      $5,000

      $100.00

      $163.00

      $100.00

      $163.00

      28/8/17

       

       

       

       

       

       

       

       

      Change in policy

      23/10/16

      $8,000

      $131.25

      $213.00

      $131.25

      $213.00

      28/8/17

       

       

       

       

       

       

       

       

      Policy Cancelled

      20/3/17

      $0

      $0.00

      $0.00

      $131.25

      $213.00

      28/8/17

       

       

      The new fields will allow the premium field to be set by a script and allow prorate of the value in premium and payable to be set based on the unexpired days left in the policy.

       

      Example: 23/10/2016 has 309 days to 28/8/2017. the premium calculation from the script would be (131.25 - 100) x 309/365 = 26.46.

       

      Everything seems to work fine, except when I reset the Sum insured back to Zero. The premium and payable fields retain the last entries.

       

      Attachments are:

      The relationship Premium Lookup is as attached as LU Premium_New

      Premium Auto Enter Calculation is Premium Auto-Enter Calculation

       

      I would appreciate any help with this issue as I have reached a stale mate.

      Cheers,

      Raymond