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:
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!