8 Replies Latest reply on Apr 18, 2016 5:03 PM by RickSR

    Calculate the number of days in a found set that contains one 'Date' field,

    RickSR

      I have been trying to find a way to calculate the number of days in a found set that contains one 'Date' field (which is the creation date of the record and is in fact a date-type field).

       

      I have created a database to track medication use and want to be able to find the average number of a particular medication taken between a range of dates. Each record has a "Date" field(date), a "Medication" field (text), and a Quantity (number). When performing a find of a range of dates I would like to be able to calculate the average number of pills(Medication) consumed between the oldest and newest records in the found set. I need to divide the sum of the Quantity by the Total of the Number of Days.

       

      Since the Date field that I need to use for this calculation is located in two separate records, separated by a variable number of records I need the first and last record of the found set to be calculated into "Total Number of Days" field a Sub-summary field. I don't have a problem performing the find and sorting, and I have a sub-summary part created in the layout. I just don't know how to calculate the total number of days.

       

      Though I have been using Filemaker for many years this solution is beyond my ability and may also require a script, I just don't know, after hours of searching through reference books and in the Filemaker Community Discussions boards as well as many failed attempts I have not be able to find anything that applies to my particular application.

       

      Thanks in advance or any help that you might offer,

       

      Rick

        • 1. Re: Calculate the number of days in a found set that contains one 'Date' field,
          Mike_Mitchell

          Hello, Rick. There are at least few ways to accomplish this.

           

          1) Use a script, as you mentioned, to just set the field. Possibly using a Script Trigger.

           

          2) Create another table with a single record and nothing but global fields (like a Connector), or one field per user (sessions model), and use a relationship to fetch the data.

           

          3) Use the Minimum and Maximum summary field types and a GetSummary function to make a calculation field that determines what you need. For example:

           

               GetSummary ( dateMax ; dateMax ) - GetSummary ( dateMin ; dateMin )

           

          where dateMax is a summary field finding the maximum creation date, and dateMin is a summary field finding the minimum creation date. (Warning: this might be slow if you have lots of records.)

           

          HTH

           

          Mike

          • 2. Re: Calculate the number of days in a found set that contains one 'Date' field,
            RickSR

            Thanks for fast response Mike, I'm afraid your talking a bit over my head so I will have to spend some time figuring out how to apply the solutions you have proposed.

             

            I only have a couple of hundred records a present but that is growing. The third option looks like something I could try fairly quickly. I'll let you know how it turns out.

             

            thanks again,

            Rick

            • 3. Re: Calculate the number of days in a found set that contains one 'Date' field,
              erolst

              In addition to what Mike mentioned, try this calculation field:

               

              Case (

                medication ≠ GetNthRecord ( medication ; Get ( RecordNumber ) - 1 ) ;

                Let ( [

                  startDate =  theDate ;

                  countThisGroup = GetSummary ( sCountAll ; medication ) ;

                  endDate = GetNthRecord ( theDate ; Get ( RecordNumber ) + countThisGroup - 1 ) ;

                  totalMedicationThisGroup = GetSummary ( sTotalOfDosage ; medication )

                  ] ;

                 totalMedicationThisGroup / ( endDate - startDate + 1 )

                )

              )

               

              and put it in your sub-summary part.

               

              Required bits:

              • a summary field, type TotalOf:dosage, and a summary field CountOf:<anyNonEmptyField>

              • the found set is sorted by medication, and by date, ascending

              • 4. Re: Calculate the number of days in a found set that contains one 'Date' field,
                RickSR

                I'm afraid I'm too inept to make this command work. I must be missing something and I suspect I'm too much of a novice to understand some of the basic functions that you have incorporated in your formula. 

                 

                I tried pasting your formula into a calculation field I created for this purpose but when I tried to save it Filemaker doesn't like a number of the items in the command string. The first thing it questions is "theDate" it says it can't find that field, it also doesn't like the sCountAll or the sTotalOfDosage. Do I need to change some of the text of field names or create additional fields to make it work with my application or are these commands that Filemaker is supposed to recognize and preform some action when they are phrased as you have done in your reply?

                 

                I'm afraid I'm a bit lost.

                • 5. Re: Calculate the number of days in a found set that contains one 'Date' field,
                  erolst

                  RickSR wrote:

                  Do I need to change some of the text of field names or create additional fields to make it work with my application or are these commands that Filemaker is supposed to recognize and preform some action when they are phrased as you have done in your reply?

                   

                  A calculation is usually a mix of (built-in or custom) functions that operate on values that you get by referencing field (and other objects) from your database. In the changed code below, functions are the bits in italics.

                   

                  So e.g. GetNthRecord ( … ; … ) is a function, but the arguments (in parentheses) are fields or variables.

                   

                  You need to make sure you have created/performed the components described in “Required bits”.

                   

                  Then change the references in my calculation to the corresponding fields in you solution – the bits in bold. I now used the field names you listed in your original post, so you only need to make sure you have the two summary fields. If you name them the same, then you should be able to simply paste this calculation.

                   

                  Case (

                    MedicationGetNthRecord ( Medication ; Get ( RecordNumber ) - 1 ) ;

                    Let ( [

                      startDate =  Date ;

                      countThisGroup = GetSummary ( sCountAll ; Medication ) ;

                      endDate = GetNthRecord ( Date ; Get ( RecordNumber ) + countThisGroup - 1 ) ;

                      totalMedicationThisGroup = GetSummary ( sTotalOfQuantity ; Medication )

                      ] ;

                     totalMedicationThisGroup / ( endDate - startDate + 1 )

                    )

                  )

                   

                  In English:

                  if (“in case”) the record is the first one in a sub-group (the first “new” medication)

                    declare a variable to hold the earliest date (from this first record per the sort order)

                    declare a second variable to hold the record count in this sorted group

                    using that information, declare a third variable to get the latest date (from the last record as per sort order)

                    declare a fourth variable that hols the total quantity in this sorted group

                    use variables one, three and four to calculate the result of this entire formula

                  [implicit else: do nothing if it's not the first record – which isn't really necessary, because you place this field in the sub-summary part, which only reflects values from the first record in the sub-group anyway]

                   

                  Understand that field names like “sTotalOfQuantity” or “sCountAll” are supposed to be generic names that describe what these field are/doing – summary fields that total or count another field. So you need to make sure you have these fields – it doesn't matter what you call them as long as you reference them in the correct place.

                  • 6. Re: Calculate the number of days in a found set that contains one 'Date' field,
                    RickSR

                    Thanks for all your help erolst, but I'm still not getting a return so I presume I haven't got the correct field settings for the summary fields your calling "sTotalOfQuantity" for which I have a field summing the field I called "Quantity". And the one I'm really struggling with "sCountAll". I'm not sure what field I should be counting. I've tried Medication,  Date and Quantity but none of them seem to work. Sorry I'm being such a bother but I think you've got me close to what I want to do but I just can't quite make it work.

                    • 7. Re: Calculate the number of days in a found set that contains one 'Date' field,
                      erolst

                      RickSR wrote:

                      I'm really struggling with "sCountAll". I'm not sure what field I should be counting. I've tried Medication,  Date and Quantity but none of them seem to work.

                       

                      Basically, count any field that will never be empty (normally, one would use the table's primary key), because that will tell you how large your group is.

                       

                      Anyway, just have a look into the attached sample file; that should make things clearer.

                      • 8. Re: Calculate the number of days in a found set that contains one 'Date' field,
                        RickSR

                        Ah Ha! Now I see what I was doing wrong. I wasn't phasing the GetSummary arguments correctly.

                         

                        Thank you so much for taking the time to put this together for me, it was a real help. You've opened my eyes to a whole new range of possibilities.

                         

                        I very much appreciate your help erolst.