3 Replies Latest reply on Jan 20, 2015 7:58 AM by Mike_Mitchell

    Update auto-enter calc, based on related data?

    flybynight

      Not sure if the title is the best description. Here is what the client is asking…

       

      We have a SalesOrders table, with a child SOitems (line items) table.

      We also have a PackingList table, with a child PLitems (line items) table.

       

      There is a 1 to many relationship between SalesOrders and PackingLists.

      Depending on when individual items are completed, each SOitem may have its QtyOrdered broken across multiple PackingLists, with each item having a QtyShipped. The SOitems ID # is used to enter each line in a PackingList, so we can also relate to each shipped item from the SOitem. So far, so good.

       

      Now, they want to update the SalesOrders::Status field to "Complete" whenever the QtyShipped = or > QtyOrdered, for all line items. The only problem is that the QtyShipped gets entered on the PackingList layout, so the fields in the SaleasOrders layouts don't automagicially update.

      And the Status field can't be a calculation field - they need to be able to manually change the status at any time. They just want something to trigger the update when all items have shipped completely.

       

      Hopefully I'm explaining that clearly. I'm sure there are a number of ways to solve this.

      I have something kind of working now, but it feels clunky and it's bullet proof. I have a OnObjectSave script trigger on the PackingList PLitems portal QtyShipped field that does the following:

      Commit Records

      Set Field [PackingList_SALESORDERS::Status ; Case ( Sum (PackingList_SOITEMS::QtyRemaining)  ≤ 0 ; "Complete" ; "OPEN" ) ]

      Refresh Window [ Flush cached join results ]

       

      It works, but in the off case that they ship more of an item than was ordered, and if the difference is more than the QtyOrdered of the other line items, it will produce a false "Complete" Status.

       

      Thanks in advance.

        • 1. Re: Update auto-enter calc, based on related data?
          Mike_Mitchell

          Shawn -

           

          Your basic process is sound. Auto-enter calculations do not trigger based on value changes in related tables (as you've discovered). So you're restricted to using either a Script Trigger (as you've done), or a batch process that runs on some frequency (which is probably not appropriate here, based on the need for more real-time feedback).

           

          I think the problem isn't your process, but the specifics of your calculation. I don't know enough about the particular database to give you a good answer, but it sounds like you need to account for the case that's coming up wrong by running a check to see if the quantities are appropriate. If not, either throw a warning and force the user to fix it, or put up a status of "Error" or something like that.

           

          Not sure that helps much, but maybe that will point you in a good direction.

           

          Mike

          • 2. Re: Update auto-enter calc, based on related data?
            flybynight

            Thanks, Mike!

            Sounds like this will work for now. The client said that if they ship more than the order, they would change the order qty before they generate the packing list. So, it should not be an issue.

            For another area with similar requirements, I had implemented this technique:

            https://filemakermisc.wordpress.com/2011/06/07/calculations-and-auto-enter-calcs/

            But that was for a updating the status of a single record, not one with multiple line items.

             

            The other complication is that this is a converted (from who knows how old) solution, so while they are using FM13, they still have everything in separate files. Not as easy to hop around.

            Laters,

            -Shawn

            • 3. Re: Update auto-enter calc, based on related data?
              Mike_Mitchell

              "The other complication is that this is a converted (from who knows how old) solution, so while they are using FM13, they still have everything in separate files. Not as easy to hop around."

               

              I feel your pain ...