1 2 Previous Next 16 Replies Latest reply on Jun 16, 2014 5:04 AM by Porpoise

# n-m (indirect) self-join

### Title

n-m (indirect) self-join

### Post

HI,

I'm struggling with the following problem:
Table T (ID; name): (1; "John"), (2; "Mary"), (3; "Peter"); (4; "Jane")

I have a second table that indicates who knows who, e.g. Mary knows John and Peter.
The relation is symmetrical, so if Mary knows John, John will know Mary.
On a form layout for table T that shows Mary, I need a button that stays within the same layout,
but finds all the people Mary knows (so John and Peter).

I tried a table R1 (ID1; ID2): (2; 1), (2; 3) with all person pairs that know each other,
and I tried a table R2 (id; ID): (a; 2), (a; 1), (b; 2), (b; 3) where id indicates the pair number.
But both solutions seem pretty clumsy and I can't really get it to work.
Any help is welcome. Thanks in advance.

• ###### 1. Re: n-m (indirect) self-join

T----<R>----T2

T::ID = R::ID1
T2::ID = R::ID2

Where T2 is a table occurrence with the same data source able as T.

A  portal to R would be used to create links between two existing records in T, with fields from T2 included to show additional data such as the name of the person so linked.

This is a "one way" relationship from T to T2. To "back link" the relationship so that linking Peter to Mary also links Mary to Peter, you'd need to create a second record in R with the values in the ID fields swapped.

R ( ID1 ; ID2) = R(3 ; 2) is the link from Peter to Mary. R ( 2 ; 3 ) links Mary back to Peter.

It's possible to set up a script trigger that automatically generates the second linking record in R to enforce the "bi-directional" links that you have specified.

• ###### 2. Re: n-m (indirect) self-join

That is indeed one of the solutions I tried. R is in a portal on a layout for T. So I hoped I could use OnObjectExit on the portal to trigger a script to create/update/delete the back link.

Unfortunately, I am also using the solution you provided in https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12.
Whenever the layout record is loaded (OnRecordLoad for the layout), I loop through the portal rows to update the selectContact field for each row. But that triggers the OnObjectExit script.

And to update the back link with the OnObjectExit script, I switch temporary to another layout, so when the script switches back, the record gets reloaded which triggers the OnRecordLoad script.

I know I can 'disable' the trigger script by setting a global variable \$\$TriggerScriptDisabled = True, and putting the whole trigger script within a "if [not \$\$TriggerScriptDisabled] ... end if". But as I wrote, it seems so clumsy, and I wonder if I'm not overlooking a more appropriate script trigger.

• ###### 3. Re: n-m (indirect) self-join

Whenever the layout record is loaded (OnRecordLoad for the layout), I loop through the portal rows to update the selectContact field for each row. But that triggers the OnObjectExit script.

You can loop through related records without looping through portal rows. Use Go To Related Records to pull up the records on a layout based on the portal's table, loop through them there ( or use a single replace field contents with the calculation option to do it without a loop) and then return to your original layout. Or you can continue to loop through the portal rows, but use the same \$\$TriggersOff method to keep this from performing other scripts.

it seems so clumsy,

I'm not sure why that seems clumsy to you.

But it is possible to loop through values instead of records in a script and such a method could be used to modify the data in your portal records without looping through portal rows or changing layouts.

Consider this relationship:

LayoutTable::PrimaryKey = PortalTable::ForeignKey

list ( PortalTable::PrimaryKey) will return a list of unique Ids for each related record in the portal.

Ir you then define this added relationship

LayoutTable::SelectedRecordID = PortalTable|selected::_pkPrimaryKey

You can set a variable to the list of primary keys and loop through them with this code inside your loop:

Set Variable [\$Counter ; value: Counter + 1 ]
Exit Loop If [ \$Counter > ValueCount( \$IDList ) ]
Set Field [LayoutTable::SelectedRecordID ; GetValue ( \$IDList ; \$Counter ) ]
Set Field [PortalTable|Selected::field ; //put value to update field here ]

• ###### 4. Re: n-m (indirect) self-join

Looping through portal rows is fine, and Go To Related Records and Replace Field Contents are great. I am sure part of my problems stem from the fact that I am new to Filemaker, and have to get used to it's paradigms. i have used more programming languages than I care to remember, so I know that there is more to becoming fluent than just learning the syntax. Your suggestion to loop through values and use an extra relationship is briljant, and typical of a much deeper understanding of the Filemaker paradigm then mine. But still, there are so many moments of "What were they thinking!?". We discussed the lack of variables with a window scope before. Two problems I got now are:

1. Before displaying the tab with the portal with relations, I need to update the field selectContact on every portal row. As you pointed out, I can use GTRR and RFC for that. As a trigger I can use OnPanelSwitch, but that will trigger before the switch is actually made. So I tried OnObjectModify. But (like OnPanelSwitch) this doesn't get triggered when a new layout record is loaded without tab switching. That leaves OnRecordLoad. So the portal rows are updated even if the relevant tab never gets selected and the portal never is displayed. Not pretty, but I can live with that. But the next problem causes too many record loads.

2. From the portal with relations I would like to select a contact and get a found set with all the related contacts. The selected contact should be the active record. This seems perfect for GTRR. But GTRR acts a bit different when called from a portal row, especially if the portal table is not the same as the related table. If I use 'Match current record only', the found set only includes the contact that matches the current portal row. That makes perfectly sense. It would only seem logically to expect that 'Match all records in found set' when called from a portal row, will include all contacts that match any of the portal rows. Nope. This still matches the entire found set of the layout table. So it seems I have to use a Perform Find and loop through the found set to find the selected contact and make it active. But that means several record loads that each trigger the aforementioned update.

The big outline is very fast to setup in Filemaker. But a lot of my productivity is lost again with this kind of excruciating details. Thankfully, your help has been wonderful. I hope you have a good solution for these two problems too. Thanks in advance.

• ###### 5. Re: n-m (indirect) self-join

I solved the second problem myself. The solution was to chain two GTRR's. The first one finds the relations in R, essentially the same records that are in the portal. The second goes from there and find the related contacts in T2.

On to the next challenge. I use OnObjectExit on the portal to detect when a relation might have changed, so the reverse relation might need to be updated. I would like to avoid leaving the portal or layout, so I don't have to update selectContact for each row. I created an extra TO, reverseRelations, that is linked to the layout contact by T.selectreverseRelation = reverseRelations.PrimaryKey and T.PrimaryKey = reverseRelations.ForeignKey2. Now I can access a reverse relation without leaving the portal or layout. For updates this works fine. But can I create or delete records in a related TO?

In most programming languages I am used that there are myriads of ways to do things. As you learn, you find easier, quicker, more elegant solutions. In Filemaker  there seem to be so many dead ends. Please tell me it's just me. :-)

• ###### 6. Re: n-m (indirect) self-join

The solution was to chain two GTRR's

Or just do a GTRR to the second table. GTRR can "tunnel through" the intervening table to the related set of record one occurrence "away" from your layout's table occurrence.

WIth regards to 1, I can't parse all of the details in your description of the issue.

a) Why does the fact that the change in tab panel create an issue? Why do you have to perform this script before the change in tab panel? (And you can use freeze window to keep the data shown in the window from updating until the script ends.) And why do you need to set this value at all? a) what is the purpose to that? and b) if you are setting the same field in large numbers of records in a table it may be that you should actually be setting a single value (or in some cases, a list of values) in a related table to which all of those records link.

• ###### 7. Re: n-m (indirect) self-join

Sorry for the long post. I am trying not only to describe my Filemaker file, but also the process that brought me there. At some point it starts to get convoluted. To manage complexity, programming languages should offer modularity and details (methods, data) of a module should be hidden outside that module except for a well defined interface.

It started really simple. I had a table of contacts, Contacts (kp_Contact, ContactName), and a table of companies, Companies (kp_Company, CompanyName). The relations I'm interested in, are that contacts might know each other, and that contacts might work for one or more companies. So I added two join tables, Links (kp_Link, kf_Contact1, kf_Contact2), and Jobs (kp_Job, kf_Contact, kf_Company). The relations are straightforward, with a second table occurrence Contacts2.

As for the layouts there is Companies with a portal for the employees (actually, Jobs). And there is Contacts with a portal for the acquaintances (actually, Links) and a portal for the employers (actually Jobs), each on a separate tabpanel.

For navigation there is a Goto-button behind each portal row. Clicking on Goto behind an employee X on the Companies layout for company Y should switch to the Contacts layout with contact X the active record, the other employees of company Y as the found set, and the tabpanel with employers should be in front, with the portal row for company Y active. Clicking on Goto behind an employer or behind an acquaintance should do something similar. This seems perfect for GTRR. But while GTRR can tunnel through the join table, the choices "Match current record only" and "Match all records in current found set" do not take the portal in consideration. I need a "Match all records in current portal". So that is the reason for the chain of two GTRRs. And we need extra layouts for Links and for Jobs too. Starting again from employee X of company Y, the first GTRR finds all jobs at company Y (Match current record only), with the job of employee X active. These are the jobs that were displayed on the portal. The next GTRR finds all contacts working in those jobs (Match all records in current found set). It isn't too bad but it adds extra layouts and extra layout changes.

To enter a new Link or Job, I don't want to bother users with the primary keys, but prefer an easy dropdown list of Contact names or Company names. Things get a bit more complicated with extra TO's: selectCompany, selectContact.for.Jobs and selectContact.for.Links. And extra fields selectCompanyName and selectContactName in table Jobs, and selectContactName in table Links. selectCompany is related to Jobs by Jobs::selectCompanyName = Company::CompanyName. In fact, selectCompanyName is the only field to be seen on the Jobs portal in the Contacts Layout. A user can select a name from a dropdown list of all CompanyNames. kf_Company is filled in by looking up the primary key in TO selectCompany. This will be the key that matches the selected company name.

Now it is necessary to update the selectCompanyName field whenever the Jobs portal gets displayed, as the name might have been changed in the Company table. I believe the correct script triggers are OnObjectModify for the TabPanel in case the user has switched to the tab with the Jobs portal, and OnRecordLoad in case the user loads a new Contact record. In that case the update is only necessary when the Jobs tab is in front. So the script checks which tab is in front, goes to the portal on that tab and does a Replace Field Contents. selectCompanyName in the table Links is updated with the CompanyName from the table Company. And we do something similar for the portals with contacts.
(BTW I haven't tested yet whether RFC works as I hope on a portal. It isn't documented.)

The next level of complication arises from the fact that for each new record (Key, Contact1, Contact2) in table Links, I need a second record (Key, Contact2, Contact1) in this table. This reverse link has to be kept in sync with the base link. I am struggling with the right triggers to use for creation, update and deletion of the reverse link. And the script to handle the reverse link housekeeping should preferably avoid tripping the script that updates the selectCompanyName and selectContactname fields.

It's starting to get late here. I'll try to add this last part tomorrow. Is there a way to upload a demo file? That might be clearer.

• ###### 8. Re: n-m (indirect) self-join

For navigation there is a Goto-button behind each portal row. Clicking on Goto behind an employee X on the Companies layout for company Y should switch to the Contacts layout with contact X the active record, the other employees of company Y as the found set, and the tabpanel with employers should be in front, with the portal row for company Y active.

That is exactly what should happen when you click a GTRR button or a button that performs a script with GTRR when the button is placed in the portal row.

But while GTRR can tunnel through the join table...

That isn't necessary, Just pull the button out of the portal row and when it tunnels through to the third table's layout, you'll get a found set exactly the same as your two stage GTRR does now. You'll get a found set all the "grandchildren" of all the "chldren" listed in your portal on the "parent" layout. If you put the same exact button inside the portal row of this portal to "chldren", you'll get just the "grandchildren" linked to the Child record whose portal row contained the copy of the button that you clicked.

See this demo file: https://dl.dropboxusercontent.com/u/78737945/ParentToGCGTRRtest.fmp12

GTRR is in many ways, a "horse designed by comittee" (ie a camel). See this thread for more one this complex script step: The Complete Go To Related Record

• ###### 9. Re: n-m (indirect) self-join

Excellent posting about GTRR. Thanks! This kind of documentation is sorely needed.

I copied the Goto-button off the portal. There are two records in the portal. If focus is on one of these records, the Goto-button brings me to this record, but the found set is just this one record. This is the same as when the Goto-button is on the portal row.

If focus is not on the portal, the found set will be both records, but the first one will be the active one. (There is no portal records selected, so there is no way of telling which records should become the active one.)

So this is not the same behavior as the two chained  GTRR. Now both records will be in the found set (like when the Goto-button was not on the portal row and focus was not on the portal) _and_ the record that had focus in the portal will be the active one (like when the Goto-button was on the portal row or focus was on the portal).

It makes no sense to me that the found set is different depending on whether a portal row had focus or not. And it is probably confusing for end users too.

• ###### 10. Re: n-m (indirect) self-join

There are two records in the portal. If focus is on one of these records, the Goto-button brings me to this record, but the found set is just this one record. This is the same as when the Goto-button is on the portal row.

That's a nasty surprise that I didn't expect, but if you take the GTRR button and set it to perform a script that contains the GTRR, you can use Commit records before the GTRR to release the focus on the field inside the portal.

While I didn't expect this, it IS consistent with FileMaker interface behavior--namely that clicking a button does not put the focus on the button. This was done, as far as I can tell, so that script steps in a script performed by clicking a button can modify data in any one of a number of different fields by modifying the field that currently has the focus. That's the main purpose behind most of the script steps that start with the word "insert".

• ###### 11. Re: n-m (indirect) self-join

Yes, Commit Records can be used to release the focus on the portal prior to the GTRR. So the found set will consistently be based on all the records from the portal. But that was just one of my two requirements. The other being that the portal row that had focus before the GoTo-button was pressed, will decide which record will be active. So it is still no substitute for the two chained GTRRs.

With the application that started out so simple, I ran against a few problems that I'm unable to solve properly.

1. As described a 'reverse link' record is necessary in the join table. To create or update this reverse link I use a script that is triggered by OnObjectExit on the portal. For now this works fine. But when a link is deleted, the reverse link has to get deleted too. What trigger can be used for that? The script has to get access to the data in the (to be) deleted link, in order to find the reverse link. Or I could use a global variable to save this information, but that would mean the user can't use this layout for multiple windows.

2. When a name is entered in selectContactName and this field gets saved, the link fields are filled in automatically. The primary key is assigned by the auto enter serial number. The foreign key for contact1 is automatically the primary key of the layout record And the foreign key for contact2 is assigned by an auto enter lookup from table selectContact::kp_Contact. All according to the nice example you gave in another posting. However, there are some validation steps I need, like checking for duplicate links and for 'self links' to the layout record itself. Ideally, this validation should be done in a script trigger by OnObjectValidate, so the user isn't allowed to leave the field till it is valid. But the validation isn't possible at that stage because selectContactName hasn't yet been used for the lookup to set the other link record fields.
If the validation is done OnObjectSave or the link record has been updated otherwise, there seems no way to detect whether the link record is a new one that can safely be deleted after a failed validation, or if the link record is being changed and should be reverted to a previous state. I could save that previous state in some global variables, but again that prevents the use of multiple windows for this layout. And the script has to be changed for each and every change in the field definitions of the record.

Any suggestions to solve these two problems are welcome.

• ###### 12. Re: n-m (indirect) self-join

The other being that the portal row that had focus before the GoTo-button was pressed, will decide which record will be active. So it is still no substitute for the two chained GTRRs.

I must be missing something here as that makes no sense. Whether you use two GTRRs in a series or just the one, you are no longer ON any record shown in your portal or layout once this script has been executed. You have a portal of Children linked to many grandchildren. After the two GTRR's you are on a Grandchild record--and no record in that table is even visible in the original portal or layout. So how can any one of the be a Current Record based on what portal row was clicked?

BTW, having to have a layout for the first GTRR doesn't strike me as a major negative factor here. You pretty much always have to have at least one layout for every table anyway--if only for testing and debugging purposes so you should have such a layout in your database anyway. I just see no need for the two GTRR steps in sequence.

• ###### 13. Re: n-m (indirect) self-join

Sorry, if I might have used the wrong terms. I have a layout with table Contacts, and a portal wit join table Links. The Links will be the children. Each link refers to a Contact1 in table Contacts and a Contact2 in a second TO of Contacts, Contacts2. The latter will be the grandchildren. But I don't believe that it matters that in this case there is an (indirect) self join.

The layout displays contact A. The portal shows links to contacts B, C and D Now I press a button on the row with the link to C.

The first GTRR (match current record only) goes to a layout for table Links, with link C the active record, while links to B, C and D are in the found set. The second GTRR (match all records) goes to contact C, with contacts B, C and D in the found set. So the fact that the portal row with the link to contact C had focus, determines which contact ends up as the active record.
I wasn't able to copy this behavior with just one GTRR.

BTW You're right the extra layout isn't a real problem. In the mean time I was able to solve problem 2 about the validation that I mentioned by cheating a bit: I use an executeSQL(). That does mean that the program became a bit harder to maintain, so a better solution is welcome. For problem 1 about an alternative for an OnRecordDelete trigger I'm still seeking any solution.
• ###### 14. Re: n-m (indirect) self-join

The difference lies in the two data models. I have three data source tables and  you have two, with a pair of TO's to make up the difference.

Well I wouldn't consider using ExecuteSQL "cheating". Most of us consider this function a real "game changer" that while still a very limited use of SQL, removes the need of using some very awkward work arounds and that can greatly simplify the relationship graph.

But after skimming through all the posts twice in this thread, I'm not spotting where you had a question about "validation".

As to deleting records in a related table, there are really only two ways to do that:

a) pull the record to be deleted up on a layout based on that record's table and use delete records

b) go to a portal row that displays this record and use delete portal row.

A partial work around might be to "mark" the record for deletion by setting a field to a value, then clear the match fields to drop it out of any portals. Later, a scheduled script can find all such "marked" records and delete them, though there can sometimes be very good arguments for never actually deleting the record but just marking them and then structuring your interface to always omit them. (That allows you to "undelete" a record by clearing the field that marked it for deletion.

And I agree that an OnRecordDelete trigger would be useful to have: http://www.filemaker.com/company/contact/feature_request.html

1 2 Previous Next