1 Reply Latest reply on Jun 26, 2015 10:25 AM by philmodjunk

    Calculating paper costs

    tdeppen

      Title

      Calculating paper costs

      Post

      I am having a hard time creating a calculation which will look at up to 4 different papers, looking those paper values up in a separate database and bringing the prices back into my original database to be multiplied by the quantity then added all up. 

      I have 4 different paper fields named "PPR NO1, PPR NO2, PPR NO3, and PPR NO4". These fields are linked to a paper database (with a paper code), which stores the description, size, vendor, and cost of each paper. My manager set up this calculation: 

      (PPR COST SHT SET * PPR QTY USED) + (PPR COST SHT SET * PPR QTY USED2) +( PPR COST SHT SET *PPR QTY USED3) + (PPR COST SHT SET * PPR QTY USED4)

      This only seems to add up the qty from all 4 papers and multiplys that number by the first paper price only. It is not looking at the other paper codes and looking up their costs. In fact I don't really see where it even references the first paper "PPR NO1" The PPR COST SHT SET is looking up the price in the paper database...it seems by default. I am not sure why it is using the paper code from PPR NO1. 

        • 1. Re: Calculating paper costs
          philmodjunk

          Look ups require relationships. What relationships have you set up?

          What I see here would seem to require 4 different table occurrences of the "paper database" in order for you to look up four different values from four different records in the "paper database" into one record of the table where you have defined this calculation.

          There are simpler ways to set this up--such as setting up a set of 4 records, one for each of your four paper cost values that you want to look up for the same job or invoice. You can then set up one relationship from this table to the table of paper costs.