0

I am trying to get to here enter image description here

From this data enter image description here

My SOQL statement so far looks like this

SELECT Designated_Bank__c, Deposit_Date__c, Actual_Payment_Amount__c, Name FROM npe01__OppPayment__c WHERE Deposit_Date__c= 2023-03-30 AND Designated_Bank__c= '218 Wintrust Checking 5906'

What I want is a single line with the Designated Bank, total for actual payment amount, and a string of PMT values that make up the payment amount.

Thank you for helping.

2 Answers 2

1

SOQL cannot do this on its own, you'll need to do some processing in Apex for this one.

To be more specific, SOQL could total the amount for you using the SUM() aggregate function (provided you also GROUP BY Designated_Bank__c). It's the "concatenate payment names" thing that SOQL can't do.

1
  • Thanks Derek, I am not familiar with Apex.
    – Malcolm D
    Apr 7 at 20:50
1

You can't get this in a single row. That's not how Salesforce works. However, you could add GROUP BY ROLLUP or GROUP BY CUBE to output additional summary rows that could include, for example, the sums grouped by bank, date, and PMT (Name), and the sum of Actual Payment Amount.

Try this:

SELECT Designated_Bank__c, Deposit_Date__c, Name, SUM(Actual_Payment_Amount__c)
FROM npe01__OppPayment__c 
WHERE Deposit_Date__c= 2023-03-30 AND Designated_Bank__c= '218 Wintrust Checking 5906'
GROUP BY ROLLUP (Designated_Bank__c, Deposit_Date__c, Name)
6
  • Thanks sfdcfox,
    – Malcolm D
    Apr 7 at 20:56
  • I get the following error [object Object]: (Designated_Bank__c, Deposit_Date__c, Name) ^ ERROR at Row:1:Column:248 field 'Name' can not be grouped in a query call
    – Malcolm D
    Apr 7 at 20:56
  • @MalcolmD Interesting. I learned something new today. Autonumber fields can't be grouped by (see this). In short, Derek is right, you're limited to writing code. What's the purpose behind your question? Maybe there's another way we can help you reach a desired outcome.
    – sfdcfox
    Apr 7 at 21:00
  • To be fair, I did forget about group by rollup/cube
    – Derek F
    Apr 7 at 21:02
  • I am trying to create input for a journal entry. Each Name (Pmt) record is the credit side of the journal entry. There may be many credit entries in the journal entry. But I only want a single line for the Debit side of the entry. The value of the Debit entry is the sum of all the Credits. So by identifying all of the Name records (PMT-XXXXX) that meet specified criteria of deposit date, designated bank, etc the dollar amount of the debit side of the entry can be determined. I hope this makes sense.
    – Malcolm D
    Apr 7 at 21:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .