12 Replies Latest reply on May 9, 2013 7:58 AM by user17753

    Add fields based on criteria of records

    user17753

      I am trying to come up with a way to sum a field in a record set based on the following:

       

      f_Date f_Time f_number

      1/1/2013 01:00 100

      1/1/2013 02:00 125

      1/1/2013 03:00 75

      1/1/2013 04:00 150

       

      What I would like to do is to add the numbers in the f_number field based on the f_Time field. If the f_Time field is between 01:00 and 12:00 add the numbers in f_number field. If the f_Time field is between 01:00 and 17:00 add the numbers in the f_field then if the f_Time field is between 01:00 and 24:00 add the numbers in the f_field.

       

      The records in the f_Time field are from 01:00 to 24:00 as in 24 hours in a day.

       

      The date is duplicated in each record to facilitate someone exporting to Excel and be able to manulipate data better. I have set it up as a flat database but I am certianly open to setting it up differently.

       

      Any help anyone could provide would be greatly appreciated as I seemed to be stumped.

        • 1. Re: Add fields based on criteria of records
          Mike_Mitchell

          So if I understand the question correctly, you want three sums:

           

          1) Total of f_number for 01:00 <= f_Time <= 12:00

          2) Total of f_number for 01:00 <= f_Time <= 17:00

          3) Total of f_number for 01:00 <= f_Time <= 24:00

           

          presumably for a given date, yes?

           

          If that's correct, there are a few different ways to do it. You can:

           

          1) Set up three self-joining relationships, with hard-calculated fields for the boundary times and using the date field on the current record. The number total then equals Sum ( selfJoin::f_number ).

           

          2) Set up a single self-joining relationship, using a pair of global time fields for the boundary times. This is easier on your relationship graph, but requires you to populate the global fields (such as via script) in order to generate the values.

           

          3) Use ExecuteSQL ( ) to create your totals. This is probably my preferred method, but requires writing a little SQL code. Example:

           

          ExecuteSQL ( "SELECT SUM ( f_number ) FROM table WHERE f_Date = ? and f_Time BETWEEN 01:00 AND 12:00" ; "" ; "" ; f_Date )

           

          (You might have to play with the time formats a bit; not sure how ExecuteSQL parses time.)

           

          HTH

           

          Mike

          • 2. Re: Add fields based on criteria of records
            user17753

            I like the idea of the three self joining relationships. This is because the people that will be entering this data will have the program open 24 hours per day.

             

            When I self-join, I have joined the f-Date - f_Date and f_Time - f_Time for two different self join relationships. Does this sound correct?

            • 3. Re: Add fields based on criteria of records
              Mike_Mitchell

              What you'll need to do is define four calculation fields on the parent side to serve as keys:

               

              oneAM = GetAsTime ( "01:00" ) (time)

              noon = GetAsTime ( "12:00" ) (time)

              fivePM = GetAsTime ( "17:00" ) (time)

              midnight = GetAsTime ( "24:00" ) (time)

               

              Then, you're going to create relationships based on multiple criteria:

               

              Date = Date

              oneAM <= f_Time

              noon >= f_Time

               

              This will give you all records between 1:00 and 12:00 on the date referenced in the current record. Similarly, you'll have:

               

              Date = Date

              oneAM <= f_Time

              fivePM >= f_Time

               

              for the second case and

               

              Date = Date

              oneAM <= f_Time

              midnight >= f_Time

               

              for the third. Then, you can use calculation fields in your parent table equal to:

               

              Sum ( betweenOneAndNoon::f_Number )

              Sum ( betweenOneAndFive::f_Number )

              Sum ( betweenOneAndMidnight::f_Number )

               

              (Notice that, in the third case, you could strictly use the date, assuming you have no records between 00:01 and 00:59. But I wrote it this way for consistency.)

               

              Make sense?

               

              Mike

              • 4. Re: Add fields based on criteria of records
                user17753

                Mike,

                This is making sense just fine but putting it in the database gets to be a challenge for me. I am not what you would consider a "Power Developer" so I do appologize if I don't seem to be getting it.

                 

                What I have done is to create a field:

                 

                f_ONEAM = GetAsTime ( "01:00" ) = (table::f_Time)

                f_NOON = GetAsTime ( "12:00" ) = (table::f_Time)

                 

                This comes up as "0" in the calculation, even in the records that have numbers in the f_Number or f_Time field. Did I do this correctly or am I supposed to come up with a number?

                 

                The relationship I created looks like this:

                 

                          f_Date = f_Date

                and     ONEAM <= f_Time

                and     NOON >=f_Time

                 

                When I set my calculation field to the relationship table for the NOON calculation I come up with nothing.

                 

                SumNoon = Sum (table::f_Number)

                 

                I think this may be where my problem is.

                • 5. Re: Add fields based on criteria of records
                  Mike_Mitchell

                  Your calculations are off. They should look like this:

                   

                  GetAsTime ( "01:00" )

                  GetAsTime ( "12:00" )

                   

                  That's it. They're constants. (You can even set them as global calculations if you like; won't affect anything.)

                   

                  Mike

                  • 6. Re: Add fields based on criteria of records
                    user17753

                    Ok,

                    I have the OneAM, Noon, FivePM and MIDNIGHT fields set to "GetAsTime ( "01:00" )", etc.

                     

                    The relationships that I have created look like:

                     

                              f_Date = f_Date

                    and     ONEAM <= f_Time

                    and     NOON >=f_Time

                     

                              f_Date = f_Date

                    and     ONEAM <= f_Time

                    and     FIVEPM >= f_Time

                     

                              f_Date = f_Date

                    and     ONEAM <= f_Time

                    and     MIDNIGHT >= f-Time

                     

                    The relationships have on the Table 2 side, "Allow creation of records in this table via the relationship" as checked.

                     

                    Then I created calculation fields for SumNoon = "Unstored, from table 2, =Sum (table 2::f_Number) with "Do not evaluate if all referenced fields are empty" is unchecked. I created one for FIVEPM and MIDNIGHT as well.

                     

                    When I put the "SumNoon" calculated field into a Portal on the layout with the other fields I get a "?".

                     

                    Any ideas as to what I am doing wrong?

                     

                    Thanks

                    • 7. Re: Add fields based on criteria of records
                      Mike_Mitchell

                      Oh, you're so close!

                       

                      Problem is here

                       

                      SumNoon = "Unstored, from table 2, =Sum (table 2::f_Number)

                       

                      Should read:

                       

                      SumNoon = "Unstored, from table 1, =Sum (table 2::f_Number)

                       

                      Why? Because the "from table 2" part is telling FileMaker, "Evaluate this calculation as if you were sitting on the other table over there." So, since the calculation is evaluating inside table 2, it's completely ignoring the relationship; it's just evaluating inside table 2. Evaluating it from the parent table will fix the problem.

                       

                      Mike

                      • 8. Re: Add fields based on criteria of records
                        user17753

                        That's it!

                         

                        Thank you very much for your help. I think I am getting this a little better now. I am going to spend some time really studying the Self-Join relationship because I really think it will help my database designs.

                         

                        Thanks again.

                        • 9. Re: Add fields based on criteria of records
                          user17753

                          I think I jumped too soon.

                          Now this only displays the data from the 1:00 hour. Not from the other hours.

                           

                          Not sure how I made that happen.

                          • 10. Re: Add fields based on criteria of records
                            user17753

                            I might also need to tell you that all 24 hours in a day are on one layout in "List" mode using a Find for the specific day.

                            • 11. Re: Add fields based on criteria of records
                              Mike_Mitchell

                              Try the attached. A picture is worth a thousand words.    

                              • 12. Re: Add fields based on criteria of records
                                user17753

                                Thanks Mike,

                                That made all the difference. What I found was that I did not label my Table Occurrences in the Relationship correctly. Once I saw how you did that then it all came into focus.

                                 

                                Thanks again.

                                Wayne