11 Replies Latest reply on Apr 4, 2012 3:07 PM by philmodjunk

    Summary or Calculation for Three Fields by Date

    VanessaAnne

      Title

      Summary or Calculation for Three Fields by Date

      Post

      Hello, 

      I have three fields that I am trying to get a total count of for a certain date.  Basically, everyday I submit a report listing that day's total of three fields.   The total comes from the amount of records that are entered for each field.  Right now, it seems to be counting every date that is entered, instead of the current date.  

      So: 

      TODAY - COUNT OF: 

      FEILD A  (lets say it has 2 records)

      FIELD B  (lets say it has 4 records)

      FIELD C  (lets say it has 1 record) 

      So the total for that day would be 7.  It could also work if I search for the date, and the total for that date is the result.  

      Hopefully this makes sense.  There are no related tables involved, it is just one.  

      Thank you!

        • 1. Re: Summary or Calculation for Three Fields by Date
          philmodjunk

          A field cannot "have 2 records" so I'm not clear on what data you have that you are counting.

          Do you mean that if you have

          3 records with "apple" in Field A and "carrot" in Field B and "rock" in field c, you want to get a count of all records where

          Field A = "Apple" OR field B = "carrot" OR field C = "rock"?

          If so, what would the count be if one record shows "Apple" in field A and "Carrot" in Field B? Would you need to count that record twice or just once?

          • 2. Re: Summary or Calculation for Three Fields by Date
            VanessaAnne

            Yes, that is what I mean - a count of however many occurances of Field A = Apple + Field B = carrot + C = rock.  And I would count each individually.  So for your scenario with apple and carrot both showing up, it would count as two.  But then I need to make them unique by date as an automatic calculation.  

            So if today there are (2) Field A - Apples, (2) Field B - Carrot, (2) Field C- Rock - making a total of 6.  And yesterday there was one of each, making a total of 3.  I would want to be able to search for yesterday and just get a count of 3, or today and get a count of 6.  

            • 3. Re: Summary or Calculation for Three Fields by Date
              philmodjunk

              I would assume that each record has a date field (and it's of type date, not another data type such as text...)

              Are these counts always for the same three values? Or could it be a count for different values specified by the user?

              Here's the basic approach, but there are several different ways to get it to work. Counting the same records twice or even three times if it has all three specified values eliminates using a summary report here, but we can still do this with either relationships or a filtered portals.

              Define a Summary field as the "count of" a field in your table that is never empty such as your date field.

              Your layout can be based on a different table or the same table where you have defined fields A, B and C. If it's the same table, you'll need to create a second occurrence of this table so that we can set up a relationship. If you don't know how to do that, let me know.

              Define this relationship:

              LayoutTableOccurrence::gSelectedDate = YourTable::DateField.

              Place three one row portals to YourTable on your layout. gSelecteDate field can be a date field with global storage (optional) where you select the date for which you want to see these counts.

              In the first portal, define this filter expression:

              YourTable::FieldA = "Apple"

              Put the "count of" summary field in this single portal row.

              Repeat this for the other two portals, but use filter expressions that limit the related records by Field B and Field C values respectively.

              This method requires FileMaker 11. If you are using an older version, we can still get these counts, but you'll need a separate relationship for each count that includes the FieldA values. Let me know if you need this and I'll give the details.

              • 4. Re: Summary or Calculation for Three Fields by Date
                VanessaAnne

                I just realized I didn't actually explain it correct.  The three values are actually all options for the same field. (sorry, long day).  So I should start over. 

                Results: Apple, Carrot, and Rock are all options for Field A.  There are also other value possibilities - i.e. orange and paper.  I only want to get the count of how many times apple, carrot, and rock occur in that field for that day.  

                And I'm using Filemaker 7.  

                Also, what do you mean by "type date" - my date field is an indexed number. 

                Sorry :( 

                • 5. Re: Summary or Calculation for Three Fields by Date
                  philmodjunk

                  Date fields should be defined as date fields when you access them in Manage | database | Fields.

                  Please note that you are using a much older version of FileMaker. Please mention that detail in any future posts you might make in a forum like this so that we don't suggest a solution that is not compatible with your version of the database.

                  Is this a check box field where multiple values can be selected in the same field?

                  • 6. Re: Summary or Calculation for Three Fields by Date
                    VanessaAnne

                    My apologies.  I've posted questions before and got answers without divulging which version I'm using.  I'll remember next time. 

                    The field is a pop up menu listing options from a defined value list.

                    And then yes, my date field is defined as a date field.  

                    • 7. Re: Summary or Calculation for Three Fields by Date
                      philmodjunk

                      Then you do not have any records where there is more than one value selected in the field? Using our example, none store "Apple" and "Carrot" in the same field?

                      • 8. Re: Summary or Calculation for Three Fields by Date
                        VanessaAnne

                        No, in each record only one value is allowed.  I have multiple records each day, this field's values can repeat based on criteria in other categories. So that's how I can have 3 apples, 3 rocks, 3 carrots.  All in one day.  But in multiple records, not the same record. 

                        • 9. Re: Summary or Calculation for Three Fields by Date
                          philmodjunk

                          OK, that opens the door to a simpler option I had originally ruled out as we don't have to worry about counting the same record twice.

                          Create a list view layout based on your table.

                          Add a sub summary part to this layout. Specfiy your popup menu field as the "when sorted by" field in the set up for this layout part.

                          Put the "count of" summary field that I described earlier in this sub summary part.

                          Delete the body layout part.

                          Enter browse mode and sort your records by the pop up menu field and you'll see your records grouped by the value in this field with a count for each existing value.

                          Then, to get just the counts you want for a specfic date, perform a find for only those values in the pop up menu field and for a specified date in your date field. Then sort by the pop up menu field. The layout will be blank whenever the records are not sorted by a sort order that includes this field.

                          • 10. Re: Summary or Calculation for Three Fields by Date
                            VanessaAnne

                            Thanks very much for all your help. 

                            • 11. Re: Summary or Calculation for Three Fields by Date
                              philmodjunk

                              Forgot a key "older version" specific detail. In FileMaker 7, 8, and 9, the reprot will only be visible when you preview or print this report. FileMaker 10, 11 and (presumably) 12 can display this report when in Browse mode.