https://netsuiteprofessionals.com logo
Join Slack
Powered by
I’ve asked this question here before but I’m going...
# suiteql
j
I’ve asked this question here before but I’m going to try again in case someone new has an idea. How in SuiteQL, or SuiteScript, or even Saved Search, would I generate the same list of transactions (Credit Memos and Payments) that appears on the “Credits Applied” tab on a particular Customer Payment?
c
When you look at a Customer Payment ABC, you see a list of "Credit Applied" that includes other payments? Are those the other Credit Memos and Payments applied to the invoice(s) which ABC is applied to?
I see what you're talking about now - the credits that were chosen to accompany a customer payment when entering / editing the payment. I've poked at next/previous transaction line link tables and don't see anything that groups the payment and credits together.
j
I’ve been trying to find this link for literally 2 years now
like HOW does NS know that this particular list of payments and credits were applied somehow “in association” with the Customer Payment record I’m looking at?
it must be linked in the database somewhere
ntll has failed me
c
It's in their database, but not our database...
j
it’s my f*cking data
sorry but I’m super frustrated with this problem
c
Right there with you - I've shaken many a fist at the sky over limitations like this
I have an entirely gross and unstable idea - you can request the transaction using the record URL and parse the reply for the data in the 
credit_form
element
We're doing something similar right now to see "Review Negative Inventory" results for more than a day at a time
j
ew
c
😄 ¯\_(ツ)_/¯
g
Not sure I’ve fully grasped the question, but I think one difficult part is due to the fact that a credit or payment (etc.) can be partially applied to multiple transactions. In saved searches I’ve used the “Applied to Transaction” fields and “Applying Transaction” fields, but not sure this will help answer your question.
c
@George McMullen here's my understanding of the situation: When you accept a customer payment, you can select credits to apply towards the selected invoice(s) in addition to the payment amount. This ends up relating the customer payment and any credits to the invoice as an applying transaction, and when you see the payment in the UI, you can see the credits that were selected at the same time; however, there doesn't appear to be a way to find via SuiteQL or search those credits in relationship to the payment.
Copy code
Invoice
|__ Payment A
|__ Credit B
|__ Credit C
|
|__ Payment B
In the above example, we can clearly see that the 4 applying transactions related to the Invoice, but we can't see from the data that Payment A and the two credit are part of an "application group"
g
Got it. Yeah that is a pain. Glad I’ve not had to deal with that yet!
j
I’ve cracked it y’all
with @battk’s suggestion
Made a template that spits out this:
Copy code
<#if record.apply?has_content><apply>[<#list record.apply as apply>{"due":"${apply.due}","currency":"${apply.currency}","applydate":"${apply.applydate}","disc":"${apply.disc}","internalid":"${apply.internalid}","total":"${apply.total}","amount":"${apply.amount}","refnum":"${apply.refnum}"}<#if !apply?is_last>,</#if></#list>]</apply></#if>

<#if record.credit?has_content><credit>[<#list record.credit as credit>{"currency":"${credit.currency}","creditdate":"${credit.creditdate}","appliedto":"${credit.appliedto}","internalid":"${credit.internalid}","amount":"${credit.amount}","type":"${credit.type}","refnum":"${credit.refnum}"}<#if !credit?is_last>,</#if></#list>]</credit></#if>
then in a Suitelet called by custom “Print (PDF)“, “Print (Excel)” or “Print (CSV)” buttons I do a 
render.transaction
with a form ID that uses my “fake” template
I grab the contents of the 
<apply>
and 
<credit>
tags, JSON.parse them.
Then use SuiteQL to get the rest of what I need for additional columns
and Bob’s your uncle
g
Wow. Are you creating a report with this or just dealing with individual transactions?
j
Just individual transactions (though a report would be possible also). We basically need to be able to print out the payment with more information than is possible with the regular templating system. Also, we need to be able to print it out as an Excel.
👍 1
18 Views
Open in Slack
PreviousNext
Powered by