1 2 Previous Next 17 Replies Latest reply on Mar 11, 2014 2:33 PM by philmodjunk

    Reporting by multiple dates



      Reporting by multiple dates


           Hi All,

           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.

        • 1. Re: Reporting by multiple dates

               To find all records where a field is empty, enter find mode, put a lone = in that field and perform the find. This can be scripted.

               If you want to list both groups of records on the same page/screen, either sort your records by the two date fields or use two filtered portals where each filter for a specific date field to be empty.

          • 2. Re: Reporting by multiple dates

                 Thanks for replying PhilModJunk,

                 Yes, I figured that if I could do this manually, then it could be scripted.  I had it wrong, so = means empty or null.  So…

                 Do a FIND for all the DateDue’s that are null.

                 Then from the result of this FIND, how do I say in script language:  For each Tool#, show me only the highest/latest date or alternatively highest ID# and then put them in ascending order.  The sort order I understand, but how do I accomplish the first action?

                 ID            Tool#                      DateIn

                 1              5                              5/13/13

                 2              5                              7/05/13

                 3              5                              12/12/13

                 4              5                              01/11/14               

            5              5                              02/05/14   <<<<<< Out of the five occurrences show me this row only.

            • 3. Re: Reporting by multiple dates

                   I thought there might be more to this.

                   I'd set up a Sub Summary layout part (when sorted by Tool#) in place of the body. Sort the records by Tool# to group them and get one row of data for every tool in the found set. A summary field that returns the Maximum of DateIn will then show the most recent DateIn value.

              • 4. Re: Reporting by multiple dates

                     lol...it's never as simple as it sounds.

                • 5. Re: Reporting by multiple dates

                       Ok, sad to say, but it's taken me hours to get this far.  I finally figured out your instructions above.  Now I'm lost...


                  • 6. Re: Reporting by multiple dates

                         Here's the obvious result.


                    • 7. Re: Reporting by multiple dates

                           I'm trying to do this in stages.  Get one side to work the way I want it to and then work on the other side.  Not to confuse anyone looking at this, but I've left the DateOut and DateDue headings in just for my own reference for the time being.  They will go away later.  This sub-summary works fine, however I don't really want to see 4, 5 and 6 or the big gap between 3 and 7 in the DateInMax column.

                      • 8. Re: Reporting by multiple dates

                             Here 's the actual layout the previous two screenshots are based on.  I should have uploaded this first...my brain is fried..


                        • 9. Re: Reporting by multiple dates

                               Things are starting to click.  I had a little more success after putting it down for a while.


                          • 10. Re: Reporting by multiple dates

                                 This is getting closer to what I want to see, but not quite there.  On the left side there is a gap in the data.  I really only want to see the date values for tools 1,2,3 and 7.  Tools 4,5 and 6 belong to the other side of the table.  Also, I have an outlier on right side, tool #2 falls under the DateOutMax summery field because it's a lone wolf, it's never been checked out more than once, hence it's maximum DateOut is itself.

                            • 11. Re: Reporting by multiple dates

                                   The two column format with different criteria for what appears in each format is going to frustrate this approach. The typical answer for eliminating the gaps is to omit those records from your found set, but since the gap is only in one column and not the other, that won't work.

                                   I suggest a layout where you do not use this layout design but rather arrange your records into two groups, tools out and tools in, one above the other to avoid the "gaps".

                                   Either that, or we set up an entirely different approach using one row portals to show the data for each tool listed from a table where you have one record for each tool. Those one row portals can be sorted to show most recent dates.

                              • 12. Re: Reporting by multiple dates

                                     Okay.  I guess I don't have to have the two groups side by side.  They can be on separate pages or layouts .  I just can't have gaps, it's too confusing for people.  What did you mean when you suggested putting the groups above each other?

                                • 13. Re: Reporting by multiple dates

                                       What I meant was to sort your records so that the tools that were ready for use are in one group and the tools that are rented out are in another, with a sub summary layout part used to label the groups of records.

                                       But I took another look at your data and am confused by your sample data. You show Tool #1 as both Ready for Use and Rented Out. If this is only a single tool and not a group of tools, then it can only be one or the other, not both.

                                  • 14. Re: Reporting by multiple dates

                                         Ah yes, I had a feeling you weren't quite getting what I'm trying to do.  Think of each tool like a library book.  It can only be checked out one at a time.  But the library keeps a record of everybody who's had that book.  I need to keep a record or history of each tool, who had it, and for how long.  So in my sample table above this is why you see Tool#1 listed three times, the third occurance shows it as still rented out, with a due date of 02/17/14.  I hope this is more clear.

                                    1 2 Previous Next