4 Replies Latest reply on Jun 2, 2016 2:35 AM by Mike_Mitchell

    Portal Calculation Help?

    atlanticind

      Im currently using a portal to relay certain information from my main database, the information that is related between the two are the fields of 'Brand' and 'Part Number'.

       

      The only other fields i have in my portal will be

       

      1. Sale Or Purchase (a drop down list to select either sale or purchase).

      2. Quantity

      3. Date

       

      Is there any kind of calculation so that when i select either the 'sale' or 'purchase' option in the drop down list that it will either add or subtract the quantity, this way my stock list can keep up to date with the correct quantities?

        • 1. Re: Portal Calculation Help?
          user19752

          Add a field in portal table (not need to be in portal)

          4. stockCount (sorry I don't know best English word for this. Choose any of you want.)

          Case ( Sale Or Purchase = "Sale" ; - Quantity ; Quantity )

          then you can get Sum ( portal::stockCount )

           

          But I think this is not for real business. You would need to limit data of summing with term of Date.

          • 2. Re: Portal Calculation Help?
            GaryTate

            I use a sock movement table that sits between the order and stock item. This simply holds key fields pointing to order & stock and the quantity. You can then either have a calc field in stock table that sums up all movements, or I tend to run a script to calculate the stock level as a number.

            • 3. Re: Portal Calculation Help?
              atlanticind

              The Case calculation, if I had a sale or purchase option, would I put the same calculation for if I selected purchase

               

              This way either option will remove quantity if sold or add quantity if purchased?

              • 4. Re: Portal Calculation Help?
                Mike_Mitchell

                Agreeing with user19752 (and expanding), with any kind of inventory system, it's usually better to have a table of "transactions" for events where the stock total is changed. Each time a record is created in this table, a script updates the quantities. Otherwise, as you continue adding data to the system, your calculations will become progressively slower until the system isn't usable.