0

I have a table of transactions. For any given record, I want to be able to include a portal which shows all related transactions. Since the related records are in the same table as the parent record, I have created a second table occurrence (TO). I have linked these two TOs with a join table, into which I enter the foreign keys of the two TOs to indicate which records relate to which other records.

On the layout for a given transaction, I've added a portal which displays related records from the second table occurrence. So far, so good.

So, let's say in the join table I said transaction 100 is linked to transactions 105 and 110. In the portal for transaction 100, I can see records 105 & 110.

However, I would also like to see transaction 100 in the portal for transaction 105, but can't figure out how to do this, without having to manually enter the same relationship, but in reverse.

NB. I'm using Filemaker Pro 12.

trf0412
  • 3
  • 1

2 Answers2

0

You need to create two records for each relationship in the intermediate table to do this.

For example, you have TO_1 and TO_2 and an intermediate table. The intermediate table has two fields, id_1 and id_2. When you create a relationship between TO_1 and TO_2, create two records in the intermediate table. One record stores the id of TO_1 in id_1 and TO_2 in id_2. The second record stores the id of TO_1 in id_2 and TO_2 in id_1.

the
  • 21,007
  • 11
  • 68
  • 101
eukaryote
  • 51
  • 3
  • "*You need to create two records for each relationship in the intermediate table to do this.*" No, you don't. – michael.hor257k Sep 27 '15 at 09:56
  • Using two records is not the only way, however, it is a very good way to do it. It offers a great deal of flexibility in design and usage. It uses simple constructs that are robust and scalable. The methods you link to use calculations to generate the links on one side of the relationship. That means that you can only use this relationship in one direction. That's a limitation. It also means that you will see performance impacts as the number of records grow. – eukaryote Sep 27 '15 at 22:23
  • No, no and no. No, it's not a good way to do it. No, it does not mean you can only use this relationship in one direction (that's the whole point here), and no, there's no performance impact. In fact, you will have twice as many records - and it makes no difference. – michael.hor257k Sep 28 '15 at 05:24
0

It sounds like you want all the foreign keys to be present in a single field that you use a multi-key. In your example, the multi-key would look like this:

100
105
110

If you do not want to display the present record in a portal of related records, you can omit it be defining the relationship to exclude itself by adding the predicate something like this:

fk = fk AND pk ≠ pk

Would that work?

SoliantMike
  • 371
  • 1
  • 7
  • How is that going to work in the opposite direction? That's what this question is about. – michael.hor257k Sep 28 '15 at 20:23
  • Because the keys to all child records are represented in the multi-key. If you are on record 100, the portal shows 105 and 110. If you are on record 105, the portal shows 100 and 110, and so on. – SoliantMike Sep 29 '15 at 12:38
  • Sorry, that makes no sense to me. You say fk = fk AND pk ≠ fk. What's in the fk field of the Transactions table? Why would there even be a fk field in the Transactions table? – michael.hor257k Sep 29 '15 at 14:50
  • fk is foreign key and pk is primary key. Basically the point is to not show the parent record in the portal, since it is a self join. – SoliantMike Sep 29 '15 at 14:57
  • Yes, I know what fk and pk stand for. However, I still don't understand your suggestion - and I am not exactly a beginner. -- Note that this is a self-join using a join table, so there would not be any foreign key in the "parent" tables (which here happen to be the same table). – michael.hor257k Sep 29 '15 at 19:54
  • Sorry, the "fk" is the multi-key in my example. I mocked this up and it works as the original poster was asking. – SoliantMike Sep 30 '15 at 00:51