You should be able to do this with a filtered portal showing one row only.
Create a field in the Patients table like last_service_date with a calculation Last(JoinServices::Procedure Date)
Then filter the portal with JoinServices::Procedure Date = last_service_date
Not sure if this is what you are looking for. The portal can be used to extract data for other operations.
Thank you for your response!
So I had started this problem by doing something similar: I created a portal to filter for all related records of services matching rabies vaccinations, and sorted it by procedure date (descending) and showing just the first record. So I can see the last vaccination date on the patient record. But I can't figure out how to get that date out as a variable to pass it to another function or script.
I don't want just the last procedure date because the last time I saw the patient is not necessarily the last time they were vaccinated. I want the last Procedure Date IF the Procedure is a rabies vaccination.
Your script could use the Last() function and no portal at all is required in order for a set field step in your script to access this data as long as the sort order specified in the relationship (a sorted portal doesn't work for this) makes the desired record the "last" related record. You can also reverse the sort order (by using descending instead fo ascending, for example) and then just use a reference of RelatedTable::Field to access data from what is now the first related record.
Note that specifying a sorted relationship can affect how other parts of your database that refer to the same relationship function. Sometimes you need to use a separate table occurrence so that you can set up a sorted relationship that is separate from your original relationship that is unsorted or sorted in a different order.
I tried something like that too, where I pulled into a List() all rabies vaccinations' dates, but the Last function - or even explicitely calling List for instance - never worked. It should have worked?
Last will work IF the sort order specified for the relationship in Manage | Database | Relationships makes the record you want the last related record.
A quick way to verify that your specified sort order is working is to put an unfiltered, unsorted portal to the same table occurrence on your layout. If you scroll that portal to bring up the last record in this portal, it should be the same record as that which will be returned by the Last function.
I can't seem to get Last to work at all in a script. I can generate a list to return the expected list of dates, but Last(ListName) returns nothing. So frustrating. I have three very different scripts going to try to get to this result, including my favorite listed above, and none of them work.
It should have this syntax: Last ( RelatedTablename::FieldName )
And have you specified a sort order for the relationship?
Have you verified that it is sorting related records into correct order?
OK I was ultimately able to get the results I expected by swapping Enter Find Mode out for Perform Find and setting two search parameters (matching both patient ID and procedure keywords). Then going to the Last record and extracting the date finally worked. Thank you for taking the time to offer alternatives!
Now to scale this script to a bunch more procedures and all the patients...
You're really doing it the hard way.
Have you considered actually following Phil's excellent instructions?
I did, but as I mentioned, using the Last function returns nothing. Ever. Syntax checked, sort order checked. I am sure the mistake is mine but I can't figure it out. I make the script end with just spitting out the list of dates and I get the correct list of dates. I add simply one more step to get the Last of the List and I get nothing.
I would love to do this more excellently but I'm not sure what else I can do.
No, that's exactly the point.
You were not able to make a basic FileMaker function work.
The lesson from this is not to discard the function but to learn how to use it.
The Last() function most definitely DOES return something when properly executed.
in a perfect world, I agree and would love nothing more than to make something that ought to work easily, work for me. But I have a script based on a different strategy that works AND I have made no progress troubleshooting my use of Last after hours of attempts. I am out of ideas. What would you like me to do differently?
There are really at least two problems here. One is learning to use a particular function. Important in itself.
The other and perhaps larger issue is being able to read, understand, apply, and communicate accurately about FileMaker techniques. You have pretty much blown off what Phil has provided for you.
This problem will repeat itself. It is very unfortunate that this forum doesn't allow posting files. It would be good for you to develop a simple file, post it, and get replies based on what you have actually done.