Record Overlap

[MacFileman]

Hey...

I am trying to make a calculation, not sure of the correct terminology here. I have a simple database where as I am tracking products. There is only one of each product. So, if a product is used on a specific date for an event, but a new event on the same day has the same product, I need a warning.

Record: 1234   Product: XYZ   Date: December 1st = Record: 1235   Product: XYZ   Date: December 1st 

This would create a warning "Product not available on that date"

 

I hope that made sense... I am stuck on how to go about doing that. I would think this is an easy calculation, right?

TIA

Mike

 

 

 

[Wim Decorte]

I would not go with a calculation for this, but rather roll this into the scripted workflow that creates a "booking" or "reservation".

When you do that it becomes a straightforward find in the booking table for that product on that table to see if one exists.  Can be a real FM find or an ExecuteSQL() call.

[comment]

You could define a self-join relationship along the lines of:

Bookings::ProductID = Bookings 2::ProductID
AND
Bookings::Date = Bookings 2::Date
AND
Bookings::BookingID ≠ Bookings 2::BookingID

Then if there are any related records, you have a duplicate booking.

However, as Wim points out, the question then becomes what to do when a conflicting booking is detected.

 

[MacFileman]

I like the feedback so far.... I am a little lost though. I am not booking a hotel rooms, this is actually very simple stuff. The odds there would be two events on the same day is remote, like 1 in a million. I still want the warning though.

Anyway, after some thought, I really just need a simple solution to get a warning if there are two events on the same day. I am not sure about how to compare information between records. What function or formula or calculation that would that be?

[comment]

1 hour ago, MacFileman said:

The odds there would be two events on the same day is remote, like 1 in a million. I still want the warning though.

The odds are immaterial. If you want to get a warning, you need to get it when it happens, no matter what the odds are.

Have a look at the attached file. It uses field validation to warn you before you commit a conflicting record. In addition, the Date field is conditionally formatted to turn red in case you override the warning.

Running a script triggered on record commit would be yet another option.

 

Conflict.fp7

 

Edited November 13, 2016 by comment

[MacFileman]

AWESOME!! Exactly what I needed to get my brain out of the gutter for direction and design. 

Please consider this post answered in full. And thanks to all who replied!!

Edited November 14, 2016 by MacFileman
Minor update