11 Replies Latest reply on Mar 29, 2016 7:51 AM by dtcgnet

    Calculation to get the value of a field based on Max of another field

    dpwrussell

      This would take me about 2 seconds in SQL, but I can't seem to figure out how to do this in Filemaker.

       

      Basic example:

       

      Table 1 has:

      id (number)

      details (text)

       

      Table 2 has:

      t1_number (number) - Relationship to Table1:id (1-M)

      recorded (data)

      value (text)

       

      Each Table1 record has several Table2 records.

       

      The idea is to add a calculation field to table 1 that displays the most recent contents of the field value in table 2.

       

      I know how to make a calculation on Table 1 which gets the max value of the recorded field: Max ( Table2::recorded ), but what I want to do is find the record in Table 2 that has the most recent recorded date, then return the value field for that record.

       

      In SQL I would just do it with ORDER BY. something like:

       

      SELECT table_name2.value from table_name2 JOIN table_name1 ON table_name2.t1=table_name1.id WHERE table_name1.id=1 ORDER BY table_name2.recorded DESC LIMIT 1;

       

      Thanks

        • 1. Re: Calculation to get the value of a field based on Max of another field
          Mike_Mitchell

          It's a little unclear exactly what you are trying to accomplish. You've asked about mechanics, but not explained your purpose.

           

          If all you want to know is the maximum value, use a Summary field in table 2 equal to the Maximum of the desired value (which sounds like it's a date):

           

          summary.png

           

          Then just reference that field from the context of table 1. It doesn't need to be a calculation field in table 1; just put a copy of it on your layout.

           

          OTOH, if you're interested in getting other data from the related table, you can sort either the portal (for display or scripting purposes) or the relationship (if you want to address the field as a calculation). Double-click on the relationship line between the two tables and click "Sort records":

           

          sort.png

           

          Of course, you can also use ExecuteSQL to flex your SQL chops. That will return a text result, which you can then parse out as you like. (But avoid doing that as a calculation field for performance reasons.) All depends on exactly what your desired end state is.

           

          HTH


          Mike

          • 2. Re: Calculation to get the value of a field based on Max of another field
            BillisSaved

            Good afternoon dwprussell,

             

            I hope your day is going well. As Mike suggested, it would be helpful to know the purpose of placing the related value from table2 into a field in table1. Would you mind providing a bit more information regarding your goal? Thanks and have a great day!

             

            God bless,

             

            Bill

            • 3. Re: Calculation to get the value of a field based on Max of another field
              dtcgnet

              If you do as Mike has suggested, and sort Table 2 in descending order based on the Recorded Date, all you'd need to do is place the value field somewhere on a layout based on Table 1. The field would automatically display the data from the first related record, which would be the one most recently recorded if you sort as described.

               

              Is it possible that there could be more than one record recorded on a given day?

              • 4. Re: Calculation to get the value of a field based on Max of another field
                Mike_Mitchell

                Yep. With the caveat that sorted relationships can produce performance issues in the right circumstances (like large related sets).

                • 5. Re: Calculation to get the value of a field based on Max of another field
                  dpwrussell

                  The sort and reference idea suggested by @dtcgnet seems to work, although this feels like a fairly dubious relationship to trust. Is it guaranteed to be the case that a field referencing a foreign table which is 1-M always displays the field value from the first matching record?

                   

                  As requested, here is the real life scenario. We have batches of things (in this case, small molecule perturbagens) that undergo quality control (QC) testing. These batches are used up slowly over months or years and QC is normally run on them many times before the batch is used up.

                   

                  Screen Shot 2016-03-28 at 17.28.50.png

                   

                  In the layout for Small Molecule Batch we want to be able to see the status (for the batch as a whole) of the most recent QC test. So if the most recently dated test has "FAIL" in "QC Outcome", then this field in the batch layout will show that. There are several states (PASS, FAIL, QUARANTINE are the most common) and any of these can be the most recent. It is very possible for there to be a series of QC entries that are FAIL -> PASS -> FAIL -> PASS for instance. We are just interested in the most recently dated one.

                   

                  I am unwilling to implement any solution which involves scripting to copy the QC Outcome into a field in the batch table as that is a brittle solution.

                   

                  It seemed to me that the way to do this was to have a calculated field in batch which basically ran a query of the underlying QC table to display the most recent QC Outcome as determined by the QC Date field.

                   

                  I hope that makes sense.

                  • 6. Re: Calculation to get the value of a field based on Max of another field
                    Mike_Mitchell

                    dpwrussell wrote:

                     

                    The sort and reference idea suggested by @dtcgnet seems to work, although this feels like a fairly dubious relationship to trust. Is it guaranteed to be the case that a field referencing a foreign table which is 1-M always displays the field value from the first matching record?

                     

                    Yes.

                     

                    In the layout for Small Molecule Batch we want to be able to see the status (for the batch as a whole) of the most recent QC test. So if the most recently dated test has "FAIL" in "QC Outcome", then this field in the batch layout will show that. There are several states (PASS, FAIL, QUARANTINE are the most common) and any of these can be the most recent. It is very possible for there to be a series of QC entries that are FAIL -> PASS -> FAIL -> PASS for instance. We are just interested in the most recently dated one.

                     

                    In such a scenario, you should probably sort the portal instead of the relationship. You can use a single-row portal if you like (even hiding the borders if it's bothersome). This will impose the performance penalty only when the portal is viewed, not every time the relationship is referenced.

                     

                    I am unwilling to implement any solution which involves scripting to copy the QC Outcome into a field in the batch table as that is a brittle solution.

                     

                    What makes you think that? It's done all the time, especially when performance becomes an issue.

                     

                    It seemed to me that the way to do this was to have a calculated field in batch which basically ran a query of the underlying QC table to display the most recent QC Outcome as determined by the QC Date field.

                     

                    I hope that makes sense.

                     

                    No.  

                     

                    Calculated fields should be used very judiciously. They impose additional performance penalties on the database, especially in a case like this where you're probably going to be referencing a related table (and hence, the calculation has to re-evaluate every time it's called, instead of just when some precondition changes). There are better ways to accomplish what you're after without putting a ball and chain on your solution.

                     

                    HTH

                     

                    Mike

                    • 7. Re: Calculation to get the value of a field based on Max of another field
                      dtcgnet

                      If you can have more than one related record on a given day, then sorting just by date (no matter how you do it) will yield questionable results. I'd consider a timestamp field instead of a straight date. Just something to consider.

                      • 9. Re: Calculation to get the value of a field based on Max of another field
                        dpwrussell

                        @Mike_Mitchell

                         

                        The idea of copying a field within a database when a simple JOIN operation would normally (i.e. in a SQL database) internally optimize this to be very efficient, just seems crazy to me. Scripted triggers in SQL is not a feature you would be delving into to deliver such a minor feature like this. I know literally nothing about Filemaker though, so I'm sure you're probably right.

                         

                        As for using a Portal, I'm afraid that wont work because the batch is already a portal inside another layout (The canonical reference of what the Small Molecule itself is, rather than individual batches of them)

                         

                        We are not overly concerned about performance as only a small number of QC items will exist per batch and presumably only those being actively displayed will be queried (and thus calculated). Also, this is an interim solution until we have the bandwidth to develop a web application backed by a relational database.

                         

                        @dtcgnet

                         

                        Yeah, I had the same thought and have actually already fed this back to the person doing the design in Filemaker. I've just been asked to help with the underlying database because I understand relational database design, although I've not found those skills that useful with Filemaker.

                         

                        Thanks all,

                         

                        I am going with @dtcgnet's answer for the time being.

                        • 10. Re: Calculation to get the value of a field based on Max of another field
                          Mike_Mitchell

                          dpwrussell wrote:

                           

                          The idea of copying a field within a database when a simple JOIN operation would normally (i.e. in a SQL database) internally optimize this to be very efficient, just seems crazy to me.

                           

                          Yeah ... that's why you use a relationship, which is FileMaker's equivalent of a JOIN. It can be indexed, so the cached set of join records is identified "very efficiently". But when a calculation can't be indexed, performance can becomes a problem, which is why stored data outperform unstored data. (Note: You can get very good results with a simple SQL call, but those results do not return as a field by default. They return as a text object.)

                           

                          We are not overly concerned about performance as only a small number of QC items will exist per batch and presumably only those being actively displayed will be queried (and thus calculated).

                           

                          Incorrect. A sorted relationship fetches all the records, not just the visible one. A portal fetches in batches of 25, unless it's sorted. How else does it know which record to put "first"?

                           

                          Yeah, I had the same thought and have actually already fed this back to the person doing the design in Filemaker. I've just been asked to help with the underlying database because I understand relational database design, although I've not found those skills that useful with Filemaker.

                           

                          FileMaker IS a relational database. It's just not a SQL database. It requires a different paradigm, but delivers quite nice results with a minimum of effort.

                          • 11. Re: Calculation to get the value of a field based on Max of another field
                            dtcgnet

                            dpwrussell wrote:

                             

                            The sort and reference idea suggested by @dtcgnet seems to work, although this feels like a fairly dubious relationship to trust. Is it guaranteed to be the case that a field referencing a foreign table which is 1-M always displays the field value from the first matching record?

                             

                            I pointed out a FileMaker behavior, but I would not actually use that method. If you add a field from the QC table to a layout based on the Batch table, it WILL display the data from the first related record (which will be dependent on the sort used in the relationship), but that's just the behavior. I don't think it's a robust solution to what you're after. Further, if someone ever builds another layout based on the same tables but in a different context and neglects to sort the relationship in the same way, the data shown would be different (it would show the result of the very first QC test on that batch).

                            dpwrussell wrote:

                             

                            The sort and reference idea suggested by @dtcgnet seems to work, although this feels like a fairly dubious relationship to trust. Is it guaranteed to be the case that a field referencing a foreign table which is 1-M always displays the field value from the first matching record?

                             

                            As requested, here is the real life scenario. We have batches of things (in this case, small molecule perturbagens) that undergo quality control (QC) testing. These batches are used up slowly over months or years and QC is normally run on them many times before the batch is used up.

                             

                            Screen Shot 2016-03-28 at 17.28.50.png

                             

                            In the layout for Small Molecule Batch we want to be able to see the status (for the batch as a whole) of the most recent QC test. So if the most recently dated test has "FAIL" in "QC Outcome", then this field in the batch layout will show that. There are several states (PASS, FAIL, QUARANTINE are the most common) and any of these can be the most recent. It is very possible for there to be a series of QC entries that are FAIL -> PASS -> FAIL -> PASS for instance. We are just interested in the most recently dated one.

                             

                            I am unwilling to implement any solution which involves scripting to copy the QC Outcome into a field in the batch table as that is a brittle solution.

                             

                            It seemed to me that the way to do this was to have a calculated field in batch which basically ran a query of the underlying QC table to display the most recent QC Outcome as determined by the QC Date field.

                             

                            I hope that makes sense.

                            I had to look up perturbagens. But the real life scenario is 1) very helpful, 2) points out how critical your data is, and 3) points out how static your data is. When you do a QC test on a sample, the results of that QC test will never change. The next QC test might have a different result, but none of the earlier results will ever change. Therefore...you don't need a live calculation field. I wouldn't use one.

                             

                            In your workflow...I imagine that as soon as a sample is QC'd, the results are entered. You don't have a stack of QC tests, and enter them all one after the other, is my guess. If you enter the results as you go, then every time you enter a result, that result IS the most recently dated one. Take advantage of 1) your workflow and 2) the strengths of FileMaker. Add an OnObjectSave script trigger to the QC Outcome field. Whenever a test is entered, your script 1) checks to make sure the data is acceptable, 2) sets Current QC Outcome to the value of QC Outcome in the batch table, and exits. If a mistake was made and the user changes the value from pass to fail, the data would automatically update.

                             

                            With a calculation field, your Small Molecule Batch table would need to examine all related records for a given batch every single time you looked at the Last Outcome field. With the method I described (and Mike suggests, too, I think) the data would be Indexed, nearly instantly accessible, accessible from other contexts, very stable, and very accurate.

                             

                            My 2 cents.