Create a Custom Transaction Search Similar to the A/R Summary Report

1. Navigate to Lists > Search > Saved Searches > New >Transactions

1.    2. On the Criteria tab > Standard subtab: Add the following fields:

---Account Type =Accounts Receivable

---Status = is none of Invoice:Paid In Full, Payment:Unapproved Payment, Payment:Not Deposited, Payment:Deposited

2.     3. On the Results tab > Columns subtab : Add the following fields:

          A.  Field:Name
                SummaryType: Group
                Formula: (Blank)
                SummaryLabel: (Blank)

B. Field: Formula (Currency)
                Summary Type: Sum
                Formula: case when ({type} <> 'Invoice' and trunc({today})-{trandate} between 1 and 30) then {amount}  when ({type} = 'Invoice' and trunc({today})-{duedate} between 1 and 30) then {amountremaining} end

     Summary Label: 1-30

C. Field: Formula (Currency)
     SummaryType: Sum
     Formula: case when ({type} <> 'Invoice' and trunc({today})-{trandate} between 31 and 60) then {amount}  when ({type} = 'Invoice' and trunc({today})-{duedate} between 31 and 60) then {amountremaining} end

Summary Label: 31-60

D. Field: Formula (Currency)
     SummaryType: Sum
    Formula: case when ({type} <> 'Invoice' and trunc({today})-{trandate} between 61 and 90) then {amount}  when ({type} = 'Invoice' and trunc({today})-{duedate} between 61 and 90) then {amountremaining} end

      Summary Label: 61-90

E. Field: Formula (Currency)
     SummaryType: Sum
      Formula: case when ({type} <> 'Invoice' and trunc({today})-{trandate} >90) then {amount}  when ({type} = 'Invoice' and trunc({today})-{duedate} >90) then {amountremaining} end

      Summary Label: Over 91

           F. Field: Formula (currency)

    SummaryType: Sum
    Formula: NVL(case when ({type} <> 'Invoice' and trunc({today})-{trandate} >90) then {amount}  when ({type} = 'Invoice' and trunc({today})-{duedate} >90) then {amountremaining} end,0)  + NVL(case when ({type} <> 'Invoice' and trunc({today})-{trandate} between 1 and 30) then {amount}  when ({type} = 'Invoice' and trunc({today})-{duedate} between 1 and 30) then {amountremaining} end,0)  + NVL(case when ({type} <> 'Invoice' and trunc({today})-{trandate} between 31 and 60) then {amount}  when ({type} = 'Invoice' and trunc({today})-{duedate} between 31 and 60) then {amountremaining} end,0) + NVL(case when ({type} <> 'Invoice' and trunc({today})-{trandate} between 61 and 90) then {amount}  when ({type} = 'Invoice' and trunc({today})-{duedate} between 61 and 90) then {amountremaining} end,0)

Summary Label: Total Open Balance

4. Click on Preview or Save button