filtering via date range

[madman411]

Hello Everyone,

 

I am trying to put together a booking section to my solution. Our assets table [system_assets] is provided in List View. I'm trying to put two global fields "start date" and "end date" in the header. The search will display the assets that are available within that date range.

 

Here's where I'm also failing:

 

Do I "track" the assets within a "tracking" table? I'm failing to create the right relationship to make this work.

 

Ideas?

[fmbug]

Hi,

 

Take two global fields in any of the table "gdate1" and "gdate2"... Suppose you want to filter dates in the field "date".

 

Put these two global fields in the header of the layout and select the dates using dropdown calendar functionality in both the fields.

 

Now in the script do the following steps:

 

set variable [$gd1 ; gdate1]

set variable [$gd2 ; gdate2]

Goto Layout [Layoutname]

Enter find mode []

set field [date ; $gd1...$gd2]

Perform Find []

 

 

Now you will get the result.

Note: Date range is always separated with "..."(3 dots) to define the range.

 

If you need any help, feel free to contact.

[madman411]

Thanks. Your way has given me an idea:

For conditional formatting purposes, I want to create a calculation that determines these two things:

1. Detect if current system date falls within the date range specified in any of the assets' related records (in Booking table). This would be the default.

And/or:

If the user entered a date range within the two global fields:

2. Detect if date range specified in the start and end globals conflicts with dates specified in any of the assets' related records (in Booking table).

I'm wanting to color code the rows of records based on the asset availability on that/those specific date/s.

[eos]

set field [date ; $gd1...$gd2]

 

Have you tested this?

Detect if current system date falls within the date range specified in any of the assets' related records (in Booking table). This would be the default.

Detect if date range specified in the start and end globals conflicts with dates specified in any of the assets' related records (in Booking table).

 

Here's one way to do this.

AssetAvailability_eos.fmp12.zip

[madman411]

Thanks, eos. I will be checking out your demo when I'm next at my computer.

Basically each booking record is set for a date range - StartA and EndA. I need filemaker to basically tell the user if said Asset is already booked and how long for, in addition to showing assets which ARE available within the required date range of the client.

I found a calculation comment put in another thread to detect overlapping ranges:

StartA ≤ EndB and StartB ≤ EndA

After solving this particular method, my next step is to provide the user with a calendar view.

I appreciate your help!

[madman411]

Hey eos

 

Can you explain to me what's going on in your "c" fields within the Control table?

[madman411]

I have a table displayed in List View which lists items in our inventory. Each one has a related booking record, which could go several months into the future. The user enters a date range in to two global fields and if the asset isn't available within that range the line turns red, using this formula:

 

_globals::g_bookingstart ≤ _camera_tracking::end_date and _camera_tracking::start_date ≤ _globals::g_bookingend

 

This works great if there's only one related record, or if the user entered date range conflicts with the newest related booking record.

 

However, like I said, there could be numerous records dated in the future which book that inventory item. I need the conditional formatting formula to reference ALL related records for that inventory item depending on the date range the user entered.

 

 

 

For example:

 

Today is 10/28/2013.

 

Asset "A" has booking records for 10/30/2013-11/05/2013 AND 11/10/2013-11/20/2013.

 

The record that would appear next to the asset is the 11/10/2013-11/20/2013 booking record as that is how FM is sorting the related records. If I enter 11/11/2013-11/15/2013 in to the global fields then the line turns red, however if I enter 10/31/2013-11/02/2013 then nothing happens, even though a record exists stating that Asset "A" has been booked for that period.

 

This may not be the ideal way, but the user can open the record in a new window and view all the active bookings for that particular asset. This is just my way of giving the user a visual reference without needing to open each record individually.

 

Is there a way to make a calculation reference all related records to do this?

[eos]

Can you explain to me what's going on in your "c" fields within the Control table?

 

– collect the IDs of all assets that have bookings records in range

 

– use this as a not equal predicate to collect the other assets, i.e. all available during the priod. (The zero is a dummy value, because if no assets are booked in range, you get an empty result, which you cannot use in the relationship to see all records.)

 

You can use the same method starting, adding an assetID to the relationship (e.g. on an assets layout, or adding a global field to specify an asset), to check on the availability of a given asset during the specified period.

[comment]

If you relocate the global fields to the Assets table, you can then define a relationship (using another TO of Bookings) as:

 

Assets::AssetID = Bookings 2::AssetID
AND
Assets::gStartDate ≤ Bookings 2::EndDate
AND
Assets::gEndDate ≥ Bookings 2::StartDate

 

 

Then it's a just a matter of testing whether any related records exist, e.g. =

IsEmpty ( Bookings 2::AssetID )

 

[comment]

If the user entered a date range within the two global fields:

2. Detect if date range specified in the start and end globals conflicts with dates specified in any of the assets' related records (in Booking table).

I'm wanting to color code the rows of records based on the asset availability on that/those specific date/s.

 

I have just now answered this in another thread of yours:

http://fmforums.com/forum/topic/90014-applying-conditional-formatting-that-references-all-related-records/

 

Please do not open more than one thread with the same issue.

[Lee Smith]

I have merged your two topics, Please do NOT multiple post your question.

 

If you have questions about this action, contact me by Private Message.

[madman411]

Sorry fellas. Thanks for your help.

[madman411]

 

Then it's a just a matter of testing whether any related records exist, e.g. =

IsEmpty ( Bookings 2::AssetID )

 

 

Hi Comment

 

Your solution works perfectly but I seem to have overlooked something you said. While testing it I noticed that booking records with dates in the future don't trigger the conditional formatting if there are older booking records for the same asset ID. I'm assuming your IsEmpty example would go in there along side the calculation?

 

Under conditional formatting, I have the following calculation applied to the asset information to turn the text red:

_system_inventory_assets::_asset_number = _bookings 2::asset_id and _system_inventory_assets::g_bookingstart ≤ _bookings 2::end_date and _system_inventory_assets::g_bookingend ≥ _bookings 2::start_date

For example:

 

Asset 001 has two related booking records: 10/29/2013-11/09/2013 and 11/12/2013-11/14/2013.

 

If I enter 10/28/2013-11/03/2013 then the line turns red, as it's supposed to. If I enter 11/11/2013-11/16/2013 then nothing happens. Have I overlooked something you said?

[comment]

I am afraid we may not be on the same page. If you have defined a relationship using the three predicates I have outlined in my post above, you can then use the expression =

IsEmpty ( Bookings 2::AssetID ) 

as the calculation for applying conditional formatting.

 

 

Repeating the predicates in the calculation is not necessary (and it won't work anyway since the calculation only sees the first related record in Bookings 2).

[madman411]

Oh ok. I understand now. I think I have failed in creating the relationship between the table occurrences correctly:

 

The "asset" table is connected to "Bookings2" with the relationship criteria you told me. Then the original "booking" table is connected to "bookings2" by the asset ID. Did I do that incorrectly?

[madman411]

Actually, I have joined the original Bookings table to the asset table via the AssetID separately. Adding a "not" to the IsEmpty function seems to have done the trick.

 

Thanks comment!

[comment]

Adding a "not" to the IsEmpty function seems to have done the trick.

 

not IsEmpty formats the booked assets. IsEmpty formats the complementing set of available assets. It's a matter of which half of the glass you want to concentrate on...