Jump to content

Finding records without a related record

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

Recommended Posts

  • Newbies

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?

Link to comment
Share on other sites

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 notequal.gif 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. wink.gif

Link to comment
Share on other sites

This topic is 7103 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.