Saved Search Shows Customers with Balances Over 90 Days

  1. Navigate to Lists > Search > Saved Searches > New
  2. Click Customer
  3. Search Title: Enter Title
  4. Example: Customers with Balances Over 90 Days
  5. Click Criteria
  6. Click Standard
  7. Filter:
  8. Select Inactive
  9. Inactive: Select No
  10. Click Set
  11. Click Summary
  12. Summary Type: Select Sum
  13. Field: Select Formula (Currency) 
  14. Formula: Enter Case When substr({transaction.amount},1,1) = ‘-‘ and (NVL({transaction.daysoverdue}, Round({today}-{transaction.trandate}, 0))) > 90 then ({transaction.amountremaining}*-1) When substr({transaction.amount},1,1) <> ‘-‘ and (NVL({transaction.daysoverdue}, Round({today}-{transaction.trandate}, 0))) > 90 then {transaction.amountremaining} else 0 end
  15. Formula (Currency): Select not equal to 
  16. Value: Enter 0
  17. Summary Type: Select Sum
  18. Formula: Enter Case When substr({transaction.amount},1,1) = ‘-‘ and (NVL({transaction.daysoverdue}, Round({today}-{transaction.trandate}, 0))) > 90 then ({transaction.amountremaining}*-1) When substr({transaction.amount},1,1) <> ‘-‘ and (NVL({transaction.daysoverdue}, Round({today}-{transaction.trandate}, 0))) > 90 then {transaction.amountremaining} else 0 end
  19. Formula (Currency): Select is not empty
  20. Click Results
  21. Click Columns
  22. Field: 
  23. Note: For every required field use the drop-down menu to select it and once adjusted click Add if needed.
  • Select Name
  • Summary Type: Select Group
  • Custom Label: Enter Customer Name
  • Select Formula (Currency)
  • Summary Type: Select Sum
  • Formula: Enter Case When substr({transaction.amount},1,1) = ‘-‘ and (NVL({transaction.daysoverdue}, Round({today}-{transaction.trandate}, 0))) > 90 then ({transaction.amountremaining}*-1) When substr({transaction.amount},1,1) <> ‘-‘ and (NVL({transaction.daysoverdue}, Round({today}-{transaction.trandate}, 0))) > 90 then {transaction.amountremaining} else 0 end
  • Custom Label: Enter Balance Over 90 Days
  1. Click Save & Run

Leave a comment

Your email address will not be published. Required fields are marked *