Show certain related record

[madman411]

Hi all

I'm trying to make a small application to track rental properties I have. Current issue is displaying the "active" tenant and lease terms.

Tables:

Property
Lease
Tenant

Tenant is linked to Lease by a LeaseID (so multiple people can be on the same lease, if necessary).
Lease is linked to Property by PropID.

On my Property form I have a portal to display the current 'active' tenants(s) and the lease terms. 'Active' can be defined as their moveinDate and moveoutDate. If today's date falls within that date range they and the lease are considered "active" and must be shown in the portal. No active tenants? Portal is empty.

Any suggestions or modifications to this are welcome. It doesn't need to be complex or fancy. I have a separate form that can show all tenant history. Right now I just want to display the active tenants and lease terms. Cheers!

[comment]

5 hours ago, madman411 said:

On my Property form I have a portal to display the current 'active' tenants(s) and the lease terms.

I find this somewhat confusing, because surely the terms of a lease are in the Lease table, while a portal showing the active tenants would be a portal to the Tenant table?

If you're a looking for a simple way to display only active tenants, then add a calculation field cIsActive (result is Number) to the Tenant table =

Lease::moveinDate ≤ Get ( CurrentDate ) 
and 
Get ( CurrentDate ) ≤ Lease::moveoutDate

then filter the portal to Tenant using the expression =

Tenant::cIsActive

(I am assuming that moveinDate and moveoutDate are fields in the Lease table and apply equally to all tenants of that lease.)

This should work well as long as you don't have properties with massive amounts of leases and tenants. Otherwise you will have to add another relationship chain to your graph and filter it instead.

 

Edited July 20, 2021 by comment

[madman411]

On 7/20/2021 at 7:38 PM, comment said:

I find this somewhat confusing, because surely the terms of a lease are in the Lease table, while a portal showing the active tenants would be a portal to the Tenant table?

 

That is correct. I failed to include that information in the original post.

I had come to a similar solution prior to reading this - but it's pretty much identical! Thanks, comment.