1 Reply Latest reply on Mar 25, 2016 12:40 PM by DavidJondreau

    Help to get a Case function to work with filtered portal data


      Good afternoon ALL!

      We have a transportation company that delivers trailers to customers, some are dropped at their location for later pick up and some are not.

      I have a field on Table1 (Which is the main viewing layout) Labeled "Record Status".  I currently have a CASE function to update as the entry of data progresses and changes the status to show the real-time status for the record and all the status changes work perfectly, with this one exception.  Each main record in Table1, can contain 1 or many separate records in Table2 before load is considered complete.

      Table1 = Main data viewing layout (Contains two differently filtered portals showing data from Table2)

      Table2 = Individual Moves to complete the load.

          Example: Record1/Table2 - Date / Driver# / Pick up Trailer/ Arr / Dep

                        Record2/Table2 - Date / Driver# / DROP Trailer / Arr / Dep

                        Record3/Table2 - Date / Driver# / Pick up Trailer / Arr / Dep

                        Record4/Table2 - Date / Driver# / DROP Trailer / Arr / Dep


      On main layout for Table1, I have displayed two portals:

      Completed: Filtered to show rows only when ALL required fields in related record in Table2 are filled in.

      In Process: Filtered to show rows with any missing data in related record in Table2, and status changes each time another piece of data is entered.

      Sort Order: Both portals are sorted by date/timestamp on Arr / Dep fields, which by default keeps the moves in order of occurrence throughout the dispatching process.


      Here is what I am trying to achieve, and cannot get the function to behave correctly and produce the correct STATUS change when Trailers are dropped.


      When a truck is moving with a trailer, that record status is shown as "On Street Moving"IF a truck drops the trailer at the customer, I need the status to change to "On Street - Dropped".  IF a truck does NOT drop the trailer, the status field would remain unchanged as "On Street Moving" I need these status changes to only take effect based on the the LAST line in the Completed portal.


      Once the truck picks up the trailer that was dropped, the new active line would change the status to "On Street Moving".  Once the trailer is returned to its final location, the line move will be identified as "DROP", however, this is the last of the moves required to close out the main record, and then the status would proceed to the next IF statement listed in the Case function, and status would become "Load Closed"


      How would be the best route to take to include this function within the current CASE function that already has working components?  I truly want this to be a solution through CASE, rather than a script step activated on field movement triggers.


      Any ideas on how best to make this happen?

      Thank you ALL in advance for your help - I have yet to have received an answer that doesn't fit my need perfectly!

        • 1. Re: Help to get a Case function to work with filtered portal data

          I can think of a couple ways, but they're kind of kludgy.


          Does the Completed portal filter rely on any unstored data in the Moves table? If so, you take the logic you have in the portal filter for "Completed" and put it into a relationship. Then you can sort the relationship and use FM's Last() function.


          If not, then you could created a new portal with the same filter criteria, but a) make it sort reverse than the regular one and b) only one row. You can then name the Moves field in the Inspector and pull its content use getLayoutObjectAttribute() to use int your calc.