First, a question. You want to display data from the Books table (ie: a book title, author, etc.) and also see a portal that shows a count of all the books read by year? That portal has nothing to do with the book information from the Books table? It is simply a count of all books read by year?
The layout on which the portal is found is only intended to display some statistics. And for the time being, I want it to display only the years and the number of books read by year.
I would create a Years table. Have a simple looping script populate the table with one record for each year you wish to track. You would then create a relationship from Books to Years that shows all of the Years. This is the relationship you see in the portal. You could filter it by year range if you wanted to with minimal effort.
You would then need a relationship from Years to Books based on year. So in your Books table you have a calc field that extracts just the year from your DateRead field. You relate the year in Years to this YearRead field in Books. Then you have a calc field in Years that counts the number of related records.
There a many ways to do this.
First you will need to create a calculation field in Books: Year ( DateRead )
If you want to display all years from 1990 - now you will need to create a table Years where each record is a separate year from 1990 onwards. From this table you create a relationship Years -> Books related by the year field.
To display number of books for each year you can create an unstored calculation field in Years
Count ( Books::bookID ), bookID being the primary key field in Books.
Or you can create a summary field in Books: Count of bookID and put that on the layout.
This will work ok in list view, if you really want a portal ( like a dashboard ) then I suggest creating a utility table with some field and one record, base your layout on this table. Then join it with Years table by Cartesian (X) join.
That will show all records from Years and related Books by Year in your portal.
If you only want Years from books that exist and have been read then the Years table is not needed, but you will still need the Year ( DateRead ) field and you can create a summary field for Count of the Year field.
It kind of depends on what you're after in your solution.
But this is the general idea.
I hope you won't mind helping me a little more because my knowledge of FM is extremely limited.
So, to start with the Years table, what fields does it need, only a Year field and an ID field?
Just saw the answer from electon. Let me consider it and I'll get back to you.
What should be the calculation in the calculation field Books: Year ( DateRead )? Only Books::DateRead?
you can name the field whatever you like, YearRead or something like that.
The calculation is Year ( DateRead ). So you get only the year part of the date.
OK, I've got the Year table and the portal in place, but the portal shows only the year 2016 in the first line. What did I do wrong?
It will really depend on how you set up your layouts and relationships and is not easy to represent with words only.
Can you post your file or a screenshot of the layout and relationship graph?
My file is not in English, so I will translate the relevant parts into English first and post the relevant screenshots. It will take me a bit of time, so I'll post the screenshots on the weekend.
Thanks for your help.
Thank you so much to all of you. I'll review all of this carefully on the weekend and if I'm still going nowhere, I'll get back to you.
This is great. I'm so grateful. Your explanations allowed to make this work finally, after weeks of going in circles. Attached is a screenshot of the result. I have two more questions:
1. in the portal Year Purchased, you'll see that the last line does not display a year. That's because I did not create records for the years prior to 1995. I'd like that last line in the first column of the portal to simply say ''Before 1995''. How do I do that?
2. I'd be extremely grateful if one of you would explain to me why it works, what is the logic that makes it work. It's easy to replicate solutions suggested by others, but I will not have made much progress in my understanding of Filemaker if I don't understand the logic behind the solution.