search for date and other criteria

[crazybake]

My solution involves checking out and reserving equipment.

When a person attempts to checkout an asset, I perform find requests for:

1.) Set Field [LineItems::LIAssets_fK; Utility::GlobalAssetNumber] - looks for the asset to be checked out

2.) Set Field [LineItems::LineItemStatus;"<" & 2] - 1= reserved, 0= currently checked out

 

the find above works well, but I am dumbfounded as the the next step. Assets have different checkout lengths from 1 day to 1 quarter. So, I need to make sure that the date they are reserving or checking out does not match a current record - one of the found records from above.) I have the criteria to check for any date (including those in between):

 

The Utility table is my holding place for the criteria to evaluate. I tried to create a loop for the found records that would test:

If [utility::GlobalLineItemOutDate >= LineItems::LineItemDateOut and Utility::GlobalLineItemOutDate < LineItems::LineItemDueDate or Utility::GlobalDueDateCalc>= LineItems::LineItemDateOut and Utility::GlobalDueDateCalc < LineItems::LineItemDueDate]

...

The criteria above works, if there is a single record that meets the criteria. However, I am still struggling with the loop - so before I continue down that path, is there a way to create syntax that would include the date criteria in the original find request, so I do not have to perform an if/then on the found set? - can't wrap my brain around this.

 

 

 

 

 

[comment]

I got a bit lost in your description. If you want to find existing bookings that overlap the currently proposed booking, you could do something like:

Go to Layout [Bookings ]
Enter Find Mode []
Set Field [ Bookings::AssetID ; Utility::gAssetID ]
Set Field [ Bookings::StartDate ; "≤" & Utility::EndDate ]
Set Field [ Bookings::EndDate ; "≥" & Utility::StartDate ]
Perform Find []

Alternatively, you could use a relationship defined as:

 

Utility::gAssetID = Bookings::AssetID
AND
Utility::StartDate ≤ Bookings::EndDate
AND
Utility::EndDate ≥ Bookings::StartDate

 

to show any existing bookings that conflict with the currently proposed one in a portal.