I know how to do this via creating extra FM relationships
You don't necessarily need a new TO/relationship; just create a new one-row portal using the existing Clients/Contracts relationship, sort it by contractDate descending, filter it with the expression 'Contracts::signed', and put the Contracts::Value field into the portal
If you want to use SQL, the calculation would look like
ExecuteSQL ( "
WHERE \"_fk_clientsID\" = ? AND Signed = 1
ORDER BY ContractDate DESC
" ; "" ; "" ; Clients::_pk_clientID
Use GetValue ( executeSQLcalculation ; 1 ) to get the value for the most recent date only. In FM 13, you can alternatively add a FETCH FIRST 1 ROWS ONLY to the SQL statement.
1 of 1 people found this helpful
The challenge with this is that you can't really do subqueries in FileMaker's SQL except for in the WHERE clause.
However, here are a couple of different approaches that might help...
ExecuteSQL ( "
JOIN Contracts ON ( Contracts.Client_ID = Clients.Client_ID )
Contracts.Signed = 1
"; "|"; ¶ )
That will show all contracts that have been signed, in descending order (based on contract ID - you could easily change this to "contract date" if you'd like), for all clients. The problem with this approach is that you won't just get the most recent contract - you'll get them all. But if you're using this in a scripted report, you could just loop over the results to show only the most recent contract for each client.
Another approach would be to set this up as a calculation in the Clients table itself. For example...
ExecuteSQL ( "
Contracts.Client_ID = ?
AND Contracts.Signed = 1
FETCH FIRST 1 ROWS ONLY
"; "|"; ¶; Clients::Client_ID )
That works, but requires adding the calculated field to the Clients table. It also has the disadvantage of having to run the SQL for every client being included in a report.
I hope this helps.
Thanks for your suggestion, I would never have thought of using a filtered portal like that.. It works fine for now, until the client asks to be able to sort on that field.
Thanks Tim, if the client wishes to sort on the last contract value then I will try your second option.
Chris, you've gotten some good answers. Instead of ExecuteSQL, might I suggest the Last() function. It will give you the last related record based on the sort for that relationship in the graph. If unsorted, it will more than likely be the last record by date as well, but you never know!
You can do the related sorting one of these ways:
a. sort the relationship (I rarely do this)
b. sort the portal (one row portal, as other suggested) - test to see if this gives you the Last() you want.
see help topic: (http://www.filemaker.com/13help/en/html/func_ref3.33.45.html)
Beverly, thanks for that, totally forgot about this function. And I just spent ten minutes looking for a "first" function before realizing I didn't need it