Double sided join

[mdfOffman]

Is there a way in Filemaker to get the same result as this SQL statement?

SELECT Field 1, Field 2, Field 2 AS Field 1, Field 1 AS Field 2.

And BTW, Happy New Year :-)

 

[comment]

Could you explain in more detail what is the result you're after? AFAIK, the AS operator creates an alias, not a join. And I am not sure what a "double sided join" is anyway.

Note also that asking for "the same result" as SQL is rather ambiguous: the result of an SQL query is a block of text; in Filemaker, the equivalent of a query produces a found/related set of records. 

 

Edited January 2, 2023 by comment

[mdfOffman]

Thanks for taking the time to respond.  I was trying to keep the question short and simple but let me explain.

I am trying to manage self-joins e.g. Link one document to another, e.g. Letter 1 is a reply to Letter 2.

I create a join table DocLinks with fkDoc1 and fkDoc2.  To view the link from both docs, I have to create two instances of the DocLinks table.  I view the join with a portal for DocLinks when in the Letter1 record.  To view the join when in the Letter2 record, my portal has to view DocLinks 2.  So I need 2 separate portals.

Using the SQL SELECT statement I wrote above, in the second half of the statement Field 2 masquerades as Field 1 and v.v.  So you see both sides of the join from both records.

To summarise, I am trying to view both sides of the join from a single portal rather than 2 separate portals.

Thanks.  i hope I am just displaying my ignorance :-)

 

[comment]

There are several ways you could do this. Let me ask this first: do you want the links themselves to be inherently symmetrical? Or do you want to keep separate "links from this letter" and "links to this letter" and only display both kinds in the same portal?

 

[mdfOffman]

Thanks again.  I'm not sure I understand your second option but I think the answer is "symmetrical" .

[comment]

38 minutes ago, mdfOffman said:

I'm not sure I understand your second option

The second option would allow you to distinguish between links to the current document and links from the current document. IOW, a record in the Links table would have a direction: there would be a FromDocID field and a ToDocID field. Thus you would be able to have separate portals for the two kinds of linked documents - and the challenge would be to build a third portal that would show both.

The symmetrical option is much simpler. Instead of having two foreign key fields in the Links table, use a single field and enter both IDs as a return-separated list. Or - preferably - use a repeating field with 2 repetitions as the foreign key field (this makes it easier to enter the values using a value list, for example).

Here we have only one kind of links and there can be only one portal - and all you need to do is filter it in order to exclude the current document. And perhaps add some mechanism to prevent duplicate links.

 

Edited January 2, 2023 by comment

[mdfOffman]

Yes, symmetrical it is!  Super cool. thank you so much.  I shall try that.

 

[comment]

Here's a link to previous discussion of this topic. IIRC, we did not have some of the current tools back then, but the analysis still holds:
https://fmforums.com/topic/38282-join-table-inverse-relationships

 

[mdfOffman]

Thank you so much for the time to help me.  After much internal debate and testing, I decided to surrender and create a "Mirror DocLinks" script.  This, of course doubles the size f the join table but I found it the simplest and most reliable way.  It works just fine though you have to be careful with the delete script which needs to delete both records but not any other records to which field 2 is connected.

Thanks again

[comment]

1 hour ago, mdfOffman said:

I found it the simplest and most reliable way.

Well, if you like it, that's your privilege - but it's neither simple nor reliable. As you yourself explained.

 

[mdfOffman]

Thank you.  No doubt I shall learn better as I progress :-)