1

As far as I know, there's no way to reference a column result in a subsequent column formula, right? If, on a line, I want to subtract one column from another, the formula for the result column has to recreate the formulas from the input columns? I'm trying to create a list of our purchase orders, and show the difference between the purchase order amount and the sum of the billed amounts. This is what I have in results, with the only criteria as 'Type' = 'Purchase Order'.enter image description here

jerry
  • 31
  • 5
  • What you are trying to achieve may already be achieved using Amount and Amount Unbilled fields and appropriate formulas. No need to try summing the billing records where vendor bills may be applied to more than one PO. – bknights Jun 08 '22 at 18:41
  • @bknights, I thought that would work, as well, but the "amount paid" and "amount remaining" fields both return zero when type = Purchase Order, and Main Line = Yes. "amount unbilled" returns an incorrect number, and I haven't been able to figure out why it differs from my formulas above. – jerry Jun 17 '22 at 19:01

2 Answers2

0

You are correct, each result (column) in a saved search acts independently. You have a couple choices.

As you suggested, you can recreate the formula(s) in other columns. Be careful about the summary types though, the order of things like SUM(and stuff inside it) really matters. I've found that putting the summary as far outside the formula tends (but isn't guaranteed) to work.

Your other option is using SuiteAnalytics to create a dataset and then in the workbook, you can create calculated values with the columns that exist in the dataset. It's pretty easy once you get a solid dataset.

MChewie
  • 1
  • 2
-1

You can simply use the formula currency field and can use the formula {fieldId1} - {fieldId2}. So:

select- formula(currency), in result 
summary type- sum, in formula - {totalamount}-{billedamount}.

Note: Use the correct field/column id that you are looking for.

Thanks.

ouflak
  • 2,458
  • 10
  • 44
  • 49
SuiteStar
  • 117
  • 6
  • Welcome to SO! As it is currently written, your answer is unclear. Please edit it and add more details why and how it works. Also, please make use of the formatting possibilities we provide to make your code more readable. You can find out more at our ["How to write a good answer"](https://stackoverflow.com/help/how-to-answer) page. – ahuemmer Dec 30 '22 at 08:03