5 Replies Latest reply on Aug 10, 2016 6:29 AM by PrisonProfessor

    Conditional Calculation


      I'm trying to calculate an inventory quantity but have not had success in getting the right inventory quantity to display.


      The records are a portal but all the fields are within the same table 'RecordsOfUsage'


      I have three fields


      QtyAdd defined as a number default data value is 0

      QtyDistribute defined as a number default data value is 0

      QtyInventory defined as a calculation


      The calculation for QtyInventory is as follows;


      If (QtyAdd  ≠  0; QtyInventory + QtyAdd; If (QtyDistributed ≠  0; QtyInventory - QtyDistributed;QtyInventory))


      This is a sample of my results






        • 1. Re: Conditional Calculation

          Is the answer suppose to be 40?  It appears you are trying to take the Inv number 50 and minus 10.  It's not going to work the way you have it set up, and you calculation is wrong.  What happens if someone puts numbers in both Qty Dist & Qty Add (say on your first portal row, 10 and 50)?

          You need to keep a running total of the inventory in another field.

          Here's a sample from Eos that may point you in the right direction

          • 2. Re: Conditional Calculation

            Thanks, Steve. I duplicated your example but I'm still not getting the expected results.


            I'll just keep plugging away

            • 3. Re: Conditional Calculation

              PrisonProfessor wrote:

              The records are a portal but all the fields are within the same table 'RecordsOfUsage'

              Just because all the records are in the same table doesn't mean they can automatically "talk to one another". What is the relationship you use to display the portal records? The calc you have indicates that it is only relating internally within a single record.

              Apart from that, it looks to me as if you should review your data structure anyway. Guessing from your screenshot, this looks like maybe an inventory of books or something similar, and the information in the portal looks more like transaction information—buying, lending, etc. So it seems to me there could be, within the book record, a single unstored inventory calc field which shows the number in stock at any given moment. In a separate transaction table are records recording purchases that add to stock, and loans, sales, losses, etc that subtract from stock, and the inventory calc simply sums all of this together: stock if any + additions – subtractions.

              1 of 1 people found this helpful
              • 4. Re: Conditional Calculation

                Thanks, Keywords; My plugging away has made some progress but I haven't arrived at the 'Yippee'


                I think I grasp what our saying so maybe I'll take what I have and add the field you suggested to the Books table and see if I get to Yippee


                I think right now I'm going to shift to something else because I'm at the point, where everything is becoming more garbled than clear when it comes to this issue.


                Thanks, will get back one way or the other

                • 5. Re: Conditional Calculation

                  OK, I'm at YIPPEE; Just before taking a break from it I was thinking you solution was similar to one I originally had. Using the 'TrnsctionType' & 'SignedAmt' logic suggested by Steve and using Keyword's suggestion of a Total Current filed on primary table, everything fell into place.

                  Thanks, Steve & Keyword