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.
Set Sums Insured
New Policy created
Change in policy
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.
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.