3 Replies Latest reply on Feb 14, 2011 12:35 PM by philmodjunk

    Calcs Slowing Me Down

    letra

      Title

      Calcs Slowing Me Down

      Post

      I have the following fields attached to each product: In Stock, Needed for Pending Orders, Backordered and Total Sold. They calculate based upon each invoice's status. Upon creating a new invoice, the default status is set to Pending, but the status changes dependent upon whether the order is shipped, backorderd etc.

      In order to account for the changing status of an invoice, I have the product fields mentioned above set to be unstored. The problem comes in when I go to enter an order into an invoice...because the calc fields are unstored, it takes FOREVER to enter each line item b/c the database is re-calculating every line item for every product ever used.

      How can I fix this? Is there a way to create a button in the product detail so that only when I click that button do the calculations re-summarize? Or is there a better solution?

      Thanks.

        • 1. Re: Calcs Slowing Me Down
          philmodjunk

          Since you only want the levels current at the time the invoice is created or at specific points in time where the status changes, you could use a script to capture the current values and store them in simple number fields that are shown on your layout. This would allow you to use scripts and buttons to either refresh current inventory levels for the entire invoice or just a single line item.

          • 2. Re: Calcs Slowing Me Down
            letra

            Is there any way to do it per product in the product detail layout?

            • 3. Re: Calcs Slowing Me Down
              philmodjunk

              Should be possible with the same basic approach. You basically copy the calculation field's expression to your clipboard and use it as the second parameter of a set field script step. To update multiple records this way, you use a loop to step through the records updating them one at a time.

              I'ts also theoretically possible to add and subtract numbers from a number field in your products table to maintain current a current inventory level, but this requires careful scripting to make sure that this works correctly in all situations--especially in situations where you might have two or more users attempting this update of the same product at the same time. (One user can lock the record--keeping the second user's script from correctly updating the field.)