Effective Date

[smsaw]

I have a table that has expiry dates (think of it like video rentals). And of course a client table which it is linked to.

What I want to do is to show the effective date (you can say it's the closest due date). If I was to create an calculation field in the expiry table called effective_date, how should the calculation be?

Maybe I shouldn't be using a calculation field for this.

[aaa]

Hello, smsaw! If i understand you.

I think that in this table you must have fields:

DateBegin (When you give your video)

Days (How many days)

Expiry_Date=DateBEgin+Days.

[LaRetta]

Maybe I shouldn't be using a calculation field for this.

I'm still unclear on what 'this' is, smsaw!

But I'll take a go at it ... From what Point of View do you want to see this date result? From your client table? Do you want the closest due date PER CLIENT?

If so (and you have relationship from Clients to Expiry on ClientID), simply sort your Expiry table on the date field in your relationship. The 'closest', 'newest', or 'oldest' dates for that Client will become the FIRST related record (depending upon whether sorted Ascending or Descending). Then just put that Expiry Date field directly on your Client layout - no need for calculation at all.

LaRetta

[smsaw]

Sorry about not being clear. Let me explain what "THIS" is. It is a calculation field in the expiry table. I thought I should create a calculation field to find out which is the closest due date.

[LaRetta]

What I meant is that the resulting 'calculation' may NOT need to be a calculation at all. Example:

Client table. Client Name Bill Smith

Expiry table with multiple records as:

1/15/2005 Bill Smith

1/22/2005 Bill Smith

2/15/2005 Mary Jones

3/15/2005 Bill Smith

You can use a calculation with Max() or Last() to find that 3/15/2005 Expiry record of Bill Smith's. OR ... if you are on Bill Smith's record (in Clients) and want to display his latest Expiry date (3/15/2005), you can simply sort the relationship by that date. In above example (wanting 3/15/2005 on Bill Smith), sort the relationship in Descending order on Expiry date. That will make Bill Smith's LAST Expiry date the FIRST record in the relationship to Bill Smith. This FIRST Expiry record (3/15/2005) field can be placed DIRECTLY on Bill Smith's client layout. Poof!! You have his latest Expiry date and no calculation necessary!!

So again I ask, "WHERE do you want this date displayed? What is the PURPOSE of wanting this closest Expiry date?" Because a sorted relationship can sometimes replace these types of aggregate calculations.

LaRetta

[smsaw]

Let me say that I'm not really working with video rentals but Commercial Insurance renewal. A company typically have several policies and they don't always expire at the same time.

Let me add another data in your example:

1/15/2005 Bill Smith inc.

1/22/2005 Bill Smith inc.

2/15/2005 Mary Jones inc.

3/15/2005 Bill Smith inc.

11/20/2005 Bill Smith inc.

12/10/2005 Bill Smith inc.

when the user enter dates that are expired;

the year should be incremented because the expiration is annual:

1/15/2005 Bill Smith inc. > 1/15/2006 Bill Smith inc.

1/22/2005 Bill Smith inc. > 1/22/2006 Bill Smith inc.

2/15/2005 Mary Jones inc.> 2/15/2006 Mary Jones inc.

3/15/2005 Bill Smith inc. > 3/15/2006 Bill Smith inc.

11/20/2005 Bill Smith inc.

12/10/2005 Bill Smith inc.

The effective date would be 11/20/2005 because today we're 11/11/2005 and the next one to be renewed is 11/20/2005.

If today was 12/08/2005 then the effective date would be 12/10/2005 and 11/20/2005 should become 11/20/2006.

Tables are Client[clientID, Name, add, effective_date,etc]

Policy Table[renewal_date, policy, clientID]

There is already a portal in the CLient table showing all the records but the management demand for a field containing the effective date.

I hope this is clear.

[smsaw]

Can anybody help me with this problem?

I need to find the next expiry date from a repeating field (expire_date field).

expire_date

21/11/2005

22/11/2005 (today)

23/11/2005

the effective date field should contain the date 22/11/2005 because it is the next date that will be expiring.

I tried using the function Min() with the expire_date field but it only returns the earliest date which is past. I need it to return the earliest date up until today.

[comment]

This is not going to work with repeating fields. You need to break them up into separate records.

The attached demo shows the bare-bones technique. You might want to expand on that, so that each policy has its own renewal table (3 tables instead of 2).

NextExpiry.fp7.zip

[smsaw]

Thanks for the example, comment. I see that you create some kind of Table Occurence but I don't understand the relationship between Policies and ActivePolicies (cActivePolicyID = PolicyID). Can you explain more about this?

edit:

I should also mention that dates that are past/expired, don't really expire, the year gets incremented. But I haven't been able to do that yet. 10/10/2005 becomes 10/10/2006. Now that I think about this, it might be easier if I find a way to fix this first, that way I can just use the MIN() function.

Edited November 22, 2005 by Guest

[comment]

The relationship is based on a calculation field. Only if a policy is active, the field returns the policy's serialID. The relationship is to itself, therefore an active policy HAS a related record in ActivePolicies, while an expired one does not.

Another way to achieve a similar result, which might be easier to understand:

In Customers, define an unstored calculation field cToday (result is Date) =

Get (CurrentDate)

Connect the ActivePolicies TO directly to Customers, so that:

Customers::CustomerID = ActivePolicies::CustomerID

AND

Customers::cToday ≤ ActivePolicies::ExpiryDate

Define the relationship to sort ActivePolicies by ExpiryDate, ascending. Here too, the first related record is the next policy to expire.

I don't quite understand what you mean by "the year gets incremented". I think if a customer has a policy in effect from 10/10/2004 until 10/102205, then that is the policy. That record should not be modified - or you lose the ability to track customer's history. Instead, upon renewal, a NEW policy should be created (see my note above regarding 3 tables instead of two). I am quite sure that when I renew MY insurance, the insurance company sends me a new policy document. They don't come to my house to scratch the expiry date off my existing policy and fill in a new one in its place.

[smsaw]

Thanks for the prompt reply, I find your examples very helpful. Yes you are right, normally we wouldn't modify the year on the original policy but my database is actually the Prospect database where our sales reps would enter data for a company that is not currently a client of ours. Now you can see the use of incrementing a year on the date of the policy. If the policy is already past that means that it has already been renewed and the sales rep would rather know about the date of the next policy to be expired.

edit:

Now that I understand how you created the calculation field. I've managed to find the earliest date. That solves half the problem. The other half would be to increment the years of past expiry dates. When this is done I should always have the next expiry date that hasn't expired with the first calculation.

So far I came up with this:

If ( renewal ≥ Get ( CurrentDate ); renewal; Date ( Month ( renewal ); Day ( renewal ); Year ( renewal )+1)) and it seems to work.

Thank you all for your help!

Edited November 24, 2005 by Guest

[smsaw]

How do you make it so that the cActivePolicyID display ACTIVE instead of the real data? This is so interesting.

[comment]

In Layout mode, select the field and choose Number... from the Format menu.

[smsaw]

thanks!