Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Self Join Table Question


Recommended Posts

I have a file (Phone List) that I created quite some time ago, that contains a duplicate record check that works perfectly.

The Phone List  file is using two fields”

Dup Check. a concatenation of the following:
Right(Phone ~ Home;3) &  Right(Phone ~ Office;3) & Last & Left(First;3)&Left(Code 2;1)

and a Dup Warning field as follows:
If(Count(Dup::Dup Check)>1; "This record is a duplicate"; "")

The Self Join table uses a table (Dup) that is not listed in the Tables listing, but is in fact joined to the Phone List table.
I now want to use this same method on a new file I am working on (HoY App, also attached).

My problem is that I cannot figure out how to create the Dup table in my new app with0ut actually creating a table by that name.

So the question is, how did I create the Dup table in the Relationship view of Phone List without creating an actual table by that name?


Phone List.fmp12.zip


Link to comment
Share on other sites

As Steve Martino said in post#2, a selfjoin relationship isn't a relationship to a separate table, it's a relationship of a table to itself — by definition.


On the relationship graph it will appear as a separate table occurrence.  To create one you can:


• option-drag the existing table occurrence (that's on a Mac; on a Windows PC it may be Control-drag or Alt-drag or Shift-drag, I forget which); or

• Select a field and drag out as if you were going to connect that field to another field elsewhere, then bring the cursor back in to the same table.  A new table occurrence will pop up along with a dialog box asking you to give it a name; or

• click the "New table occurrence" button at lower left and specify the same table as the source table of it.


Then set up the relationship according to your criteria, just as you had it in the other solution.


Link to comment
Share on other sites

I understand what you said, and I tried to apply it to the last file I sent to Steve Martino. My problem is that I think I had adapted the idea from my old Phone List.fmp12 file, but it still resulted in all entries (whether or not they are duplicates) to show a message showing  that they are duplicates.

I found another approach, and adapted that to my TestRecovered.fmp12 (the last one I uploaded to Steve Martin), and it does the same thing.

I am not a complete fool, though it may seem that way to you. However, something here is escaping me, and that's why I need help with the file I last uploaded.

Thanks for your understanding.


Link to comment
Share on other sites

Unfortunately, I can't download your file:


Sorry, there is a problem

The page you are trying to access is not available for your account.

Error code: 2C171/1


I'll try to let Ross know there's still an issue with my account permissions on the board.

Link to comment
Share on other sites

OK, Ross fixed the permissions issue and I downloaded your file.

Your definition of "duplicate" appears to rely on this relationship:


where "DUP MATCH" is defined as the field Full Name.


... and you appear to have set the storage of the field DUP MATCH to "unstored".  That should mean that no record will ever show up as a related record (because you can't use an unindexed field on the "far side" of a relationship)

Then you've got a field named DUP CHECK which is defined as:




You're saying "If the Full Name in this table is the same as the Full Name in this same table as seen through the selfjoin relationship, it is ORIGINAL but if that isn't so it's a DUPLICATE".


Everything is coming up as a DUPLICATE because the field DUP CHECK is unstored.  But if you change it to a normal stored calc field, everything is going to come up as ORIGINAL.  Do you see why?


Here's why:  Let's say record 217 in your table has the Full Name value  "John Smith".  Therefore the field DUP MATCH is also "John Smith".  Your relationship is defined as DUP MATCH = DUP MATCH.  That means record 217 is going to be related to itself because you haven't prohibited the record from "seeing its own self" through the relationship.  So FULL NAME will indeed be equal to CLIENT DATA 2::FULL NAME because it's the same bloody record.

I said that it makes no sense, and indeed it doesn't:  first let's fix it so that each record will NOT see its own self as a match:


1) Define a calc field as Get(RecordID) and let's call it RecID.  

2) Now change the definition of your relationship:


RecID ≠ RecID

(i.e., RecID does NOT equal RecID)

... So now record 217 will no longer "see itself"; there will only be a related record in CLIENT DATA 2 if there is a second record for "John Smith".  With me so far?

Now let's examine your DUP CHECK field definition:


That's backwards; it means record 217 will show up as "ORIGINAL" if there is a duplicate but it will show up as "THIS IS A DUPLICATE RECORD" if there isn't a duplicate.


So we need to change that field definition:


Case (Length ( CLIENT DATA 2::FULL NAME)=0; "UNIQUE";  




This will let you not only find the records that are duplicates but also distinguish between the first occurrence and its duplicate.


Link to comment
Share on other sites


  • Create New...

Important Information

Terms of Use