1 2 Previous Next 24 Replies Latest reply on Jan 14, 2014 5:34 PM by Stephen Huston

    Dashboard layout shows no records on solution open (FM12)

    flybynight

      I have a Dashboard layout to show current jobs, broken up as a portal for each press.

       

      Here is a shot of the TOG. The layout is based on the left-hand TO "Dashboard" and there are 7 portals, each based on the 3rd column of TOs (ignore the top one, that was a show-all for testing).

      Screen shot 2014-01-02 at 4.12.47 PM.png

      The Dashboard table has 1 record, with mostly global fields. The first relationship from Dashboard is to an auto-enter calc number field that updates to 1 based on the status field choices that I want to show up. The next hop is the actual Jobs I want to show in the portals (Jobs is a child of JobTickets, each JobTicket can have several Jobs). After that (not shown), it goes back up to the JobTicket level, to be able to sort based on the JobTicket DueDate, and also show some info from that table, then it goes up one more level to the Contact table to show the company.

       

      Anyway… this works great… most of the time.

      Over the last couple of weeks, however, I'm noticing that when I first open the solution and navigate to my Schedule layout, then to the Dashboard layout, all of the portals are shown as empty.

       

      After working in the database for a while, I can go back and see the Dashboard portals populated as expected. I have no idea what makes the difference for when it decides to show records. I'm never modifying anything in the Dashboard table. If it just never worked, that would be one thing… but I don't get why it starts out not working, then all of a sudden works.

      I've tried some tinkering, adding cartesian joins to the relationships, and that didn't seem to fix anything. I've tried doing a manual refresh, and that does nothing.

       

      If anyone has any ideas as to why this would be, or what I need to do to kick it into gear, it would be greatly appreciated.

      Thanks!

      -Shawn

        • 1. Re: Dashboard layout shows no records on solution open (FM12)
          steve_ssh

          Hi Shawn,

           

          Would you be willing to elaborate just a little bit regarding the tinkering that you tried?

           

          In particular:

           

          - What is the nature of the "manual refresh" that you tried?  Was this a "refresh window" script step?  If so, was "flush cache" checked as an option?

           

          - When you added cartesian joins, where did you add them with respect to the TOG that you show?  (thanks for the screenshot)

           

          - Were you adding the cartesian joins to nudge the relationships ( per http://www.teamdf.com/weetbicks/ditch-those-flush-caches-use-cartesian-join-instead/42/ ) ?

           

                or:   Were you just adding them to test to see if you could get anything to show in your portals?

           

           

          Given your scenario, the first theory that I would be testing would be that of having stale cached data that you need to nudge (the weetbicks link above would help with this), but perhaps you have already tested this theory.  The intent of my questions above is just to make sure that you have adequately tested this theory before discarding it as non-applicable.

           

          Kind regards,

           

          -steve

           

           

          p.s.  If this *is* a caching issue, my recommendation would be to read and understand the linked weetbicks article above.  It is to your advantage to be able to use the technique described there to refresh only the records in the cache that you know need to be refreshed, as opposed to losing your entire cache in order to refresh some small proper subset of cached records.

          • 2. Re: Dashboard layout shows no records on solution open (FM12)
            flybynight

            Steve,

             

            Thanks for taking the time to help me troubleshoot this!

             

            steve_ssh wrote:

            - What is the nature of the "manual refresh" that you tried?  Was this a "refresh window" script step?  If so, was "flush cache" checked as an option?

            I tried 2 things when I refer to "manual refresh" - one is just going to the Record menu and selecting Refresh Window Shift-Cmd-R, and the other method I tried is creating a 1-step script with Refresh Window [Flush cached join results].

            Both of these had no effect. So, I'm not sure if it is "stale cached data" - especially since I have the issue of nothing showing up when I first open the solution… when there should be NO cached data, stale or otherwise.

             

            steve_ssh wrote:

            - When you added cartesian joins, where did you add them with respect to the TOG that you show?  (thanks for the screenshot)

             

            - Were you adding the cartesian joins to nudge the relationships ( per http://www.teamdf.com/weetbicks/ditch-those-flush-caches-use-cartesian-join-instead/42/ ) ?

             

                  or:   Were you just adding them to test to see if you could get anything to show in your portals?

            I tried adding them between the two relationship joins in my screen shot above… but I think I may not have been doing it right. The intended purpose was to nudge the relationships… but I don't totally understand the cartesian join, so I'm probably just shooting in the dark. Since I didn't see any change, I took out the cartesian joins I added.

             

            Funny enough, my inspiration came from an article that references the article that you linked. Here is the one I was reading: http://filemakermisc.wordpress.com/2012/07/02/efficient-refresh-with-cartesian-join/

            I think the one I was reading left out an important step: creating the field specifically to do the nudge. I was reading it as just needing a cartesian join between anything - like just fact that it was there would force everything in that relationship to refresh. I didn't quite understand it.

            I had the weetbicks article on my reading list, and had skimmed it, but I'll go over it more thoroughly now to try and wrap my head around it!

             

            My other plan for today was to leave a 2nd window open with the Dashboard layout, so I could try to see what event(s) trigger any update.

             

            I'll report back after I've had a chance to read and play. Thank you!

            -Shawn

            • 3. Re: Dashboard layout shows no records on solution open (FM12)
              Stephen Huston

              This sounds like a simple caching issue. When first opening the solution, all of the portal data has to be cached from the server to the client before things will refresh correctly, and that is going to take time, depending on your network. The more records exist to be cached, the longer the initial refresh will take.

               

              Depending on the use of your dashboard, a series of calc results using the SQL results across the relationships may perform much better than portals.

               

              There are also questions about how your dashboard record is refreshed. If the global record is deleted and then created and committed afresh, that would force a refresh as the key is regenerated and commited. That may help refresh things, but it may also just restart the caching process all over again.

              • 4. Re: Dashboard layout shows no records on solution open (FM12)
                flybynight

                Our database is pretty small, at least for "current" jobs that will show up here. At any given time, we have beteen 75-125 current JobTickets, and 150-300 Jobs. Each press portal will show from a handful to maybe 30-50 Jobs.

                 

                I don't think it's an issue of needing time to download/cache from the server. There just isn't that much data. I haven't had time to mess with it today, but I've had it sitting open for several hours, and it still shows nothing.

                I also just tried deleting and re-creating the Dashboard record. Still no change.

                 

                This is just weird because it was working fine for a few weeks. Normally, as users add and update the status of various jobs, they just show up in the portal. It was great, because it didn't need any intervention. The boss or production manager could just leave it up and monitor the list of current jobs.

                When it was working, it was lightning fast. Something just seems "broken" now and I can't figure out why it changed.

                 

                Like I said, I haven't had time to tinker with it today yet.

                Thanks!

                -Shawn

                • 5. Re: Dashboard layout shows no records on solution open (FM12)
                  Stephen Huston

                  Something may have gone wrong with your relationships' setups if this is failing after tinkering with relationship graph items.

                   

                  Check that your field types ono both sides of all relationships in this structure are set to the same data type, either number or text, and that they are stored/indexed fully.

                   

                  If that doesn't do it, can you post the files for examination?

                  • 6. Re: Dashboard layout shows no records on solution open (FM12)
                    flybynight

                    OK, after a weekend in the frozen tundra, I have an update. Kind of. 

                     

                    I have narrowed down part of my issue to the field JobTickets::Status_2Press. This is a field that will show a 1 if the status is anything from WriteUp, Prep, Proof… up to "Press" and then a 0 for anything beyond that like Bindery, Done, Invoiced, etc.

                     

                    I'll be honest, I don't remember how I had that field set up when I made the OP, but I know that at one point, it was a simple calc field, that said Case ( Status = "[desired values]" ; 1 ; 0 ). There were a series of options for each case and Value.

                     

                    At some point, I changed that field to be a Number field, with an auto-enter calc of the same formula. The theory being that for something this simple, an auto-enter field should give me the same results, with less overhead or need to re-evaluate.

                    I somehow turned indexing off (I don't remember doing that, but in my tinkering I must have… no idea why), which created a new problem. I eventually noticed on my relationship graph, that instead of the first step looking like it did in my screen shot above, it looked like this:

                    Screen shot 2014-01-07 at 2.08.26 PM.png

                    Notice the difference in the ends of the join lines. Instead of:

                    [Dashboard]|---[=]---<[Dashboard_JOBTICKETS__2Press]   it was now:

                    [Dashboard]|---[=]----|[Dashboard_JOBTICKETS__2Press]

                    It had a bar on the right side, instead of the chicken foot. I always thought that bar meant it was joining a global field (or I assumed, based on my observations). I went looking for the real answer… and couldn't find it. This page:

                    http://www.filemaker.com/help/html/relational.11.11.html   …lists all the other symbols for the graph (single record match vs multi), but not the bar. If someone can find the official documentation for this, I'd love to educate myself!

                     

                    So, anyway… that was my hint that it might be part of the problem. So tinker away… I tried changing it from a number to text (and then back because it didn't make a difference). Then I stumbled on the fact that I had turned indexing off. After doing that, the join symbol changed back to the chicken foot and BOOM!, my dashboard layout was full of info. Rejoice! But wait…

                     

                    I still have the issue where when I first open the solution, I go to my Schedule layout, which shows all current JobTickets, then click my Dashboard button, taking me to the Dashboard layout… and all the portals are empty. Leaving a second window open to the Dashboard layout and working in another window, one time when I changed the Status field of a JobTicket record, the Dashbaord updated to be full of data in the portals. But, I can't replicate that consistently. I just tried closing it and repeating those steps and it stays empty.

                     

                    I tried adding an OnLayoutEnter script trigger that does:

                    Set Field [Dashboard::zg_True; 1]

                    Commit Records/Requests [No dialog]

                    And that didn't work either.

                    From the Records menu "Refresh Window" still does nothing. Same with a script that does a Refresh Window w/ Flush Joined Cache Results checked.

                     

                    I thought I was there, but now nothing seems to nudge it to working. Need to walk away from it again…

                     

                    Feel free to chime in if you see what I'm doing wrong. Thanks!

                    -Shawn

                    • 7. Re: Dashboard layout shows no records on solution open (FM12)
                      Stephen Huston

                      I see your dashboard field is also unindexed, porbably a global value. Try turning on indexing for the dashboard field as well, make it an indexed auto-enter, and you can still reset it via the script if necessary. No need for that field to be a global if it's part of the dashboard layout's base-table.

                      • 8. Re: Dashboard layout shows no records on solution open (FM12)
                        flybynight

                        I tried changing that to an indexed auto-enter, and that made no difference.

                         

                        I made a duplicate of the file, deleted our customer data and made a test file for you to look at.

                        It's a duplicate from last night's backup, so it doesn't have that last change.

                        Here is the link:

                        https://www.dropbox.com/s/egdeodahe8mspm5/Dashboard%20Test.fmp12.zip

                        User: Admin

                        Pass: temp

                         

                        I just added a few fake customers and fake jobs.

                        The live solution has a lot more data in it, of course. Approx figures:

                        1800 contacts

                        1600 companies

                        26,000 JobTickets

                        74,000 Job items

                        A few hundred in the Paper tables that aren't used on this layout.

                         

                        OK, so to test…

                        From the home screen, go to SCHEDULE, then DASHBOARD from the second tier nav buttons.

                         

                        At first, in this test file… I couldn't get it to NOT work!

                        That was just opening it locally. Then, I threw it on the server… and now it's not working properly.

                         

                        I did the 2 window test - leaving the Dashboard up in another window. When I add a new JobTicket to the system, then the Dashboard portals fill with data.

                         

                        The only problem is that the people who are most likely to want to sue the Dashboard (the owner, production manager, me) do not typically add new JobTickets to the system. The CSRs or Sales team does that. So, I need some other way to trigger that refresh.

                         

                        Let me know if you have any ideas.

                        Thanks!

                        -Shawn

                         

                        PS - not that it's anything so amazing, but I am trusting the TechNet community with my file, representing many hours of dev time. I'll take it down after a day, or once someone points me to a fix. Just an FYI incase anyone wonders why the link doesn't work after tomorrow.

                        • 9. Re: Dashboard layout shows no records on solution open (FM12)
                          flybynight

                          And today, my live file is refreshing info on the Dashboard after I update the Status on any JobTicket. Yesterday it did that once for me, then I couldn't get it to repeat the behavior. Today I just did it several times in a row!

                           

                          Something not working I can deal with… but this inconsistent behavior is driving me batty! 

                           

                          Laters,

                          -Shawn

                          • 10. Re: Dashboard layout shows no records on solution open (FM12)
                            timwhisenant

                            Hi Shawn,

                             

                            What do you get, when this file is on server & the dashboard is up on one computer and the update is made on another? I would expect no difference, but this will introduce your network into the mix. Also, is first entry to dashboard as expected?

                             

                            One other question: How many fields in “JOBTICKETS_2Press”?

                             

                             

                             

                            Just a couple of thoughts,

                             

                            TimW

                            • 11. Re: Dashboard layout shows no records on solution open (FM12)
                              timwhisenant

                              Another thought:

                              Have you tried filtered portals rather that drawing all the graph lines to multiple instances of the same tables?

                               

                              Also, I see your relationships are 4 levels deep to get your data, meaning that all tables need to be cashed to some extent to render the page the first time.

                              To provide summary data like this, accounting systems break the normal form and store / update summary tables as values change.

                              This type of data would be low maintenance here, it would appear. Simply create the record when ticket is assigned to a press and track status and due date modifications as they change. This coupled with filtered portals would greatly reduce the fields needing to be cashed and simplfy the graph, making this very fast screen draw, Also since portals do not prevent updating of fields displayed, changes can be posted without record locking.

                               

                              HTH

                              Tim

                              1 of 1 people found this helpful
                              • 12. Re: Dashboard layout shows no records on solution open (FM12)
                                flybynight

                                Tim,

                                 

                                Did a little testing to answer your questions, but it the same as I would have guessed. When it is hosted on FMS, and one user has the Dashboard open (showing empty portals), and another user on another computer adds a record… the Dashboard shows correctly populated portals for that 2nd user, but the fist user's Dashboard is not updated.

                                 

                                The odd thing is, once it starts working for any 1 user, it updates automagically when any user adds or modifies any records. It's just that first instance to kick it into gear that needs to happen. After that, works as expected.

                                 

                                There are 99 fields in the JobTickets table. 25 of those are globals for different Press and Status options, plus a couple that I'm using for search fields. "JOBTICKETS__2Press" is a TO of that Table, matching the auto-enter calc field referenced above that enters a 1 for any status options that we want to show in the dashboard.

                                 

                                Thanks,

                                -Shawn

                                • 13. Re: Dashboard layout shows no records on solution open (FM12)
                                  flybynight

                                  When I first did this, I did try filtered tables… I don't remember the reason, but there someting that wasn't working right there either. I think someone on this forum talked me out of filtered portals (which made for a much simpler graph) and into doing the filtering with the relationship. I remember there was something that was very slow performance. I think that was using a cartesian join to get everything in the relationship, then using portal filtering to norrow it down.

                                   

                                  I found that old thread it you wanted to read up on that…

                                  https://fmdev.filemaker.com/message/130460#130460

                                   

                                  I re-read all of that too… and it sounded like I had it working then, using filtered portals for the 2nd hop. Now of course, I can't remember why I changed it to seperate TOs. That change happed between that first thread and this one… and it was working for a while. At least I think it was. Maybe it was broken and I didn't notice?

                                  I think I may remember the reason I changed it: at the top of each portal, I have a "count" merge field to tell us how many jobs are current on that press.

                                  Is there a way to get the count of records in a filtered portal?

                                   

                                  This may be one of those cases where I've tried so many different methods, that I missed some combination near the beginning that would have solved it.

                                   

                                  Thank you everyone for your help!

                                  -Shawn

                                  • 14. Re: Dashboard layout shows no records on solution open (FM12)
                                    erolst

                                    flybynight wrote:

                                    Is there a way to get the count of records in a filtered portal?

                                     

                                    To display the count of a filtered portal, create a summary field Count of primaryID in your portal TO's table (if you don't already have such a field) and put in into a one-row portal with the same filter.

                                     

                                    To get this number programmatically, give the field object an object name and use GetLayoutObjectAttribute ( objectName ; "content" ).

                                    1 2 Previous Next