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.

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