AnsweredAssumed Answered

Issue in trying to capture values from Lookup Field

Question asked by Raymond on Nov 27, 2016
Latest reply on Nov 27, 2016 by philmodjunk

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

Outcomes