Friday, November 30, 2018

List of Sales Orders with total Invoice or Cash Sale Amount greater than Sales Order Amount

1.       Navigate to Reports> Saved Searches> All Saved Searches> New

2.       Select Transaction as search type

3.       Set the Search Title.

4.       On the Criteria tab> Standard subtab> Filter column, add the following:

a.       Type is Invoice or Cash Sale.

b.       Main Line is Yes.

c.       Created From is none of none.

5.       On the Criteria tab> Summary subtab, set the following:

a.       Summary Type = Maximum.

b.       Field = Formula (Numeric)

* On the pop-up window,

** Formula = sum({amount})-max({createdfrom.totalamount})

** Formula (Numeric) = greater than

** Value = 0

6.       On the Results tab> Columns subtab, add the following:

a.       Name > set Summary Type = Group.

b.       Created From Fields: Number > set Summary Type = Group > Set Custom Label = Sales Order Number.

c.       Created From Fields: Amount (Transaction Total) > set Summary Type = Sum > Set Custom Label = Sales Order Amount.

d.       Amount > set Summary Type = Sum > set Custom Label = Invoice Amount.

e.       Formula (Numeric) > set Summary Type = Maximum > Formula = sum({amount})-max({createdfrom.totalamount}) > set Custom Label = Difference of Invoice or Cash Sale over Sales Order.

Note: Created From Fields can be found at the bottom of the list along with the other fields.

      7.       Click Save & Run.

No comments:

Post a Comment