alonjr

@comment, I finally found a solution which is a hybrid of a calculation field (as you pointed out in your last post above) and an ExecuteSQL query functioning as a record filter on the Portal. Thanks so so much!!!

@comment, thank you. I'm going to tinker with Filemaker relationships to see if i can achieve the non-ExecuteSQL alternative.

Explained as simple as possible Objective: track the whereabouts of items (Products) as they move from person (Contact) to person. At any given time, a Product is in possession of a Contact. Three tables in use, Products, Contacts and Transactions. A row in the Transaction table records: t.fk_ContactID— the Contact that is in possession of ... t.fk_ProductID— the Product and the ... t.EffectiveDate— date-time at which the Contact took possession of the Product. I want to know what Products Steve (a specific Contact) has at this very moment. To answer this question, I need to logically follow these 3 steps, in order: Get a list of all Products that were at the possession of Steve (in the past and until now). Identify the latest Transaction for each Product in the list of #1 above. Filter out transactions, found in #2 above, that don't have Steve as the Contact After step 3, you end up with a list of transactions that are associated with Steve and the Products that he is now in possession. Hope this makes sense.

@Comment, Although I no longer get an error, adding t.fk_ContactID, I get the wrong result. What I'd like to do, in essence, is run the first green query below, then on the result run the purple query below. SELECT t.fk_ProductID, MAX(t.EffectiveDate) AS latestDate FROM Transactions t GROUP BY t.fk_ProductID WHERE t.fk_ContactID = '9EE273FD-A35C-4177-8E2F-8ABE6F368286' I am pretty sure that in the standard SQL, this is possible, but in Filemaker for some reason it is not permissible.

@comment, thank you so much for creating a small demo, I'm very grateful! Your demo indeed works; however i must apply the WHERE clause after the GROUP BY clause, otherwise I get the wrong results. There must be another way to achieve it, I just don't know how.

WHERE was the first thing I tried because I thought that using GROUP BY requires using HAVING instead of WHERE. Substituting HAVING with WHERE gives a syntax error.

Hello, first, I'm a newbie. SELECT t.fk_ProductID, MAX(t.EffectiveDate) AS latestDate FROM Transactions t GROUP BY t.fk_ProductID HAVING t.fk_ContactID = '9EE273FD-A35C-4177-8E2F-8ABE6F368286' The green part of the query above works well, however when I add the HAVING clause, it breaks with the following error: "All non-aggregated column references in the SELECT list and HAVING clause must be in the GROUP BY clause." How do I overcome this apparent limitation? Looking forward to your feedback. thanks a lot.

Thanks guys. I found a great solution by Guy Stevens on youtube. https://www.youtube.com/watch?v=hhe7tMvaorg

Hello, I am a newbie to FileMaker but a long time programmer ... I am working on an Art Gallery collection tracking application. A key layout in this application is a matrix of images, each representing one artwork item in the gallery's collection. Clicking on any image will take you to a details layout of that particular artwork (see link below to image) I've defined an Artworks table such that each row of this table documents a single artwork with fields such as Title, Artist, Material, YearCreated, Dimension, Price, Image, etc. List Layout and Portals, both limit you to vertical rendering of records, they cannot render records horizontally. I therefore moved on to adding a new Table called ArtworkMatrix where each row references 5 artwork records from the Artworks Table. With this technique I was hoping to be able to render rows of 5 images each. My challenge now is to write a script that automatically populates the ArtoworkMatrix Table. I was not able to piece it together with the little documentation and examples available out there. Any help would be greatly appreciated. Cheers Screenshot of Art Gallery image matrix layout