11 Replies Latest reply on Jun 26, 2015 10:26 AM by MauriceG

    Counting number of records with specific year in a date field

    MauriceG

      Title

      Counting number of records with specific year in a date field

      Post

      Hi everyone,

      I have a database that I use to manage my books. In one table, I have a Date field where I record the purchase date of the book. In a different layout, I have created 21 fields, one for each of 1995 to 2015 inclusive. How can I obtain in each of these fields the number of books (i.e. the number of records) in which the Date field contains the year in question. For instance, if I purchased 12 books in 2015, the field 2015 in my layout should show 12, and of course that number should update each time I record a new book with a purchase date in 2015. Same for the other years.

      Thanks.

        • 1. Re: Counting number of records with specific year in a date field
          philmodjunk

          I have created 21 fields, one for each of 1995 to 2015 inclusive.

          This is not the optimum layout design. It would be better to have 21 related records. For one detail, it allows you to add a record for 2016 without having to make design changes to a table in your database as this becomes simple data entry.

          1) Using your current design, each of the 21 fields could be calculation fields that use ExecuteSQL to query your data and return a count. 21 (or more) related records with a single calculation field using ExecugeSQL could also return the same counts though the query would be slightly different.

          2) If you add a calculation field to your books table with a calculation such as Year ( Books::DatePurchased ). You can use that field in a relationship that matches by year. Then 21 calculation fields using the count function can count records from 21 different occurrences of the books table can show your counts. Or a single occurrence of the books table can be linked to a new table with one records for each year that in turn links to your layout and you can use a single Count function calculation in this new 21 record table to count records.

          3) Define a "count of" summary field that counts a never empty field in your books table. Create 21 one row portals on your layout and  put the same summary field in each. Define a portal filter expression such as Year ( Books::DatePurchased) = 2015 and the summary field will show the count for the books that show in the portal. Use the X operator instead of = in the relationship.

          • 2. Re: Counting number of records with specific year in a date field
            SteveMartino

            Did you try a simple report?  Calculation field with a calc for your date field Year(dateField).  Sort by this field and any field you want in the body.  Put a summary field in the sub-summary part-count of calc field.  Should look something like this.

            Edit:  I'm using FM12, FM14 has some newer features that would make it even easier.

            • 3. Re: Counting number of records with specific year in a date field
              MauriceG

              Thanks PhilModJunk,

              My knowledge of FM is not great to say the least. You say the optimum approach would be to have 21 related records. I have no idea how to implement that. Would you have the patience to explain step by step how to proceed. If not, that's fine, I'll understand.

              Thanks again.

              • 4. Re: Counting number of records with specific year in a date field
                philmodjunk

                Steve's Summary report recommendation is even simpler.

                But to respond to your request:

                I will call the table where you originally defined 21 fields, Dashboard and your table of bookpurchases, Books. We'll add a new table named PurchaseYears. In this table, you can define a number field named Year. If you do not already have one, define a calculation field with a number result type named cYearPurchased. It's calculation is simply: Year ( PurchaseDateFieldHere ).

                Your relationships:

                Dashboard-----<PurchaseYears------<Books

                Dashboard::anyField X PurchaseYears
                PurchaseYears::Year = Books::cYearPurchased

                (In Manage | Database, you can double click a relationship line to get a dialog where you can change the = operator to the Cartesian join operator (x).)

                Create your 21 records in PurchaseYears and enter a different year into the Year field of each record. Now you can define a field in PurchaseYears named "cBookCount" defined as Count ( Books::cYear ). And you can then put a portal on the Dashboard layout that lists each record in PurchaseYears with cBookCount included in the portal row to show the count of books purchased in that year. When 2016 arrives, you just add another record to PurchaseYears and enter 2016 into the year field.

                And a "horizontal portal" method can be used to arrange these totals in a row though this does not handle the addition of yearly totals as flexibly as a single portal with a scroll bar.

                 

                • 5. Re: Counting number of records with specific year in a date field
                  MauriceG

                  Thanks a million times PhilModJunk,

                  I implemented your proposals to the letter and it works great (even though I don't really understand how it workssmiley.) Now here is a little twist: the portal now shows the 21 years from 1995 to 2015 and the number of books purchased in each of these years. But my library contains a few hundred books that were purchased before 1995, i.e. from approximately 1976 to 1994. The problem is I don't have the exact year of purchase of these books and so the PurchaseDate field is empty. Is there a way to show in the portal, in a line just before the 1995 line, something like Before 1995 - 350?

                  And how do I add a Total field at the bottom of the portal?

                  Thanks again.

                  • 6. Re: Counting number of records with specific year in a date field
                    philmodjunk

                    Relationship match fields cannot be empty. You can go to your books table layout, enter find mode and put an = into the date field to find all records where the field is empty. You can then use Replace Field Contents to put the same date into the date purchased field of all the records such as: 1/1/1-- a date deliberately impossible so it's clear that it's an artificial date. In your PurchaseYears table, you can add a record with the number 1 in the year field. You can use conditional formatting or "hide object when" to show something other than 1 in this year row (such as layout text: "< 1995" that is only visible when the year is 1).

                    There are other ways to do this, but this seems the easiest for someone of your experience level to set up.

                    To show a total at the bottom, add a calculation field defined in Dashboard with this expression: Count ( Books::cYear ).

                    • 7. Re: Counting number of records with specific year in a date field
                      MauriceG

                      Thanks again,

                      No problem with changing the date in the PurchaseDate field of all empty records. I can't seem to be able however to change the text in the Year field. I looked carefully at the conditional formatting but don't see how it would permit me to do that. What am I missing?

                      • 8. Re: Counting number of records with specific year in a date field
                        philmodjunk

                        Conditional formatting would not be set up on the field to make this text appear. You'd use the text tool to add text to your layout positioned on top of the field. You'd set up conditional formatting on that text object to make it appear and disappear. The only conditional formatting that you'd apply to the year field is one to make the data in it disappear when the value is 1, such as turning the text color the same as the field's fill color or changing it's font size to a very large size.

                        • 9. Re: Counting number of records with specific year in a date field
                          MauriceG

                          Tks for your patience. I'm almost there. I've applied the conditional formatting to the year field and now it's empty when the year is before 1995. I've used the tool text to create the text I want in the field in the Before 1995 line, which is simply "Before 1995". But how do I make that text object appear only in the 1995 line using the conditional formatting? I've tried the calculation If ( PurchasesYears::Year  > 1994; "" ), but that does not do it.

                          • 10. Re: Counting number of records with specific year in a date field
                            philmodjunk

                            Use

                            PurchasesYears::year > 1994

                            Then go to "more formatting" and specify the very large font size--typically a size of 120+ points for a single line of text in the 10-12 point size range (when not resized by the conditional format).

                            You could also use:

                            PurchasesYears::year > 1

                            Make sure that the year field is of type number.

                            • 11. Re: Counting number of records with specific year in a date field
                              MauriceG

                              Tks very much PhilModJunk,

                              Everything works fine now. Ignorants like me are fortunate that passionate people like you are willing to spend time and share their knowledge with them.