Finding records without a related record

[Peter Davis]

I've got two tables, one that stores Contact records and another that stores a list of scheduled Meetings. Each meeting is related to a single contact, and a contact can be related to multiple meetings. What I'd like to do is create some kind of layout that lists all of the contacts that don't have any meetings scheduled.

I come from an SQL background and running a join/select statement to do this would be pretty straightforward, but I can't figure out how to do it using FM. Any thoughts?

[CobaltSky]

Hello Peter,

How best to approach your problem depends somewhat on exactly how you have set up the one-to-many relationship - and you haven't actually said.

Assuming that the relationship is one in which a contactID field is stored as a foreign key in either a join table or directly in the meetings table, here's a method you might like to consider.

1. Create a value list called 'AssignedKeys' that uses all values from the contactID field in the related (join or meetings) table.

2. Create an unstored text calculation in the Contacts table called 'cAssignedKeys' with the formula defined as:

ValueListItems ( Get ( FileName ); "AssignedKeys")

3. Add an extra occurrence of the Contacts table onto your relationships graph (eg called 'UnassignedContacts') and create a relationship script between the Contacts table and the new occurrence of the Contacts table, which is defined as:

cAssignedKeys ContactID

4. Create a script or button which uses the command:

Go to Related Record [show only related records; From Table: UnassignedContacts; Using Layout: ContactList]

Each time you run the script (or click the button), contacts who currently have no meetings assigned will be found.

[Peter Davis]

Thanks very much for the help!