Sorting a many-to-many relationship

[Steve Gubser]

Hi folks,

I have a many-to-many relationship where the join table includes a date. I want to sort the relationship by that date in descending order. For example, let's say I have Buyers <-> Sales <-> Sellers, where Sales is the join table. Then I want to sort by sale date in descending order.

When I show a Seller in a layout attached to Sellers, I want to display information about the Buyer involved in that Seller's most recent Sale. I want to do that with a related field in the layout, not a portal, because another task I need to do is to send e-mails to each Seller where I remind him who his most recent buyer is---and for those e-mails I need to be able to call on Buyers data like I do in a related field.

I can go halfway by sorting the Sales <-> Sellers part of the relationship according to descending sale date. Then when I pull related data from Sales---asking for only one record---I get the most recent record, as I should. But if I then reach one step further along the relationship tree and ask for information about the Buyer, Filemaker reverts to its default behavior of going through the oldest Sales record, not the newest one.

So: How to I make the sorting of the Sales <-> Sellers relationship also control which Buyers record on a layout attached to Sellers?

I have a small example file illustrating the problem, but I seem not to be permitted to upload .fp7 files.

Thanks,

Steve

[comment]

I want to display information about the Buyer involved in that Seller's most recent Sale.

You can do this by using a one-row portal to Sales, sorted by Sales::Date, descending.

I want to do that with a related field in the layout, not a portal, because ...

You will need another relationship for this. However, you could have your script get the data from the correct buyer record - without complicating the relationships graph.

I seem not to be permitted to upload .fp7 files.

You need to zip them.

[Steve Gubser]

You can do this by using a one-row portal to Sales, sorted by Sales::Date, descending.

Yes, this method I think I understand.

You will need another relationship for this. However, you could have your script get the data from the correct buyer record - without complicating the relationships graph.

I'm not sure I understand this. When you say I need another relationship, do you mean (for example) that I should introduce a foreign key in Sellers that points to the most recent Buyer, and vice versa? In other words, I'm not supposed to use the many-to-many relationship at all, but instead somehow copy part of the join table into fields that establish a one-to-one relationship?

You need to zip them.

OK, just for completeness I include an example file. In the Sellers layout, you see that Elmer Fudd's most recent buyer was Mickey Mouse---and that's right according to the Sales table. But Mickey's e-mail address is wrong because it was pulled as a related field through the many-to-many relationship---so, per my previous posting, Filemaker ignores my specified sorting of Sales <-> Sellers and instead goes through the oldest Sales record to grab the Buyer data.

I also include in the file a work-around where I have an unstored, calculated field in the join table whose value is the Buyer's e-mail address. When I ask for that calculated field, I get the right address, because Filemaker properly heeds my specified sorting of of the one-to-many relation between Sellers and Sales.

Thanks for your help.

[bruceR]

OK, just for completeness I include an example file.

Oops, no file attached.Attachments here can be a bit confusing.

[comment]

When you say I need another relationship, do you mean (for example) that I should introduce a foreign key in Sellers that points to the most recent Buyer

Yes. If the records in Sales are entered chronologically (or the relationship is so sorted), you can use a calculation field in the Sellers table =

Last ( Sales::BuyerID )

Then use another TO of Buyers for a relationship:

Sellers::cLastBuyerID = Buyers 2::BuyerID

I also include in the file a work-around where I have an unstored, calculated field in the join table whose value is the Buyer's e-mail address.

That's a good option, too - if ALL you need is the e-mail address (or if the field concatenates several buyer's attributes).

[Steve Gubser]

Oops, no file attached.Attachments here can be a bit confusing.

Right. Trying again to attach the file...

test.zip

[Steve Gubser]

Yes. If the records in Sales are entered chronologically (or the relationship is so sorted), you can use a calculation field in the Sellers table =

Last ( Sales::BuyerID )

Then use another TO of Buyers for a relationship:

Sellers::cLastBuyerID = Buyers 2::BuyerID

Very clever. This seems like just what I wanted. However, when I tried it I ran into a strange problem. When I add a new sale, cLastBuyerID updates when I view it in the Sellers layout, as it should since it's an unstored calculation. But the last buyer's e-mail address, as displayed in the Seller's layout, does NOT update unless I quit and restart FileMaker!

To see this for yourself, you can open test2.fp7 and add a new Sale, for example with Buyer = Donald Duck and Seller = Elmer Fudd. What I see when I do that is that the cLastBuyerID field in the Sellers layout changes immediately to "Donald Duck" (as it should), but the related field Buyers 2::Email doesn't change to "[email protected]"---until I restart FileMaker.

Is there a way to tell FileMaker, "I just changed a calculated foreign key, now make sure you update all the related fields in the current layout that used this key?"

... And here's the test2.fp7 file.

test2.zip

[comment]

Is there a way to tell FileMaker, "I just changed a calculated foreign key, now make sure you update all the related fields in the current layout that used this key?"

Refresh Window [Flush cached join results]

[Creek]

I have a very similar problem.

I attach a example.

Unfortunately, I do not know how to make a search in the relational field.

Layout T3

Fields: T1 Name

test3.fmp12.zip