Reporting by multiple dates
I love this Filemaker program. And the fact that it can be utilized on my iPad is even better. Just recently I converted two databases over from Access. One was fairly straight forward, the other not so much. Thus far I have most of the database working just fine. However, there is something that I’m just not getting. I have a table that has the following fields
Record ID, Tool# (1 through 120), Name, DateOut, DateDue, DateIn, and Notes
The table collects a history of all the people who rented tools #1 through #120 and at the same time shows me which tools are currently in and which are out. Each tool gets overhauled once it is returned, so that they all get an even amount of use/wear on them.
So here’s what it looks like in the portal:
ID Tool# Name DateOut DateDue DateIn Notes
1 1 James Brown 10/12/11 12/24/11
2 1 Sarah Ferguson 01/05/12 04/15/12
3 1 Ted Lucas 11/17/13 02/17/14
4 2 Joanne Sutter 07/05/13 08/13/13
5 2 Alan McFarland 10/23/13 01/23/13
6 3 …… …. Etc. Etc.
And so on...pretty simple stuff. I have a portal set up on a Layout that shows all the occurrences per tool# and a button to advance through each tool rather than just going by each ID.
Here’s where I’m having problems. I need to generate a report each week that shows all the tools that are currently available, as well as all the tools that are out being used. The report needs to be printable as well as just viewable on a layout. In the old Access database, I have this set up with all the “available” tools under a header on the left side of the page according to oldest date. So, as they come in they go to the bottom of the ”available” list. In other words, last in, last out. On the opposite side of the page is a header with a column showing the tools already out being used, according to date.
In Access it’s simple, just generate a query that shows the tools that have a “null” value in their DateDue column and then build a report based on the results of the query. For the tools currently out it would be query all the tools with a “null” value in the DateIn column and build a report based on that result. These two queries assume that a tool that is out will always have a due date and a tool that is in won’t have a due date. Then the two reports are combined in what Access calls a sub-report/subform so that both are viewed side by side on the same page (within the same report). Not sure if this is even possible with FM. However, I’m trying to get the same result in FM12 Pro Adv.
I have watched all the Chris Ippolite videos and Googled and forum searched my butt off and cannot find anything that resembles what I want to accomplish here. I’m betting the answer is so stinking simple that I’ll be embarrassed that I asked, but I just can’t figure it out. How do I do this? Any help would be appreciated.