1. FileMaker is pretty good about refreshing data to other users after it's been committed — especially with object hiding calculations, which are relatively trigger-happy with when they evaluate.
2. Yes, there are performance concerns. The first thing to do is remove any redundant hiding calculations. If one object contains other objects, such as a portal containing a button on the portal row, don't put the same hiding calculation in the contained object; hiding the container hides everything in it for free. Only include hiding conditions for child objects if there are states where you want the container object to be visible, but not the contained object. The second thing to do is to use a plain-old FileMaker calculation instead of ExecuteSQL. ExecuteSQL has to do a lot of work translating SQL to how FileMaker is working under the hood, so just using IsEmpty ( ChiefComplaint::fkPHIPatient ) is significantly more efficient.
IsEmpty ( ChiefComplaint::fkPHIPatient )
This sounds much better but don't I have to include a "find" or "search" function somewhere? I'm still learning about what are and aren't the active records on layouts that include several individual record fields from related tables and/or multiple records in portal rows from related tables.
Will ChiefComplaint::fkPHIPatient resolve to empty just by stating this field name, if there are no ChiefComplaint records where ChiefComplaint::fkPatient = Evaluation::fkPatient?
When you reference a field in another table, FileMaker will use the terms of the relationships connecting where you currently are to the table you're referencing in place of a find. So, assuming that your ChiefComplaint table occurrence is related to the Evaluation table occurrence by ChiefComplaint::fkPatient = Evaluation::fkPatient, then yes, ChiefComplaint::fkPatient should resolve to empty if there are no ChiefComplaint records matching the Evaluation record that you're currently on.
Thanks Jeremy, that makes sense and works perfectly!
Now I'd like to replace this ExecuteSQL statement with a more efficient plain-old FileMaker calculation:
Set Variable [$ElementData; Value: ExecuteSQL ( " SELECT * FROM " & $ElementTable & "Popover WHERE pk" & $ElementTable & " = ?"; ¶; ""; $pkElement )
In other words, given the primary key of a record, set a variable to a carriage return delimited list of the values of all of the fields of the record. I can't find a script step to do this. Does one exist?
There isn't a script step to get a return-delimited list of all the field data for a record. Why do you want to? That isn't a request that comes up very often, so I'm wondering if you might be better off with a different approach to whatever bigger-picture problem you're trying to solve.
...bigger-picture problem you're trying to solve.
Here's the requirement:
ChiefComplaintPopover is an occurrence of the table ChiefComplaint. A portal on a popover displays records from ChiefComplaintPopover. A user selects a portal row. That performs a script that executes these steps:
(1) Perform Find looks for a ChiefComplaint record meeting specified criteria. A new record is created if one does not exist for those criteria.
(2) Values from some of the fields in the selected ChiefComplaintPopover record update some of the fields of the found (or new) ChiefComplaint record from step (1).
I use this script with many different tables so I don't know in advance which fields I will need, or even how many fields are in the table. So, I set the value of a variable to a return-delimited list of all the field values for the selected record and use a conditional to decide which fields to update.