Saved Search to Display Old and New Amount Value when there are Changes on Line Level of a Transaction

Scenario:

User wish to search for any price (Amount) changes on line level of a Transaction. This article shows how to create a search to display the old and new values.

Solution:

To Display old and new Amount value together with the date when there are changes on line level of a Transaction, create the following Transaction Saved Search.

Navigate to List > Search > Saved Search > New

Select Transaction

Under Criteria Tab:

  1. Type Is Sales Order

  2. Main Line is False

  3. Historical Transaction(Custom Column) is True

  4. System Notes : Date is not Empty

In the Results tab sort by Date

  1. Date (Group)

  2. Document Number (Group)

  3. Name (Group)

  4. Amount (Transaction Total)(Group)

  5. Formula(Text)(Group) Under Formula add the following line
      case when {systemnotes.field}='Total' then {systemnotes.oldvalue} end

  6. Formula(Text)(Group) Under Formula add the following line
      case when {systemnotes.field}='Total' then {systemnotes.newvalue} end

  7. Formula(Date/Time)(Group) Under Formula add the following line
      case when {systemnotes.field}='Total' then {systemnotes.date} end

Save and Run.

Note: This Saved search will show only the transactions that has been changed.