1 2 Previous Next 19 Replies Latest reply on Feb 18, 2017 8:45 PM by BruceRobertson

    Daily Planner


      Hello all.  I'm creating a database for an event company.  I have a table for events wherein each event is a single record containing a serial number field (number), a name field (text) and 3 date fields -- one field for the setup date called "InDate", one for the date the event takes place called "EventDate" and one for the date the equipment is loaded out called "OutDate".  I'm trying to create a sort of daily planner layout that shows the current date, events happening on the current date, as well as any events that may be setting up or loading out on that day. I'd also like to be able to click forward on the date field to show the same for upcoming dates.  I initially assumed that this type of report could be created using subsummary parts, but this doesn't yield the result I'm looking for.  It's like I need a layout with 3 different parts, each filtering the records according to a different criterion.  It seems like I could achieve this through the use of filtered portals, but this requires the portals to be created on a layout of a related table, and I simply don't have any other tables that I need to relate to the events table.  I thought about creating a related table called "Days" that would have fields for InDate, EventDate and OutDate, but it seems a little absurd.  It's entirely possible that I'm missing something basic or obvious, so I thought I would turn to the community for help.  Apologies if this is unclear.  Wondering if anyone out there has any insight or advice.  Happy to clarify any points of confusion.  Thank you.

        • 1. Re: Daily Planner

          I reckon you will need multiple portals. Not filtered for this purpose, but created by a self join relationship.


          Try creating a global field for the current date either in the event table or a dedicated global table. In the relationship graph, add two additional events TO's . Call one EventDate and match it to the events by joining the event date to gCurrentDate (global field) in the parent Event TO. Call the other TO Out date  and connect it by the OutDate back to the same gCurrentDate field,


          You can build you portals off that setup. For switching dates you can just start by setting the gCurrentDate by changing it to a mini calendar type on the layout. you could do something fancier with a button bar showing count of loads and events by date etc dow the track.


          clear as mud?

          • 2. Re: Daily Planner

            Mostly.  Thank you for your reply.  There are a couple of points I'm not clear on --


            -- When you say to connect the tables, wouldn't this relationship require the creation of a foreign key field in the related table occurrences? I don't quite get how to relate the TOs.


            -- I have experimented with a global date field in the past but I found that in order for the current date to always be current, it needs to be in an unstored field, and a global field cannot be unstored.  Could I just use the getCurrentDate function somehow?

            • 3. Re: Daily Planner

              Hi Scott


              A rough demo file is attached. So basically you relate the event date to the current date to get a portal of those and do the same for the date out Table occurrence for the date out.


              Yes you can can get(currentdate) as I have in the calculated field . You can use is in conjunction with the set field script on startup or a layout level script trigger.


              good luck with it

              • 4. Re: Daily Planner

                Thanks a million for taking the time to create the sample file.  Still trying to wrap my head around the necessity for a table with no fields and no records, but perhaps by the time I've finished the Advanced Manual I will start to comprehend.  Cheers

                • 5. Re: Daily Planner

                  "necessity for a table with no fields and no records"

                  There is none.

                  It serves no purpose.

                  Not sure how much value you should attach to that example file.


                  Additional note: the ID field has been defined as a number field; but with auto-enter get( UUID ).

                  But get( UUID ) returns TEXT and so this field needs to be changed to type TEXT, as shown.

                  field definitions.png

                  • 6. Re: Daily Planner

                    Ok. I have deleted the attachment. What  approach would you advise BruceRobertson ?



                    • 7. Re: Daily Planner

                      Thanks for the pick up. I don't think I normally create them as number , but I'd best be checking...

                      • 8. Re: Daily Planner

                        Sorry that table was meant to be deleted.

                        • 9. Re: Daily Planner

                          >3 date fields -- one field for the setup date called "InDate", one for the date the event takes place called "EventDate" and one for the date the equipment is loaded out called "OutDate"


                          SO you have three dates, if you want to list them then you need 3 records.


                          Create a table for "Reminders" where each date is a separate record.

                          • 10. Re: Daily Planner

                            Here's how I'd do this. See the attached pic.

                            It's a bit of a task to set up, but once it is you have a lot of flexibility in terms of display, manipulation, and editing.

                            Screen Shot 2017-02-15 at 9.43.54 PM.png

                            This shows the duration of an event and the actual event date.

                            Would this work? It is a web viewer that uses the fullCalendar.io library. You can check out an explanation of it in a library on Soliant's blog site.

                            Some people are FileMaker purists, but using a web viewer and using Javascript, you can get some good functionality added to your solution.

                            (By the way, the records you see here on this calendar are from a table inside this solution).

                            1 of 1 people found this helpful
                            • 11. Re: Daily Planner

                              You can set the global to today's date on startup; and then allow other dates to be selected.

                              Attached is a mod to the HammerOz file that illustrates this plus a few other ideas.

                              Screen Shot 2017-02-15 at 9.50.29 PM.png

                              • 12. Re: Daily Planner

                                Though there were some problems with your example, it was - as you stated - rough and it did illustrate self-related portals. So with the ID fix and dropping the empty table, it was useful as a basic lesson.

                                Of course once we get into the business of expanding on the ideas there are a LOT of approaches that can be taken.

                                1 of 1 people found this helpful
                                • 13. Re: Daily Planner

                                  Bruce -- This is awesome, and thank you for the file.  Looking at the relationship graph, I see there are relationships between an occurrence of the Hires table and 2 separate occurrences of the Dates table, one being a cartesian ( x ) relationship.  I'm wondering if it's possible to explain why this is necessary.  I have been staring at the relationship graph for a while now and I'm still not getting it.

                                  • 14. Re: Daily Planner

                                    This looks like a dream come true. I have already been playing around a little with web viewers and some very basic HTML.  Thank you for the heads-up and for the link.  I downloaded the library from Soliant but haven't looked at it yet.  Hopefully the setup isn't beyond the scope of my knowledge at this point.

                                    1 2 Previous Next