12 Replies Latest reply on Apr 3, 2015 7:50 AM by sccardais

    Global Field Relationship- Dashboard Build



      Global Field Relationship- Dashboard Build


      Hi everyone, hope you can help me.  I watched a FMP video the other day talking about building a dashboard.  It was a very helpful tool, except that I must be missing something.  I have 3 tables.  An "interface" table which has one global field which is a date which relates to a "months" table.  The instructions were to create 2 calculation fields (a start and end date) in the "months" table based on the global date field in the "interface" table.  Then I needed to create a field in the "months" table that showed the sum of the "incidents" (my 3rd table which is related to the "months" table by a calculation using the start and end dates and the date of the incident).  My problem is that it seems to work when I first put them on the layout and the start and end dates in the months table work great, but the # of incidents does not "update" when I change the date in the global field from the "interface" table.  The layout is the "interface" table with the global field on it and the "months" table showing the "#of incidents" in the table.  Can anyone advise me on this or tell me what I am doing wrong?  I really appreciate you help! Thanks!

        • 1. Re: Global Field Relationship- Dashboard Build

          The instructions were to create 2 calculation fields (a start and end date) in the "months" table based on the global date field in the "interface" table.

          What do you mean by "based on"? That doesn't really track here and sounds like something that will produce unstored calculation fields in your months field and that won't produce a relationship that works from the context of your interface table.

          A detailed description of your relationships would also be helpful as it saves possible inaccurate guesses on the part of anyone responding to your question.

          • 2. Re: Global Field Relationship- Dashboard Build

            Sorry, "based on" meaning the calculation were dates using that global field, like this:

             Right(Year(Dashboard::g_Dashboard Global Date);2)


            • 3. Re: Global Field Relationship- Dashboard Build

              And that won't work for what you want to do for the reasons that I noted earlier. I suggest describing what you are trying to accomplish with your global date field and then we can take a crack a an approach that will work for what you want.

              • 4. Re: Global Field Relationship- Dashboard Build

                I see, thank you.  When I saw this done, I didn't think it would work, but it did and I really think it would be a great tool for the management team of the juvenile detention/treatment center where I work.  So here is what I was thinking:

                A screen with a date field that would show related records in portals and it would update each time a new date was entered.  The portals could be incidents, restraints, etc.  To start with, here is how I structured it:

                3 tables:   Interface, Months, Incident

                A global date in "interface table", a start and end date in "months table" (which is the calculation I sent in my initial post) and the existing "incidents" table. 

                Relationship (please forgive my ignorance on the proper way to type this)   Interface ----->>Months (Interface::globalOne-Months::globalOne)  and then the other table occurrences would be  Months----->>Incidents (start date < incident date AND end date> incident date)

                If I have a field in the "Months Table" that is the Count(Incidents::Incident ID). 

                The layout could look like this:

                Layout= Interface    Table=Months

                Date (global_interface) 1/1/14 (enter date here)

                and it would show:

                Mo/Yr         # Incidents Current Yr             # Incidents Prev Yr

                1/14                   10                                               11

                2/14                       9                                               7

                3/14                     5                                                 8

                • 5. Re: Global Field Relationship- Dashboard Build

                  So you don't want to see the actual incidents, you want to see a count for each month.

                  A different approach can make this happen quite easily and I suspect that your demonstration did this from a different context than you are expecting.

                  Here's how I would do it:


                  Months::MonthKey = Incidents::cMonthKey

                  cMonthKey is a calculation field, but a STORED one, that does this:

                  IncidentDate - Day ( IncidentDate ) + 1

                  Select Date as the result type. IncidentDate is the field where you record the date for a given record in Incidents. This calculation computes the date for the first day of the month for all dates from the same month so it gives you a common value for all incidents in one month. Months::MonthKey would be a date field with a "first day of the month" date. You can set several different auto-enter calculations to ensure that this is the date found in any given MonthKey field such as: Self - Day ( Self ) + 1 or you might use Get ( CurrentDate ) - Day ( get ( CurrentDate ) +1. Either way, you can create a series of records in Months with one record for each month.

                  A calculation field such as Count ( Incidents::IncidentDate ) defined in Months would give you the total count of incidents for that month and year. To get a count from incidents for the previous year, you can set up a calculation field such as:

                  Date ( Month ( MonthKey ) ; 1 ; Year ( MonthKey ) - 1 ) and use it to match to another occurrence of incidents so that a use of the count function can count the incidents for the preceding year.

                  A portal on a layout based on Inteface can then list all Months records in a portal and show the incident counts in two columns inside that portal.


                  Interface::anyField X Months::anyField
                  Months::MonthKey = Incidents::cMonthKey
                  Months::cMonthKeyLastYear = Incidents|LastYear::cMonthKey

                  For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                  • 6. Re: Global Field Relationship- Dashboard Build

                    Thank you so much, it worked perfectly!

                    • 7. Re: Global Field Relationship- Dashboard Build

                      Thank you for posting this question and thank you, Phil for your detailed answer.

                      One quick question. What happens if you enter a new Incident in a date that hasn't already been entered into MONTHS? For example, when you enter an incident for 7/15/2015, is a new record automatically created in MONTHS::MonthKey (7/1/2015) or do you have to enter this manually in MONTHS?



                      • 8. Re: Global Field Relationship- Dashboard Build

                        Should the relationship between MONTHS and INCIDENTS be Many to Many or should the fields in MONTHS be defined in a way to force FMPA to force a One to Many?

                        This is a screenshot of a test dbf based on your thread.The relationship graph on the right shows a Many to Many relationship between MONTHS::MonthKey = INCIDENTS::c_MonthKey and a similar Many to Many between MONTHS::c_PrevYr= INCIDENTS::c_MonthKey.


                        • 9. Re: Global Field Relationship- Dashboard Build

                          FileMaker will show "crowsfeet" on any field that is neither an auto-entered serial number field nor set up with the "unique values" validation option. It would be a good idea to specify that for MonthKey. You can't specify that validation for c_prevYr unless you changed it to a data field with an auto-enter calc, but there is no real need to do so.

                          You'll need to set something, such as a script that adds new month records for you in the Months table. This could be a server scheduled script that runs once a day, a script that runs when you first open the file or a script that adds the month if it is not present each time that you log a new incident.

                          If you select "allow creation of records via this relationship" for Months in the Months to Incidents relationship, a script that runs from a layout based on Months Incidents with this single script step:

                          Set Field [ Months::MonthKey ; Incidents::c_MonthKey ]

                          Will create the new record if one does not exist and make no change to your data if it does exist.

                          Edit note: Corrected typo due to comments found below....

                          • 10. Re: Global Field Relationship- Dashboard Build

                            Didn't get this to work yet. 

                            I added Validation "Unique Value" to MONTHS::MonthKey. Only during Data Entry and Allow User to override.

                            Specified "Allow creation of records in ...[ MONTHS ] via this relationship.

                            Created Script "Create Month If New" with the following steps.

                            1. Freeze Window
                            3. Got to Layout ["MONTHS"(MONTHS)]
                            5. Set Field [MONTHS::MonthKey;INCIDENTS::c_MonthKey]
                            7. Go to Layout [original layout]

                            Created Script Trigger "On Record Commit" that runs the script above when a record is committed in INCIDENTS. 

                            I realize the Script has more steps than you described but if I wanted to create a new record in MONTHS when a new record in INCIDENTS is created.

                            Created new record in INCIDENTS with new date but this didn't create a new record in MONTHS.

                            What did I do wrong?

                            • 11. Re: Global Field Relationship- Dashboard Build

                              My typo, the morning caffeine has yet to be imbibed here. You would not do this from a layout based on Months, it would be from a layout based on incidents.

                              • 12. Re: Global Field Relationship- Dashboard Build

                                BINGO! That got it. Thank you.