Jump to content

Record Overlap


This topic is 2744 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

 

 

 

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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.

 

  • Like 1
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 by comment
  • Like 1
Link to comment
Share on other sites

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 by MacFileman
Minor update
Link to comment
Share on other sites

This topic is 2744 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.