using self join to create a conflict cather

Posted on 2003-03-07
Medium Priority
Last Modified: 2010-04-27
I’m hoping someone can help me. I’m trying to create a conflict catcher for a crew scheduling database, i.e. if the same person is selected to do two different jobs on the same day, a conflict warning will come up.

I’ve been trying to come up with something using a Self Join relationship based on booking date. But I can’t seem to make it work, either everything’s a conflict or nothing is.

Anyone have any ideas?

Question by:RMO
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Expert Comment

ID: 8105952
Assuming you only book crews one day at a time. (ie no date ranges) then a self join will work perfectly.

I don't know how you've got it set up, but there should be a table that tracks what each person is doing each day...

sheduletable: {crewid, date, task}

define a calculated composite key:
crewidDate = "crewid" & " " & date

define a self join between crewidDate.

then define a calc "conflictFlag" = if(count(crewidDate:crewidDate)>1,"Conflict", "No conflict")

you need to define a composite key because you don't want two different people to conflict if they work the same day. And you need to test for >1 so that it doesn't conflict with itself.

Author Comment

ID: 8114081
Thanks vux984, but as usual, I’m not doing a very good job of explaining the scenario.

 What I have is 10 different fields containing a possible 10 different personnel names. These names are derived from dynamic value lists based on job function from the personnel file (i.e. you get a list of all camera operators if you choose Camera from a  function list, all audio operators if you choose Audio).

The result is that I need to compare the names in all ten fields of the current record with the names in the 10 fields of records with the same date, knowing that Joe may be in the first name field in one booking, but in the second name field in the next booking, and so on.

I have created a field that contains all the personnel name fields. I have used that field via a self joining relationship, based on booking date, to create a value list of all personnel working on any given date, but using this value list doesn’t work because it contains the values of the current record as well. Nor do I understand how to compare this value list with the personnel field(s). A portal seems to have the same problems.

I should have done this differently to begin with, but I’m stuck with what I have for the moment.

Any further ideas?

Accepted Solution

vux984 earned 300 total points
ID: 8114268
you are quite right... it definately needs a redesign.  Using 10 fields that track the same information, especially where you want to derive infromation from the aggregate of those 10 fields, should be factored into a new file, with up to 10 related records for each parent record.

I strongly urge you to redesign the system.

(You might be able to get around it using multiline keys...
That is define a key with 10 lines, each line constisting of (field1-10  & " " & date) and do the self join on that. Its a poor design but it  should work. Basically it *should* return a match if any line in the parent key matches any line in the child key.

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question