6 Replies Latest reply on Dec 6, 2014 3:01 PM by SteveFransen_1

    Refreshing Multi-User Object Hiding, and Performance?

    SteveFransen_1

      Title

      Refreshing Multi-User Object Hiding, and Performance?

      Post

      These are my relationships:

      Patient -----< Evaluation -----< ChiefComplaint

      where Evaluation::fkPatient = Patient::pkPatient and ChiefComplaint::fkEvaluation = Evaluation::pkEvaluation.

      Evaluation -----< ChiefComplaintPopover (a second occurrence of ChiefComplaint) 

      where ChiefComplaintPopover::fkPatient = Evaluation::fkPatient.

      The Evaluation layout has a portal showing ChiefComplaint records related to that Evaluation. A popover has a portal showing ChiefComplaintPopover records which allows the user to see all of the current and past chief complaints for the patient from the current or past evaluations.

      The popover  button should be hidden if no chief complaint records exist for the patient. It's hiding calculation is:

      ExecuteSQL ( " SELECT fkPHIPatient FROM ChiefComplaint WHERE fkPHIPatient = ?  "; ""; ""; Evaluation::fkPHIPatient ) = ""

      Question # 1: If user 1 deletes the last chief complaint record for a patient, or creates the first one, what's the fastest way to refresh the hiding state calculation on user 2's layout and update the hiding state of their popover button?

      Question # 2: The Evaluation layout has multiple occurrences of the same group of objects (portal, popover button, portal on popover) for the different elements of the evaluation. Does this approach create performance concerns since each calculation involves a SQL SELECT call?

      Thanks,

      Steve

       

       

        • 1. Re: Refreshing Multi-User Object Hiding, and Performance?
          jbante

          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.

          • 2. Re: Refreshing Multi-User Object Hiding, and Performance?
            SteveFransen_1

            Thanks Jeremy,

            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?

             

            • 3. Re: Refreshing Multi-User Object Hiding, and Performance?
              jbante

              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.

              • 4. Re: Refreshing Multi-User Object Hiding, and Performance?
                SteveFransen_1

                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?

                Thanks-again

                • 5. Re: Refreshing Multi-User Object Hiding, and Performance?
                  jbante

                  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.

                  • 6. Re: Refreshing Multi-User Object Hiding, and Performance?
                    SteveFransen_1

                    ...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 ChiefComplaintPopoverA 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.