Report Entity Last payment date

[silentbob]

Customer Account Statements and Payments (Custom)

This Tutorial will demonstrate how to build a Custom Account Handling Screen for Customers. It allows for Printing detailed Customer Account Statements, making Payments to Customer Accounts (via the built-in Payment Screen), and Crediting Customer Accounts. Requires SambaPOS 5.1.56 or higher Operations The Customer Account Operations Screen we will build and Automate is shown on the right. [image] Quick Nav: Account Type and Account for Transfers (for Account Payments via Paym…

Following on from this tutorial, I am trying to modify it to suit my needs.
I am trying to create a report showing all customers with open balances and show last payment date (It would be better if this could show in days Eg, 30 days ago.)

I plan on adding this to the Account statement entity screen created following the tutorial above.

The aim is to have be able to view the report, See who has not been making payments recently, click on their name, show statement , and email them a reminder.

1. Is it possible to report last payment and purchase date using custom reports?

2. Is it possible to create a report using built in reports where I can select a row or should sql be used? I need to be able to pass the Entity id to Select Customer Automation Command.

[QMcKay]

@silentbob

Is it possible to report last payment and purchase date using custom reports?

Probably, but you might need to use REPORT SQL DETAILS tag to do so.

@silentbob

Is it possible to create a report using built in reports where I can select a row or should sql be used? I need to be able to pass the Entity id to Select Customer Automation Command.

You can use a Report of any kind, it just needs to be in [#grid] mode (the leading # in the report header puts the report in that mode). Then the columns are passed via Settings Mappings in the Widget Settings as:

varName=ReportTitle.1 (column 1 of selected row/record)
varName7=ReportTitle.2 (column 2 of selected row/record)
varName2=ReportTitle.3 (column 3 of selected row/record)

[silentbob]

I have tried using AScustomerid.1 via settings mappings but it wont work. It just returns 0.

I also copied your account statement report settings and added it like
AScustomerid=Customer Account Balances.1
I noticed in your report widget there are parameters assigned, are these necessary?

[QMcKay]

@silentbob

I also copied your account statement report settings and added it likeAScustomerid=Customer Account Balances.1I noticed in your report widget there are parameters assigned, are these necessary?

That is the proper syntax, yes.

varName=ReportTitle.<column>

You access them in Automation via {SETTING:varName} or {:varName}.

I have them in the Report so that I can access the Ticket ID and the Doc Id.

[silentbob]

That’s exactly what I have been trying but it won’t work.

[#Customer Account Balance Due:1,2,2, 1.5]
>ID |Name| Phone| Balance
{REPORT ENTITY DETAILS:E.Id,EC.Customer,E.Name,E.Balance.sum:(ET=Customer) && E.AccountId>0:::$4>0}

This is the result.

EDIT: I have found my mistake. Its easier to spot problem when its all on the one page.
Setting mappings has to be the report title not the report name.
It needs to be AScustomerid=Customer Account Balance Due.1

[silentbob]

I have almost figured out how to extract the data but I cant get it to show all Customers. At the minute i have it showing only customer id =275
Another issue is my customer name is a custom field (phone number is primary) and I cant seem to extract this data from the CustomData String
[{"Name":"Customer","Value":"test customer"},{"Name":"Address","Value":null},{"Name":"Email","Value":null},{"Name":"Company Name","Value":null},{"Name":"Other Phone","Value":null},{"Name":"Date ZeroBalance","Value":"2015-12-12"}]

EDIT: I have almost got this working Now.
I just cannot manage to extract the Customer Name from the CustomData string

[QMcKay]

Easiest method is to run the SQL select statement for the customer via JScript using sql.Query().

Then also in JS (in the same script), use the JSON.parse() function to extract Custom Data fields.

[silentbob]

I have decided to just use the customer account name as this includes the customer name and phone number.
I have no understanding of JScript so i wouldn’t know where to start.