6 Replies Latest reply on Oct 23, 2013 8:11 AM by ThomasM_1

    How to get a summary over last 10 records only?

    ThomasM_1

      Title

      How to get a summary over last 10 records only?

      Post

           I need to know the average of field A for the last 10 records (based on a date field B) whose field A is not empty. I need to have this information in a field and cannot use a script performing a search for the last 10 records.

           I thought of using a summary field on A with a weighted average relative to a calculation field. This calculation field should be boolean and simply indicate whether the record is part of the last 10 records. However, I could not find a way setting up this calculation field. Maybe there is even another and better approach. Please let me know.

        • 1. Re: How to get a summary over last 10 records only?
          philmodjunk
               

                    and cannot use a script performing a search for the last 10 records.

               You probably can use a script, but I doubt that it is necessary.

               I need to know more about the context involved here. Can you describe what you want in more detail?

               In particular, are these "last 10 records" the last 10 records in your report's found set or are they records in a related table such as you might show in a portal?

          • 2. Re: How to get a summary over last 10 records only?
            ThomasM_1

                 This is still for the questionnaires part of my database. I have this table structure: 

                 Clients ---< ClientMeasurements ---< Responses >--- Questions >--- Questionnaires

                 In ClientMeasurements a field A holds time it took for a client to complete a particular questionnaire. In this table I have also added a summary field S calculating the average time it takes to complete a ClientMeasurement. 

                 Placing the summary field S on a layout based on Questionnaires (a list view of available questionnaires) it correctly shows the average time it took in the past to complete each questionnaire. Now I want S to only show the average over the last 10 ClientMeasurements per Questionnaire. The main reason for this is that with practice value of A will likely change over time. 

                 As there is no found set involved I'm afraid I could only accomplish this with the help of ExecuteSQL, however, I could filter as required and I could sort records based on date field B in ClientMeasurements but I could not find a way to actually get or mark the last 10 records.   

            • 3. Re: How to get a summary over last 10 records only?
              philmodjunk

                   Now I want S to only show the average over the last 10 ClientMeasurements per Questionnaire.

                   Does this mean that you want to see this computed average for more than one questionaire at a time. Ie. the last 10 average for Questionnaire A and the last 10 average for Questionnaire B, etc. at the same time?

                   What data in your records tell you which records are the most recently created? Do you have a field that records a timestamp or auto-entered serial number value?

                   ExecuteSQL could be used, nested inside a RightValues function call to get the last 10 records, but it's not the only possible approach here.

              • 4. Re: How to get a summary over last 10 records only?
                ThomasM_1
                     

                Does this mean that you want to see this computed average for more than one questionaire at a time. Ie. the last 10 average for Questionnaire A and the last 10 average for Questionnaire B, etc. at the same time?

                Yes. It's a layout in list view listing all questionnaires based on Questionnaires where information like questionnaire name and the computed duration is shown.

                     

                What data in your records tell you which records are the most recently created? Do you have a field that records a timestamp or auto-entered serial number value?

                I have a field in ClientMeasurements that records a timestamp. This can be empty for planned measurements in the future.

                      

                • 5. Re: How to get a summary over last 10 records only?
                  philmodjunk
                       

                  I have a field in ClientMeasurements that records a timestamp. This can be empty for planned measurements in the future.

                  I read that to mean that each record that exists in ClientMeasurements has a timestamp (date and time) that records when that record was created and/or last updated and that the "last 10 records" would be the 10 records linked to a given Questionnaire record with the largest timestamp value.

                  In many, cases, an unsorted relationship will show these as the last 10 related records as unsorted records list automatically in the order in which they were created. I'm going to use a sorted relationship that sorts on this field, to be sure that I get correct results, but you may find that the sort specification is not needed.

                       Restating your relationship, but listing Questionnaires first as it will be the TO for the layout used:

                       Questionnaires---<Questions ---< Responses >---ClientMeasurements

                       A portal to ClientMeasurements placed on questionnaires should list all ClientMeasurements records for the given questionnaire. If you encounter trouble with the following, temporarily putting such a portal on the layout can help confirm that all is working correctly at the relationship level.

                       Double click the relationship line between Responses and ClientMeasurements to open the Edit Relationship dialog. Specify a sort order for ClientMeasurements that sorts the records in ascending order by your TimeStamp field.

                       This calculation, defined in Questionnaires, should do the trick:

                       Let ( [ Values = RightValues ( List ( ClientMeasurements::ValueFieldHere ) ; 10 ) ; // get list of up to last 10 values
                                 Values = Left ( Values ; Length ( Values ) - 1 ) ;                                             //strip off extra return added by RightValues function
                                 Cnt = ValueCount ( Values ) ;
                               ]

                               Evaluate ( "Sum ( " & Substitute ( Values ; ¶ ; " ; " ) & " ) " ) / cnt   //Turns list of values into "Sum ( value1 ; value2 ; ...)" and evaluates it
                              )

                        

                  • 6. Re: How to get a summary over last 10 records only?
                    ThomasM_1

                         Thanks, Phil. This is exactly what I was looking for.

                         I just needed to make sure that the evaluation runs only if 2 or more values are available which reads like this:

                    Let ( [
                         Values = RightValues ( List ( ClientMeasurements::ValueFieldHere ) ; 10 ) ; 

                    Values = Left ( Values ; Length ( Values ) - 1 ) ; 
                    Cnt = ValueCount ( Values ) 
                    ] ;
                         If ( Count ( ClientMeasurements::ValueFieldHere ) > 1 ; 
                    Evaluate ( "Sum ( " & Substitute ( Values ; ¶ ; " ; " ) & " ) " ) / Cnt ;
                         "" )

                    )