Calculate only 45 days ahead

[Hawaii80]

I'm trying to create a report that looks at a tenan'ts lease end date to see who's lease will be expiring in the next 45 days.

 

I have a tenants table with related information for the tenant, in particular I am focusing on their lease end date field. I created another field on this same table called LeaseExpire and made a calculation as Get(CurrentDate) + 45 set to Date, unstored.

 

I then in the relationships graph created a duplicate of this table, (creating another occurrence) and called it upcoming_expired. I related the 2 tables as:

 

Tenants ----> upcoming_expired

 

Tenants::LeaseExpire >= upcoming_expired::lease end date

 

Using a portal, it shows upcoming expirations, but it goes past the 45 days showing also leases ending in 2009, which is not what I want. I only want to show anyone who's lease will be expiring within the next 45 days based upon the current date.

 

Should I create a script that performs a find and omits records vs. a calculation or am I just missing something in the calc? Thanks for the help.

[Weetbicks]

create a calculation on tentants which is unstored, and is basically Get ( CurrentDate )

 

then change the relationship you defined:

 

Tenants::LeaseExpire >= upcoming_expired::lease end date

 

to

 

Tenants::CurrentDate ≥ upcoming_expired::lease end date

Tenants::CurrentDate ≤ upcoming_expired::LeaseExpire

 

this will show all tenants whose lease end date has not already passed (ie it didnt expire yesterday, or a week ago...) AND whose expiry date is GOING to expire within the next 45 days.

 

So the crucial thing I think you've missed is simply incorporating current date into your relationship.

[Hawaii80]

create a calculation on tentants which is unstored, and is basically Get ( CurrentDate )

 

So now I have 2 fields on Tenants table one that is leaseexpire calc for Get (CurrentDate) + 45 and another field called CurrentDate and calc Get(CurrentDate).

 

then change the relationship you defined:

 

Tenants::LeaseExpire >= upcoming_expired::lease end date

 

to

 

Tenants::CurrentDate ≥ upcoming_expired::lease end date

Tenants::CurrentDate ≤ upcoming_expired::LeaseExpire

 

I did this, but what I get now is tenants that have expired only from January 31 to March 31 and nothing after. It still seems to not show only upcoming end leases from todays date and 45 days out.

[Hawaii80]

Hi Weetbix,

 

Well I'm not sure if I didn't understand you correctly or if it's just not working exactly the way I need.

 

I'm able to get the current date + 45 but I also am getting expirations that have already occurred from January through March this year as well which is not something I want. I could create a script to have it omit records that are prior to the current date which I guess would solve the problem, but I'm thinking that the calc should take care of it and that a script would not be necessary. Any other ideas? Thanks.

[Weetbicks]

Look at the attached example file, I've made the solution simpler and impossible not to understand (hopefully!)

[Hawaii80]

Thanks for the example. I wasn't using a Let statement but still had the calcs as get (currentdate) and get (currentdate) + 45. The difference I see may have been that I had it as a Date vs. Number? In either event it's working now. Thanks.