Saved Search to Display Combined Open Bills and Open Purchase Orders that Exceeds their Credit Limit

1. Navigate to Transactions > Management > Saved Searches > New.

2. Select Transaction.

3. In the Criteria tab > Standard subtab, set the following filters:

— Type = is any of Bill, Purchase Order

— Status = is any of Bill:Open, Purchase Order:Pending Supervisor Approval, Purchase Order:Pending Receipt, Purchase Order:Pending Bill.

— Main Line = is True

4. In the Criteria tab > Summary subtab, set the formula below:

— Summary Type = Sum

— Field = Formula (Numeric)

  Formula = case when ((SUM(case when {type} = ‘Bill’ then {amount} else 0 end) + SUM(case when {type} = ‘Purchase Order’ then {amount} else 0 end))) > MAX({vendor.creditlimit}) then 1 else 0 end

  Formula (Numeric) = equal to

  Value = 1

5. In the Results tab > Columns subtab, set the following fields:

— Name (Summary Type = Group)

— Document Number

— Formula (Numeric)

  Summary Type = Sum

  Formula = case when {type} = ‘Bill’ then {amount} else 0 end

  Custom Label = Total Bills

— Formula (Numeric)

  Summary Type = Sum

  Formula = case when {type} = ‘Purchase Order’ then {amount} else 0 end

  Custom Label = Total Purchase Orders

— Formula (Numeric)

  Summary Type = Sum

  Formula = (sum(case when {type} = ‘Bill’ then {amount} else 0 end) + sum(case when {type} = ‘Purchase Order’ then {amount} else 0 end)) – max({vendor.creditlimit})

  Custom Label = Remaining Credit Limit

— Vendor fields…Credit Limit (Summary Type = Maximum)

6. Click Save & Run.