You will not get related information from the query. ExecuteSQL() does not know that these are to be related unless you tell it.
You must make the JOIN in the FROM clause and the match (as you have in your relationship dialog).
I don't know what other fields you have so cannot get more specific on the query.
If you have a "looked-up" named: FP Lot Number, in the table: Quality Details, then this query works. If OTOH, the field is already related where does it exist and what are the fields for the match to make it show as a related field on the layout showing the samples.
Don't you mean something like this below.
(I'm assuming you have a single table. However, if you have multiple tables, you need to do a join as beverly said.)
If this isn't what you meant above, please post some representative data. Your posting is not clear without representative data.
And how would you use that query as a "portal filter"? A portal'a filter expression has to produce a true or false result and be evaluated against every record in your set of related records.
You could use this calculation in place of a portal, but not as a filter expression nor as a match field in a relationship . It would match to records in a relationship but would not give you a set of unique values when it did so.
You need to decide which record should be shown in portal, for example "oldest" can be detected using Get(RecordId) calculation field
FROM \"Quality Details\"
GROUP BY \"FP Lot Number\"
This get record id list distinct on lot number, then you can make portal using this as related field.
main::this = inPortal::recid
I would never rely on Get ( RecordID ) for anything other than temporary this-works-now-if-the-data-is-not-exported-and-reimported. It is a very useful feature for say, web publishing and you get results returned and then can be specific to edit or delete or update a particular record. It is unique for that transaction. "Creation Order" is not one of those things for which I'd use RecordID. Something else should be used.
Without knowing your structure it's a little hard to answer, but I have used SQL to filter portals before. But I used a multi-key field (complex key field).
I use the SQL query to fill the complex key field, and that validates the relationship to the records I'm specifically interested in. It's also very light and quick.
So if you use SQL query to fill in a multi key field, this should work for you. There are lots of resources about complex keys available online. It's basically just a key field that, rather than having a single value, has multiple separated by carriage returns. If any of the values in the list match related records, the relationship is validated and their data shows.
OH! one other thing I should mention. For your SQL query, I think all you'll need to make that work is a WHERE clause. It looks like you're just looking for data related to the current record.
So you would essentially add a WHERE clause, feed it a "?" parameter, and define that parameter to be your primary key.
Lots of info about the WHERE clause online and in the SQL manual from FileMaker
Good suggestion, but DISTINCT (on a field/column) already narrows down a list). WHERE can help only if there is further narrowing needed.
SELECT DISTINCT city
WHERE state IN ( 'CA', 'WA', 'OR' )
// only the West Coast, for example
This reply, above) is an other example of using the WHERE:
And "HAVING" will narrow down a GROUP BY.
I do recall seeing that in the "The Missing FM 12 ExecuteSQL Reference". LOL
I quoted as "oldest" to mean it could be really oldest, but I was bad for writing. OP looks not mind about which record in group (having same value on FP Lot Number field) is shown in portal (only a field may be there), so "oldest" may not nead really oldest.
I believe this is the real case for "this-works-now-if-the-data-is-not-exported-and-reimported", but I forgot to write the SQL calculation field (main::this) should be unstored. This is calculation field so will never be imported.
My point were, inPortal::recid field should be unique in each group (FP Lot Number), so if there is primary key field in the table, it could be used, not need to be bothered by Get(RecordID).
For the purpose it is not need to be unique in whole table, so if there is no such field, Get(RecordID) may be best. Making it unique in group become more complex, Get(UUID) is too long.
kazznfx wrote well about multi-key what I tried to.
Where may be need for project, since OP wrote "the unique lots of a related project", it is already used for portal relation.
I don't want to steer you away from using SQL and maybe I'm looking at your question the wrong way, but you wish to create a portal to show the unique "fp lot numbers" so why don't you create an additional table with just the lot numbers? That way you do not need to filter any portal.
There are sveral scenarios that you can follow to achieve this, but this is an example: Every time you create a quality-record and a lot number is entered, you could check for an existing lot number against that additional table. If the lot number doesn't exist in there, create one. Via a multilevel relation, the unique lot numbers can now be shown in a portal to the additional table.
Primary keys and Foreign keys, (unique identifiers making the relationship), but never the Get ( RecordID ). This should never be used for relationships. And I wouldn't rely on them for filtering (which is just another 'relationship'), especially if unstored.