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.
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.
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.
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 ).
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.
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 works.) 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?
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 ).
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?
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.
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.
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.
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.