Use ticket lister, or entity grid to display some customers

In my dive shop, many customers pay a deposit before they actually go diving. Sometimes they pay months in advance, sometimes just hours in advance. When an order is added to a ticket we also add the date for the activity.

When a deposit is paid I create a ticket with the full priced activity. The deposit is processed as a payment and the remaining balance is added to the customer account.

I know it is possible to see a list of customers who owe money by using the accounts screen and filter zero-balance accounts. The problem with this is that it will also show me the customers who are booked for months in the future that I really donā€™t need to worry about today.

What I would like is to see just a list of customers who have an outstanding balance AND have an order which is dated (via order tag) for today or in the past. This way I can focus my eyes on the customers who owe me money right now.

I have tried using the Entity Grid, but could not work outā€¦

  • how to tweak the details that or displayed
  • how to filter based on account balance
  • how to filter based on the orders that the particular customer has made

I have tried using the Ticket Lister, but could not work outā€¦

  • how to filter based on account balance
  • how to filter based on the orders that the particular customer has made

Any pointers would be greatly appreciated. I guess this could also be useful for people who are running restaurant reservations and want to see which customers are due today.

The first thought that pops into my head is to use Tasks. You might be able to use Entity Search or Entity Grid Widgets, or Ticket Listers to do what you want, but I think Tasks are going to give you much more flexibility in the long-run since a Task Type can be expanded to include any type of data you want, not just Entities or Tickets and Orders.

I have not seen a really good implementation of a Reservation system yet. Certainly someone must be running some type of setup that supports this. This Topic is the only one that I remember:

I know @JTRTech has also done a lot of work in this area as well for Hotel bookings, but it uses a 3rd party online PMS which SambaPOS communicates with ā€¦

SambaPOS API Integration with NewBook PMS/Booking System


The first thought that pops into my head is to use Tasks. They can be used for simple things like a To-do list, and I also created a Timeclock using Tasks rather than Entity State Logs which was the original way we developed the Timeclock. Although Entity State Logs could probably be used, Tasks will support a much more flexible and advanced method for such things. Kitchen Displays have also been created using Tasks, while the original method used Ticket Listers.

Tasks appear to be really simple at first, and TBT they are. Similar to an Entity State Logā€™s Custom Data field, Tasks have a main Content field where you can store extra information. But much more than that, Tasks have fields for Name, User, Start/End Date/Time, State Logging, and most of all: Custom Fields that you can pre-define as part of the Task Type, or even on-the-fly during Task creation.

Interacting with Tasks can be done in a couple ways which include the Task Editor Widget, and Report Tags, which can filter the Tasks and Task data that you see based on any part of the Task Type data.

You might be surprised to learn that a Report can do more than just show you rows of dataā€¦ you can actually interact with a Report by switching on Table mode. This gives you access to row-by-row, and cell-by-cell values that you can use with Automation. The Timeclock Punch Editor is a good example of this, and in itā€™s implementation, we use the actual Row ID from the DB to access and modify data directly to edit Punch Data.

My Tutorial on Account Statements also uses a Report in Table mode to pull up associated Customer Tickets.

In addition, GraphQL has very good support for manipulation of Tasks as well so that we donā€™t need to mess with the DB directly like we do with the Punch Editor. GQL will allow you to Add Tasks, Toggle Completion status, change Custom Data field contents, and update Task States. Also, when you pull data from a Task Type using GQL, you can filter it on any part of the Task Type, in the same way you can do with Task Reports.

Ok enough about Tasks.

You mention trying to use an Entity Grid or a Ticket Lister, but your filtering options with those Widgets can be limited. So let me draw you back to something I mentioned in my previous post: Reports in Table mode.

Using a Report to supply parameters to another Report

Reports can be filtered in many ways as well, and it depends on the Report Type you are using such as Ticket, Order, Entity, or Transaction Reports. You can also combine certain Reports and use @{REPORT::,} to create lists that feed into a subsequent Report.

For example, you could list Customers that have an outstanding balance, and feed that list into a Ticket Report that is filtered to show those Ticket having a certain Date Tag.

Or vice-versa, you could list Tickets to show those which have a Tag containing a date of Today or in the the past, and feed that list into an Entity Report that is filtered to show Entities with an outstanding balance.

So the first step would be to come up with a suitable Report that shows the data you want to see. And then you can turn on Table mode to be able to interact with that Report. Here is an example that shows Ticket Payments:

[Ticket Payments:0.01,1,3,3,3, 1.5, 2, 2, 2, 2, 2]
@{REPORT TICKET DETAILS:T.Id.desc::,}

>|TID|Date Time|Customer|Table|Ticket Total|Cash|CC|CA|PAY TTL|+/-

|{REPORT TICKET DETAILS:T.Id:T.Id==$1}|{REPORT TICKET DETAILS:T.Date,T.Time:T.Id==$1:{0} {1}}|{REPORT TICKET DETAILS:EN.Customers:T.Id==$1}|{REPORT TICKET DETAILS:EN.Tables:T.Id==$1}|[=F(TN('{REPORT TICKET DETAILS:T.TotalAmount:T.Id==$1}'),'0.00;(0.00); ')]|[=F(TN('{REPORT PAYMENT DETAILS:P.Amount.Sum:((PT=Cash) OR (PT=Cash USD)) AND T.Id==$1}'),'0.00;(0.00); ')]|[=F(TN('{REPORT PAYMENT DETAILS:P.Amount.Sum:((PT=Credit Card) OR (PT=Credit Card USD)) AND T.Id==$1}'),'0.00;(0.00); ')]|[=F(TN('{REPORT PAYMENT DETAILS:P.Amount.Sum:(PT=Customer Account) AND T.Id==$1}'),'0.00;(0.00); ')]|[=F(TN('{REPORT PAYMENT DETAILS:P.Amount.Sum:T.Id==$1}'),'0.00;(0.00); ')]|[=F((TN('{REPORT PAYMENT DETAILS:P.Amount.Sum:T.Id==$1}')-TN('{REPORT TICKET DETAILS:T.TotalAmount:T.Id==$1}')),'0.00;-0.00; ')]

>> | | | |GRAND TOTALS|[=F(TN('{REPORT TICKET DETAILS:T.TotalAmount.Sum}'),'0.00;(0.00); ')]|[=F(TN('{REPORT PAYMENT DETAILS:P.Amount.Sum:((PT=Cash) OR (PT=Cash USD))}'),'0.00;(0.00); ')]|[=F(TN('{REPORT PAYMENT DETAILS:P.Amount.Sum:((PT=Credit Card) OR (PT=Credit Card USD))}'),'0.00;(0.00); ')]|[=F(TN('{REPORT PAYMENT DETAILS:P.Amount.Sum:(PT=Customer Account)}'),'0.00;(0.00); ')]|[=F(TN('{REPORT PAYMENT DETAILS:P.Amount.Sum}'),'0.00;(0.00); ')]|[=F((TN('{REPORT PAYMENT DETAILS:P.Amount.Sum}')-TN('{REPORT TICKET DETAILS:T.TotalAmount.Sum}')),'0.00;-0.00; ')]

That ^ produces something like this:

Letā€™s look at how @{REPORT:::,} lists can be used to feed data from one Report into another. This line creates a comma-separated list of Ticket IDs:

@{REPORT TICKET DETAILS:T.Id.desc::,}

Those IDs can be fed into subsequent Reports using a $variable which is referenced by $1 in lines like this:

{REPORT TICKET DETAILS:T.Id:T.Id==$1}|{REPORT TICKET DETAILS:T.Date,T.Time:T.Id==$1:{0} {1}}
                                                                           ^^^^^^^^

{REPORT TICKET DETAILS:EN.Customers:T.Id==$1}
                                    ^^^^^^^^

Can you come up with a Report that shows the data you really want to see? If not, try making a mockup in Excel or Word or whatever to indicate exactly how you want it to appear.


The almighty #Table Report

We can turn on Table mode with a simple change of the Header line. This is our Header:

[Ticket Payments:0.01,1,3,3,3, 1.5, 2, 2, 2, 2, 2]

When we add a # to the beginning of that Header as follows, we activate Table mode:

[#Ticket Payments:0.01,1,3,3,3, 1.5, 2, 2, 2, 2, 2]
 ^ Table mode Activated!

In Table mode, we have access to Row and Cell data ā€¦

All of sudden, we have the ability to pick out Rows and Cell data and invoke Automation based on the Cell data, but we also have control over what is displayed by way of Sorting, Filtering and Grouping.

3 Likes

I didnā€™t do a reservation system in samba as our demands exceed what I can see samba for filling in features like booking channels etc.
The newbook systems intergrated accounts with 3rd party.
The hotel setup I did in samba was only for open accounts of inhouse guests based on using a seperate actual booking system.
A full blown reservation system within samba would be a very complex setup I havenā€™t seen attempted yet.
It depends on what you want to gain, basic bookings maybe posible but when you weigh time/functionality it may be better to used3rd party system and make more basic intergration.

Thanks as always QMcKay - Sorry I have not replied sooner, I have been doing a lot of reading about tasks (which you seem to like a lot! and custom reports.

I am happy with the way in which we process transactions ahead of a customerā€™s arrival, so I have been concentrating on custom reporting and I think I am starting to make progress. In particular, I think the table report approach should work well for us here.

Just one thing as I move forward - Do you know if itā€™s possible to include comments in the customer reports (I really like adding comments to things like this so I remember what I was doing when I come back to things months later). I have tried using -- Comment (as in Ticket layouts) but that is not working.

Here is what I have got so far to give me a list of customers who owe money (with comments that appear in the report at the moment).

-- Find customers who have non zero balances
--                    :Fields to reutrn
--                    |                  :Conditions (select the entity type I want to look for)
--                    |                  |              :Although we are asking for 2 fields, only output the first one (index 0)
--                    |                  |              |   :???Not sure what this section is for???
--                    |                  |              |   |:Only output the value where the second field is not zero (the second field is the balance, even though it's not output)
--                    |                  |              |   ||       :Use a comma to separate each field (useful because we will use this in another report)
{REPORT ENTITY DETAILS:E.Id,E.Balance.sum:(ET=Customers):{0}::($2!=0):,}

I wonder if you or @emre could answer the question on the 5th line (with the ???) - I have read Emreā€™s post here which tries to explain these sections, but it doesnā€™t quite match with the number of sections I have here.

Hi @QMcKay

I have been going around and around trying to find the solution to this using custom reports. I really like the idea of using the table mode you suggested for reports. Ultimately, all I am looking for is a list of customers who owe me money around about now (not those customers who have partially paid for diving but are not actually arriving for quite some time in the future)

I have changed my workflow, so now I am using a Ticket Tag called ā€œArrivalā€ which uses a Date format.

I have used the following:
Table1 correctly lists all customer who owe money
Table2 correctly lists only the customers who owe money now, but if lists customers multiple times if the have multiple tickets. How can I adjust the output of this report so it effectively does a SELECT DISTINCT?

>> CSV list of customers who have non-zero balances
{REPORT ENTITY DETAILS:E.Name,E.Balance.sum:(ET=Customers):{0}::($2 != 0):,}

[Table2]
>> CSV list of ticket IDs belonging to customers from Table 1
@{REPORT ENTITY DETAILS:E.Name,E.Balance.sum:(ET=Customers):{0}::($2 != 0):,}
{REPORT TICKET DETAILS:EN.Customers:(TTDR.Arrival=Last 30 Days) AND (TEN.Customers=$1) OR (TTDR.Arrival=Next 3 Days) AND (TEN.Customers=$1):,}

Current output:
Table 1
John,Steve,Dave,Spyro

Table 2
John,John,John
Steve
Spyro,Spyro

Soā€¦ How can I change Table2 so that it outputs each name only once, so I can turn on Table mode and connect each Entity name to an Entity account statement.

You might be able to use a grouping feature such as:

{REPORT TICKET DETAILS:EN.Customers.asc: ...

However, I would likely approach it in a different way, and not use Tickets at all. Instead use an Entity State or Entity Custom field to store the Arrival, and filter on the State or Custom Field.

Hi @QMcKay - I know itā€™s been a while, but I am keen to crack this element of the systemā€¦

I re-read your accounts tutorial, but I struggled to understand how you linked a value from a report in ā€œtable modeā€ to some automation commands. Specifically, if I have a list of customers in a table, how can I link that to another screen that will allow me to display a list of tickets for a particular customer. I donā€™t understand how itā€™s possible to switch entity screens and ā€œfeed inā€ these bits of data to display a report for a specific entity.

I am probably missing something really obvious, but I just can figure it out :frowning:

Custom Report Viewer Settings has a section called Setting Mappingsā€¦

For the Account Statement screen, these are the mappings:

ASdate=Account Statement.1
AStid=Account Statement.2
AStno=Account Statement.3
ASdesc=Account Statement.4
ASamt=Account Statement.5

The part on the left is the name of a Local Program Setting that you want to use.
The part on the right is the Report Header and the column number.

LocalProgramSettingName=ReportHeader.ColumnNumber

:warning: Important: You need to use the Report Header, NOT the Report Name. This is because a single Report can contain multiple Headers, and you need to specify which Header (section) you want data from.

So if you highlight (select) a row on the Report, and then trigger an Automation Command, you will have access to the all the cell data for that row via the Setting Mappings, which can be obtained via the assigned Local Program Setting name.

For example, {SETTING:ASdate} will contain data from Column 1 of the selected row, and {SETTING:AStid} will contain data from Column 2 of the selected row, and so on.

1 Like

Perfect, thanks @QMcKay - This is exactly the bit I was cnfused about - I could see you were pulling program settings but couldnā€™t for the life of me see when or where you were setting them. I was looking out for you using the AS Store rule but now I see that these are autimatically stored as program settings on the flyā€¦ I will give it a try now. Wish me luck! :slight_smile:

1 Like

I have a ticket tag called ā€œarrivalā€ and I only want to see customers who owe money and who are currently diving with us. If they owe us money, but arenā€™t actually diving for quite some time in the future then I really donā€™t care too much - I want to focus on the customers who are ā€˜activeā€™ at the moment.

This is what I have so far for a report:

[#Outstanding accounts:1,1]

Name|Balance
@{REPORT ENTITY DETAILS:E.Name,E.Balance.sum:(ET=Customers):{0}::($2 != 0):,}
{REPORT TICKET DETAILS:EN.Customers.asc:(TTDR.Arrival=Last 30 Days) AND (TEN.Customers=$1) OR (TTDR.Arrival=Next 4 Days) AND (TEN.Customers=$1):,}
Using something like this, how can I also include the account balance and, hopefully, the account name in the output from the report. As far as I can tell, the only thing that is exposed by REPORT TICKET DETAILS is the entity name.

I know @QMcKay has an excelent tutorial for account statements, but I really need to use the POS menu for topping up customer accounts because I need to use multiple currency calculations, rounding and payment surcharges which are all handled well in the POS settle screen, as opposed to Account Transaction Document approach.

One other thingā€¦

Sometimes the ticket tag is net set. How can I change the report so that it also shows tickets from customers witha zero balance and no arrival date set?

Maybe you need to use REPORT TICKET DETAILS for the parameter source and generate report by using REPORT ENTITY DETAILS.

1 Like

I had considered this, but I thought that in terms of efficiency, the number of tickets in a 35 day period is likely to be MUCH larger than the number of clients who owe money, so I thought it would be good to do the Entity based query first to speed up the processing of the results???

Iā€™ll give it a go for now, just wasnā€™t sure if it was the right approach from a performance point of view?