13 Replies Latest reply on Nov 25, 2013 2:25 PM by flybynight

    Dashboard showing multiple portals, sorted by parent table

    flybynight

      I'll try to describe this as succinctly as possible… wish me luck! Sorry in advance for the long, rambling post, but hopefully you won't have to ask for relevant details that I left out!

       

      Using FMPA 12 on a Mac. Solution is hosted on FMS 12 on a Mac. 6-8 FMP 12 clients on Windows, plus 1-2 FMP on Macs.

       

      I have 2 tables relevant to this issue: JobTickets which has a child table of Jobs.

       

      JobTickets has the following fields that will be relevant:

      DueDate

      Status, which has a value list of 12 items to choose from, as JobTickets move through the shop.

       

      Jobs has the following fields that will be relevant:

      Press, which has a value list of 7 items.

       

      I created a Dashboard table with calculated, result is text, global storage fields for every Press value and every Status value. For instance zg_Status_Invoiced.

       

      OK, that should be enough exposition. Here is what I want to do:

       

      I want to creat a "Dashboard" (for lack of a better term) with 7 portals on it, one for each Press. The items in that portal need to be limited to Jobs that are current (basically JobTickets::Status ≠ Done, Invoiced or Void - any other values are considered "current" - this rule may change, but for now this will get me int he right direction.), and of course have that value of Press chosen, plus I want to sort it by DueDate, ascending.

      I want the boss and/or production manger to be able to leave this screen up, and have it always be up to date, without have to do something to refresh the window. So, as people add jobs to the system, they will show up, and as jobs get marked Stats=Done, they will drop off of the dashboard view.

      At some point, I could see a variant of this going up on a big screen in the shop to replace the whiteboard that is currently used. That may be a pipe dream of mine, but why not, right?

       

      I've tried a couple of different methods, but the main hang-up seems to be getting it to display just current Jobs. One way, I show every job ever on that press (and not sorted how I want), and the other ways I'm showing nothing in the portal.

       

      Here is the way I'm thinking it should work. Hopefully someone can tell me where I am going wrong.

      In my relationship graph, I have a TOG that starts with Dashboard. The first relationship is to Dashboard_JOBTICKETS__Current, with the following criteria:

      zg_Status_Void ≠ Status

      zg_Status_Done ≠ Status

      zg_Status_Invoiced ≠ Status

      I think this is my main problem, causing nothing to show up. For giggles, I have the right side of this relationship set to Sort by my DueDate field, ascending. The sort here has made no difference.

      The next hop on my relationship TOG is to Dashboard_JobTickets__Current_JOBS where I am relating __pk_JobTicketID to _fk_JobTicketID.

      Now, I would expect to be able to make a portal on my Dashboard layout pointing to that last TO and use the portal filtering to narrow it down to only show values from 1 Press. Repeat for each Press portal.

      Trying this way, I get empty portals.

       

      In testing, I also went at it a different way… with closer results, but still missing a key component.

      This way, my TOs went something like this:

      Dashboard |----< Dashboard_JOBS__[PressName] >---- Dashboard_Jobs__[PressName]_JOBTICKETS

      The firs relationship is set up as zg_Press_[PressName] = Press and the second relationship is just _fk_JobTicketID = __pk_JobTicketID.

      With this method, I am able to display all the jobs for that press AND I am able to filter it down to current jobs with the following portal filter:

      Dashboard_Jobs__[PressName]_JOBTICKETS::Status ≠ "Invoiced" and

      Dashboard_Jobs__[PressName]_JOBTICKETS::Status ≠ "Done" and

      Dashboard_Jobs__[PressName]_JOBTICKETS::Status ≠ "Void"

      So, we are closer… but I am not able to get it to sort the portal by DueDate, because that field is in the JobTickets table, not in the Jobs table.

      And, if this way does work, it is a lot more TOs than the first way: 2 for each Press (14 total, plus the Dashboard TO), instead of the 1 for JobTickets to narrow the scope down to current JobTickets, then 1 for each Press (8 total, plus the Dashboard TO).

       

      Hoping it's another one of those stupid little details that I missed.

       

      Or… there's probably another method that even easier, that I'm overlooking? I am not well versed in ExecuteSQL or VirtualLists but maybe that is the answer here?

       

      Thanks for any guidance!

      -Shawn

        • 1. Re: Dashboard showing multiple portals, sorted by parent table
          Stephen Huston

          Check out the built-in Help file regarding Portal Filtering. You can have a portal which shows all jobs, and filters it by only those with Status = "Current", or whatever the status actually is that you want to see. Only those will show up. The only relationship you will need is a Cartesian (K) join using the "X" function instead of a series of "≠" joins. The portal filter takes care of the rest.

           

          You will probably want ot sort them with the oldest Due timestamp at the top, but that sorting can also be done at the portal level rather than in the relationship. You can sort portals on fields in related tables, and you can even filter portals on data, iuncluding dates, in related tables.

          • 2. Re: Dashboard showing multiple portals, sorted by parent table
            flybynight

            I will check that out tomorrow… have to run now. I haven't used Cartesian join much, so I'll do some research on that.

             

            Question about the sorting… are you using the Portal Setup dialog to sort the portal? Is there any other way? When I try that, the Specify… Sort Records dialog only gives me the option for fields in the current table (in my case, Jobs). I don't have the normal drop-down to select a different TO. So, how are you sorting on fields in related tables?

             

            And I do want the Jobs sorted by DueDate, ascending, so that jobs that are due soonest (or all-to-often, that are past their due date) are at the top of the list.

             

            Thanks!

            -Shawn

            • 3. Re: Dashboard showing multiple portals, sorted by parent table
              Stephen Huston

              Sorry about that. I am so used to the standard sort dialog that I forgot the TO options are not in the Portal Sort setup.

               

              If your table isn't particularly wide, you could add a calculation field with a date result which references the due date field in the related table, thus creating an "alias" of that field in the portal's table. It would be unstored in the local table, but should be indexed in the source table, so I don't think it will hit performance too badly, though this is certainly not optimal or best data-normalization practice.

               

              FileMaker work-arounds are what let us do a lot of things....

              • 4. Re: Dashboard showing multiple portals, sorted by parent table
                flybynight

                I did consider that option (adding a calc field that mirrors the date from the JobTickets table to the Jobs table)… but over the last couple of years, I've been transitioning from someone who has dabbled with FileMaker for 18 years, mostly self-taught… to trying to learn the "right" way to do things. So, I've been trying to avoid the normalization no-nos, as a matter of self-discipline. But, I guess the rules are made to be broken, right? 

                 

                I'll do more playing around today. Thanks!

                -Shawn

                • 5. Re: Dashboard showing multiple portals, sorted by parent table
                  erolst

                  You can temporarily select a different TO for the portal, select the desired fields from the other TO's table and then reset the portal to the original/intended TO. The selected fields remain and will be used for sorting.

                   

                  Be careful that your selection makes sense; duplicating a portal, redirecting it and forgetting to delete or re-define the old sort fields can – under the “right” circumstances - be a pretty effective way to grind your DB to a standstill …

                  flybynight wrote:

                  self-disepline

                  That's a new one … 

                  • 6. Re: Dashboard showing multiple portals, sorted by parent table
                    flybynight

                    erolst,

                    I had noticed that behavior… but since there are other elements that aren't totally working yet, I hadn't really been able to test the theory to see if it actually worked.

                    Thanks for catching my type-o. Fixed in my above post.    Or did you just mean that self-discipline itself is a new one?

                     

                    -Shawn

                    • 7. Re: Dashboard showing multiple portals, sorted by parent table
                      Stephen Huston

                      Neat trick on the sorting, and it avoids breaking the normalization rule. Thanks for that.

                      • 8. Re: Dashboard showing multiple portals, sorted by parent table
                        flybynight

                        OK, so an update…

                         

                        I have 2 methods that I have been trying out. Always more than 1 way to do things… and I like to experiment and learn, so I have options in the future.

                         

                        Here is a snapshot of the TOG that I'm working with:

                        SS 2013-11-22.png

                         

                        I have portals on my layout testing both methods.

                        The bottom "line" on the TOG was working, with erolst's sorting trick… but then I quit and re-launched FMPA (things were getting really bogged down, even in a smaller, un-related solution)… and now those portals show nothing. WTH?

                         

                        I'm still trying to figure out why the top one isn't working right. I changed it to a Cartesian X. If I have filtering for the Dashboard_JobTickets_JOBS::Press field, it works, but then I'm showing ALL jobs in the database for that press, not just current jobs. As soon as I try to also filter by the status field in Dashboard_JOBTICKETS, everything goes away. As a test, I put the field Dashboard_JOBTICKETS::Status in one of the portals showing Dashboard_JobTickets_JOBS. Interestingly enough, they all show up with a value of "Invoiced" - even for the ones that are in various stages that we would consider stages of "current."

                        So it kind of makes sense, that "Invoiced" is one of the Status states that we are trying to filter out, and if it thinks they are all Invoiced, then none of them show up. But why would it be showing up that way?

                         

                        I feel like I'm more confused today than I was yesterday. Hopefully someone can show me what I'm doing wrong.

                         

                        Thanks,

                        -Shawn

                        • 9. Re: Dashboard showing multiple portals, sorted by parent table
                          erolst

                          If I read this correctly (after some false starts), you could solve this by putting the press filters into JobTickets, instead of into dashboard.


                          So you have the following (relevant) fields:

                           

                          Dashboard: gStatus

                          JobTickets: gPress1, gPress2 etc.

                          Now create one TO Dashboard_JobTickets by gStatus, and spawning from it seven TOs from Jobs, each using JobTickets::jobID_fk and one of the press names.

                           

                          Now a portal on Dashboard going through JobTickets (by status) into one of those new TOs should show you all current jobs of the respective press.

                           

                          When implementing complex relational filters, you'll often find that you need to put some filters further “down the line”, or have to duplicate and synchronize them The good thing of course is that with their being globals, you can display them all in unified UI, regardless of their actual native table.

                          • 10. Re: Dashboard showing multiple portals, sorted by parent table
                            flybynight

                            OK, I think I'm getting closer…

                            Since I had several possible "Status" criteria that could be considered "Current," for our business rules, I added a calc to JobTickets that will return 1 if JobTicket::Status is anything up to "Press" in our chain of Status possibilities. That simplified the relationship from Dashboard to JobTickets. I just have a global number calc in Dashboard called zg_True = 1.

                            SS 2013-11-25.png

                            I then have the second hop to Jobs linking my pk to fk. I tried 2 methods: one just linking those keys, then using the portal filter to narrow it down to the specific press; and one using the relationship to narrow it down to the press, like we had talked about - so I would eventually have 7 TOs off of the Dashboard_JOBTICKETS__2Press TO.

                            The good news is that they produce identical results, and it is narrowed down as I would expect! So, yay: progress!

                             

                            Any performance difference to using the relationship vs portal filter? Once we are down to current/up-to-press jobs, we are down to only a few hundred records, so there shouldn't be a huge hit either way.

                            When I was experimenting with the Cartesian from Dashboard to JobTickets, then I was noticing quite a wait for it to draw. I could get the Command-period icon for a cursor for up to a couple of minutes. That would make sense if it first had to grab all of the records (at present about 23,000 in the JobTickets table and 75,000 in the Jobs table - and this will only grow).

                             

                            Anyway, performance tuning aside, I am still having a fairly major issue:

                            My portal is from the TO on the right, and any of those fields draw just fine. However, if I add fields to the portal from the middle TO (Dashboard_JOBTICKETS__2Press), every line in the portal shows the same content for those fields. For instance, for Status, they all show one value when there should be several different ones, and DueDate all shows the same date.

                             

                            Because of this, I haven't even tried getting the sort by DueDate to work.

                            Do related fields in portals all have to "downstream" in the graph, rather than "upstream" like I'm trying to do? I thought that related was related. So, do I need to create another TO of JobTickets to the right of my TO that I'm using for the portal?

                             

                            If it works, that is fine… fairly easy fix.

                            This process has just been frustrating because I feel like I've tried 27 variations, most of which seem like they should have worked. Just seems like there is something that is escaping my comprehension.

                             

                            Thanks for your help, again!

                            -Shawn

                            • 11. Re: Dashboard showing multiple portals, sorted by parent table
                              erolst

                              flybynight wrote:

                              Because of this, I haven't even tried getting the sort by DueDate to work.

                              Do related fields in portals all have to "downstream" in the graph, rather than "upstream" like I'm trying to do? I thought that related was related. So, do I need to create another TO of JobTickets to the right of my TO that I'm using for the portal?

                               

                              Yes, they do; you can't “look back” (well, one shouldn't do that anyway …). As you've mentioned, the fix is easy: add a TO downstream. Tha said, when I find myself adding certain TOs over and over to other TOs just to provide a data lookup, I often create a calculation field to hold the desired value (using a structural relationship or ESQL), so I can refer it from other contexts. It comes down to either cluttering your field list or your RG …

                               

                              Here is something I played around with during the weekend, maybe it helps you.

                               

                              One thing I noted with my system, and I guess any system that uses process stages: if you filter all JobTickets that denote a “current” stage, you will find all Jobs that are and WERE current, not only those which are “currently current”. Know what I mean …? I guess a JobTicket would have to know if it is the most current for its job, and this would have to be an additional predicate for the relationship.

                              • 12. Re: Dashboard showing multiple portals, sorted by parent table
                                beverly

                                Rather than additional relationships on the graph, would filtered portals fit the need?  You can use one relationship and have different portals ( same relationship ) sorted differently and filtered differently on the same layout.

                                 

                                -- sent from my iPhone4 --

                                Beverly Voth

                                --

                                • 13. Re: Dashboard showing multiple portals, sorted by parent table
                                  flybynight

                                  Thanks. Yes, that did the trick… adding a TO downstream.

                                   

                                  In the end, my RG isn't very cluttered at all. I'm using the relationship to limit to current jobs, then the portal to filter down the press. So, I've only got 5 TOs for this dashboard:

                                  Dashboard - JobTickets - Jobs - JobTickets - Contacts

                                  (The last one to add a customer field to the portal).

                                   

                                  Sorry that I didn't mark my OP as a question, so I could assign you and Stephen some points.

                                   

                                  Side note that doesn't really matter - in your sample file, I'm not seeing anything show up in the Dashboard layout. I didn't spend much time trying to figure it out (enough problems of my own!), but I did see that you had Jobs and JobTickets flopped. In my file, Jobs are a child to JobTickets.

                                  And my Status field is just a text field that is either chosen with a pop-up menu or radio buttons, depending on the layout. We have a log field that auto-adds and entry with user and timestamp whenever the status changes. There is no Status table, just a static value list. The Status field is on the parent JobTicket table, so each child Job entry just has the status of it's parent JobTicket. Doing this, the filter does not have any issues with jobs that "were current."

                                   

                                  Beverly - see above posts. I had tried a Cartesian join to get all records, then a filtered portal, but something wasn't working, and the performance was horrible. I'm using a combination now, with the first step in the relationship doing most of the "filtering" then the portal just filtering the last step. This seems to provide a balance of good performance and manageable RG and fields.

                                   

                                  At some point, I might play around with ESQL. I think you could just make all of your needed fields on the Dashboard table itself and keep all of the mess self-contained. But, this is working and I think I'll pursue it this way for now.

                                   

                                  Thanks again!

                                  -Shawn