Determining availability based on several dates in portal records

[Daniel Shanahan]

I am having difficulty getting information based on date ranges in a portal.

I have an INVENTORY database where an inventory item can be rented many times:

INVENTORY -< RENTALS

One layout shows INVENTORY items in a form view. A portal shows the following fields:

RENTALS::rentalDate

RENTALS::dateReturnedExpected

RENTALS::dateReturnedActual.

I want to show whether the INVENTORY item is "Available" or "In Use", so I have a field in the INVENTORY table called status. The RENTALS table may have records in the past, present, or future:

Past: RENTALS::rentalDate and RENTALS::dateReturnedActual <= Get ( CurrentDate )

Present: RENTALS::rentalDate < Get ( CurrentDate ) and RENTALS::dateReturnedActual isEmpty

Future: RENTALS::rentalDate > Get ( CurrentDate ) and RENTALS::dateReturnedActual isEmpty

From the perspective of the INVENTORY table, I'd like to look through the relationship and determine if an inventory items is "Available" or "In Use". I believe I can use this simple rule:

RENTALS::rentalDate <= Get ( CurrentDate ) and RENTALS::dateReturnedActual isEmpty.

however, I'm not clear on how I would script or calculate this (the latter in the event that I use a calculated field).

It seems like it is a common enough problem. Yet, I've not been able to find a solution. Can anyone offer any insights?

Thanks.

[comment]

From the perspective of the INVENTORY table, I'd like to look through the relationship and determine if an inventory items is "Available" or "In Use". I believe I can use this simple rule:

RENTALS::rentalDate <= Get ( CurrentDate ) and RENTALS::dateReturnedActual isEmpty.

This would work only if the latest rental record is the first related record in the sort order of the relationship.

See also:

http://fmforums.com/...019#entry369019