1 2 Previous Next 18 Replies Latest reply on Mar 1, 2010 10:22 AM by jenh

    How to display/print the lowest and highest value only of a field range in a found set.

    jenh

      Title

      How to display/print the lowest and highest value only of a field range in a found set.

      Post

      I have FileMaker Pro 10X on Windows Vista.  

       

      I get a successful found set on a find. 

       

      There is a sub summary set on a date.  Works great,  gathers all the records I need by date. 

        

      Within each date there are numerous sequential records. 

      I only want to get the first and last record of the sequential records within the sub summary date. 

      There is a field within the found data that is sequentially numbered however the numbers of the field are always different ie: 1 -150, 533-622, or 212-230. 

      I just want to capture (Display/Print) the first and last numbered fields only within that sub summary range, ie: Record 1 and 150 or Record 533 and 622.  

       

       

      Sample of solution would be  

       

      01/01/2010

       

      File #  9  

      File # 15

       

      as opposed to

       

      01/01/2010

       

      File # 9

      File # 10

      File # 11

      File # 12  

      File # 14

      File # 15

       

       

       

      Any suggestions or references would be appreciated.   TY  

       

       

        • 1. Re: How to display/print the lowest and highest value only of a field range in a found set.
          mrvodka
             Use two new summary fields but instead of total use Min and Max. Put these two fields in the part.
          • 2. Re: How to display/print the lowest and highest value only of a field range in a found set.
            jenh
              

            Thank you.  Works great. 

             

            I need to display just those two records.  The first and last.  (Currently all records in between display.)  Any sugggestions ?

             

            also

             

            The fields displaying the value of the first record and last do not start over with the subsummary value.  For example: in a found set the field value is 9 - 622, which is what the new fields depict.  I need to get values on each subsummary break.   

             

            01/01/2010

            9 - 150 

             

            01/10/2010

            153- 200  

             

            Thanks again!  Any reference or suggestion greatly appreciated!

            • 3. Re: How to display/print the lowest and highest value only of a field range in a found set.
              mrvodka
                

              For #1. The only way would be to omit the rest of the records that you do not want to display. You could remove the body part to only show the subsummary data but I am not sure if you could work with that.

               

               

              For #2. I am not sure what you mean. If you have your summary fields with Min and Max on the subsummary part, they will only be specific to each group, grouped by the break field.

              • 4. Re: How to display/print the lowest and highest value only of a field range in a found set.
                jenh

                Thank you.  Your suggestions have been very helpful. 

                 

                I only want to see the 2 fields affiliated  with the records that are the max and min.  values. 

                Maybe a script could be applied to eliminate all the records in between the the records with the max and min values. 

                • 5. Re: How to display/print the lowest and highest value only of a field range in a found set.
                  comment_1

                   


                  jenh wrote:

                  I only want to see the 2 fields affiliated  with the records that are the max and min.  values.


                   

                  Which 2 fields? So far, you have only mentioned a date field and a value field.

                   


                  • 6. Re: How to display/print the lowest and highest value only of a field range in a found set.
                    jenh

                    Rephrased.  The (selected)  fields associated with the 2 records that hold the minimum value and maximum value. 

                    Both these records will hold  the same vaue in the date field in sub summary.  

                     

                    The sub summary on date currently set on is actually only sorting by month.. for example:

                     

                    if I have sub summary dates:

                     

                    01/02/07

                    12/24/07

                    05/11/09

                     

                    The sort will put in the following order

                     

                    01/02/07

                    05/11/09

                    12/24/07 

                     

                    TY..  

                    • 7. Re: How to display/print the lowest and highest value only of a field range in a found set.
                      comment_1

                       


                      jenh wrote:

                      Rephrased.  The (selected)  fields associated with the 2 records that hold the minimum value and maximum value. 


                      I am afraid that only makes me more confused. The date field is the same for the two records, and the two summary fields show their respective values, e.g.

                       

                      1/1/2000

                      • Min: 55

                      • Max: 101

                      1/2/2000

                      • Min: 34

                      • Max: 95

                      ...

                       

                      What additional information do you want to show in this report?

                       

                       


                      jenh wrote:

                      The sub summary on date currently set on is actually only sorting by month.


                      It looks like your date field is actually a field of type Text, not Date.

                       



                      • 8. Re: How to display/print the lowest and highest value only of a field range in a found set.
                        jenh

                        Hi again and thank you.

                         

                        I changed the date to date format and not text and the sub summary on date no longer worked.. I changed it back to text which works but as stated in prior message not sequentially.  

                         

                        In addition to the records that hold the minimum value and the maximum value I'd need to display a few additional fields that are in the same record as the records that hold the minimum and maximum value.  

                         

                        Let me know if there is a way to manipulate the date so that it summarizes sequentially..  

                         

                        Thanks again! 

                        • 9. Re: How to display/print the lowest and highest value only of a field range in a found set.
                          comment_1

                           


                          jenh wrote:

                           

                          In addition to the records that hold the minimum value and the maximum value I'd need to display a few additional fields that are in the same record as the records that hold the minimum and maximum value.


                           

                           

                          A simple solution: add another sub-summary part by Date to your layout, so that you have two of them - one leading and one trailing. Place the fields you want to show, including the value, in these two parts (one instance of each field in each part).

                           

                          When you sort your records by date AND by value, the leading subsummary will show data from the record with the minimum value, and the trailing subsummary from the record with the maximum (you don't need the summary fields for this).

                           

                           

                           


                          jenh wrote:

                          I changed the date to date format and not text and the sub summary on date no longer worked..


                           

                          The field must be a Date field or it won't sort correctly. I am not sure if you have valid date entries, though. Try changing it to Date again, and do a find for ! (exclamation mark) in the field. Any records found have invalid dates.

                           

                           

                           

                           



                          • 10. Re: How to display/print the lowest and highest value only of a field range in a found set.
                            philmodjunk

                            One small correction:

                             

                            To find your invalid dates use ? instead of the exclamation point.

                            • 11. Re: How to display/print the lowest and highest value only of a field range in a found set.
                              comment_1

                              Indeed. That's what comes from noisy neighbors. Thanks for the catch.

                              • 12. Re: How to display/print the lowest and highest value only of a field range in a found set.
                                jenh

                                Hi and thank you.  

                                 

                                I have changed the date field to the date type and it doesn't break/sort at all.   I also tried the timestamp type and same results. 

                                (The type of the sort/break field is actually a timestamp type)

                                 

                                The date values are all good values.  

                                 

                                 

                                  See the sample below; it seems the field when set as text does sort/break but by the value of the month only.  (1st 2 digits) 

                                 

                                Also the sample below depicts data I don't want to see .. ie:  I just want to see

                                "05/11/09 15:10:54"  Minimum: 478 "0X10" "12/13/2008" "8"  not all the records in between the 5/11/09 date,  minimum value field and maximum value field.  (In the light grey italics font)   I have taken your suggestions and created leading and trailing subsummary but if there is not  a body part or anything in the body, nothing shows at all.  

                                 

                                 

                                Thanks for your time, effort and patience.    Greatly appreciated.   

                                 


                                "05/11/09 15:10:54" Minimum: 478

                                 

                                "0x10" "478" "12/13/2008" "8"
                                "0x10" "479" "12/14/2008" "8"
                                "0x10" "480" "12/26/2008" "8"
                                "0x10" "481" "12/27/2008" "8"
                                "0x10" "482" "12/28/2008" "8"
                                "0x10" "483" "12/31/2008" "8"
                                "0x10" "484" "01/01/2009" "8"
                                "0x10" "485" "01/04/2009" "8"
                                "0x10" "486" "01/04/2009" "8"
                                "0x10" "488" "01/09/2009" "8"
                                "0x10" "489" "01/10/2009" "8"
                                "0x10" "490" "01/12/2009" "8"
                                "0x10" "491" "01/17/2009" "8"
                                "0x10" "492" "01/18/2009" "8"
                                "0x10" "493" "01/21/2009" "8"
                                "0x10" "494" "01/22/2009" "8"
                                "0x10" "495" "01/23/2009" "8"
                                "0x10" "496" "01/24/2009" "8"
                                "0x10" "497" "01/24/2009" "8"
                                "0x10" "498" "01/24/2009" "8"
                                "0x10" "499" "01/25/2009" "8"

                                "0x10" "500" "01/27/2009" "8"

                                 

                                "05/11/09 15:10:54" Maximum: 500

                                 

                                "12/12/08 08:45:42" Minimum: 450

                                 

                                "0x10" "450" "10/18/2008" "7"
                                "0x10" "451" "10/17/2008" "7"
                                "0x10" "452" "10/18/2008" "7"









                                • 13. Re: How to display/print the lowest and highest value only of a field range in a found set.
                                  comment_1

                                  If you want to group your records by date, then you cannot use a timestamp or text field as the breakfield. I am not getting a clear picture from you as to what your data actually is. If it's a timestamp, and you need to group by date, then the breakfield should be a calculation field - returning only the date portion of the timestamp.


                                  jenh wrote:

                                   

                                  if there is not  a body part or anything in the body, nothing shows at all. 


                                  There could be two reasons for that: (a) you didn't put any fields in the subsummary parts; or (b) the subsummary parts are not showing at all, because you didn't sort the records by the breakfield.

                                   

                                   


                                  • 14. Re: How to display/print the lowest and highest value only of a field range in a found set.
                                    jenh

                                    Great!  Creating a calc field to convert the timestamp to date made all the difference in the breakfield/sort loading in the correct sequential order!  

                                     

                                    Although still unable to get the subsummary parts to show the fields without a body. If there is something in the body it will show those fields and the subsummary field.   I've tested sorting the subsummary break field as well.   

                                     

                                    I'm sorting on the subsummary field (break field) . 

                                     

                                    Thanks for the help.  Almost there. 

                                     

                                    1 2 Previous Next