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
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

569 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