[mharrington]

Hello-

I want to create a report that identifies customer payments over three periods – something simple like this:

Customer…..Payments this Month…..Payments Last Month…..Payments 2 Mo Ago

ABC Cust…..250……………………….250……. ………………….250

DEF Cust…..0………………………….500…… ……………………500

GHI Cust…..500……………………….1000…… ………………….1000

I have tried to accomplish this building a transaction report from scratch, but my list of customers show $0 even though we have collected payments over the past three periods. If I change the Transaction Type filter = “Payments” and select “Invoices”, the results show values. Is this because Payments can not be tied to Customers using a report in this manner?

Here is an example of what I tried that did not work:

Columns: (Customer Name | Amount field X 3 (with Alternate Date range for the periods I want to see))

Filters (Transaction Date = previous one quarter; Transaction Type = Payment)

This produces a nice customer list with zeros in every column (trust me – we have payments for these folks). I have also tried editing the existing A/R Payment History by Payment report, but wasn’t able to use Alternate Date Ranges and had the same issue tying payment values to customers (after attempting to delete the transaction grouping that this report is based upon).

This request seemed relatively simple, but I am stumped on making this happen.
This is a cached copy. Click here to see the original post.

[Nick Horowitz]

You need to also filter for trantype=Customer Payment. (I assume you are using Invoices, A/R, and Accept Customer Payment vs. Cash Sales?). This filter should limit the returned dataset to just the Customer Payment transactions, then you get those grouped by date, i.e., subtotal/group them first by customer, then second by numeric month. This won’t give you the 3 columns, but at least you will get a hierarchal listing grouped by month vertically. Here is the formula for numeric month:

TO_CHAR({trandate}, ‘MM’)

The reason you want numeric month is so that the grouping sort will be in correct ascending order. If you print the alpha name of the month they won’t be in order. But you could put the name of the month on the results tab, and then use the above formula on the sort tab. The formula to return the alpha name of the month is:

TO_CHAR({trandate}, ‘MON’)

Here is a link to see the various format masks that can be used in TO_CHAR function for date conversions:

http://infolab.stanford.edu/~ullman/…e/or-time.html

[mharrington]

RE: Payments by Customer

Thanks for the reply, Nick. I was frustrated with this and took a breather from this for a bit…

I really need a report (not a search) because I need to web query the results. I have a report that works perfectly the way I want for Invoices by Accounting Period. When I change the criteria from my functional Invoices by Accounting Period report by moving Transaction Type from “Invoice” to “Payment”, I receive nothing but zeros (even though customers have made payments).

I think this is because payments in Netsuite are relationally tied to the invoice they are applied to and not directly to customers (this is the only explanation I can think of why this does not work). I can make this join in a search, but that defeats the purpose of creating a web query with this information. Can anyone confirm this and is it possible to have a report produce a simple matrix by accounting period showing payments made by customers?

Last question: any idea when web queries will be available for Searches?

[mharrington]

RE: Payments by Customer

Anyone have any ideas? Going once, going twice…

[Author]

Posts