Retrieve entity balances despite changing name

This is slightly related to the issue I discussed a while back.

What I am trying to do is display an Action Button on a ticket which we show the account balance for the entity. Currently the code I use for the action button is:

Account<linebreak/>[= TN('{CALL:co.getBalanceFromName('{ENTITY NAME:Customer}')}') < 0 ? 'We owe ' + F(TN('{CALL:co.getBalanceFromName('{ENTITY NAME:Customer}')}')*-1) : ''][= TN('{CALL:co.getBalanceFromName('{ENTITY NAME:Customer}')}') > 0 ? 'Owes ' + F(TN('{CALL:co.getBalanceFromName('{ENTITY NAME:Customer}')}')) : ''][= TN('{CALL:co.getBalanceFromName('{ENTITY NAME:Customer}')}') == 0 ? '---' : '']

This uses a little helper script to retrieve the balance from an accunt based on the ENTITY NAME.

My ENTITY NAME (The unique and ‘Primary Key’) is the clients’ email address. However, as you can expect, these change from time to time.

I have noticed that if a client email address changes then for new tickets this button continues to function perfectly, but when I look at old tickets for the same customer (all the tickets appear on the same customer account), then the button does not work correctly as the email address returned by “ENTITY NAME” pulls back the original customer email address, and not their current one.

Is there any variable I can pull in via a printer tag that will be constant despite some of the customer’s data changing?

Not sure what your script is doing, but have you tried these?

{ENTITY ACCOUNT BALANCE:<EN>}

{ENTITY CUSTOMDATA BY NAME:<ET>.<EN>:<FN>}

Where:

<ET> == Entity Type
<EN> == Entity Name
<FN> == Field Name

Unfortunately this doesn’t seem to work on the ticket screen (it just displays as “{ENTITY ACCOUNT BALANCE:john@smith.com}”

With this type tag is it possible to pull back the true unique ID that will remain constant even if a customer’s information changes?

You will need to use a report tag for that. Those tags are going to read whatever was stored for that ticket.

What I can’t work out is how can I pass a particular customer entity through to a report tag, nice if I use ENTITY NAME this changes if the customer details change.

No. There is no way to get the true unique Id of an Entity other than reading the [Id] column in the [Entities] table in the DB.

In Entity configuration, the Primary field, while required to be unique, should not be changed. The obvious reason for that is exactly what you are describing now… from a SambaPOS standpoint, the unique identifier has been changed… but it is only a “pseudo” primary key… in a DB, the “real” primary key is both unique and never changes.

Since you are using JScript, and I assume SQL, to derive the Entity Account Balance in some way, you might as well get the Entity Id beforehand, based on matching several fields from the Entity Data. Then use that Id to get the Balance.

The reason I mentioned those Tags is that you might be able to use them in some way to retrieve the Entity Custom Data Field Values for use in matching in the [Entities] table.

You might also consider this:

ENTITY ACCOUNT NAME:<EN>

Theoretically, even if you change the Entity Name, the Account Name will not change. So first you use the Account Name to locate the Account Id, and/or the Entity Id, then calculate your Balance.

The problem is, if I create a getIdFromEntityName() then this won’t work because if I am looking up old tickets then the Entity Name that was valid then, won’t exist in the current Entities table, since it has been updated.

Is there no way this solution can be better? As far as I can tell, all the advice on choosing the Primary Field seems to be to pick something unique (phone number, email address etc) - But the problem with all of these choices is that these do change from time to time. It seems silly to have some customer data that is based on a changeable real-world piece of data, but when it changes in SambaPOS it will break the functionality.

THINKING OUT LOUD…

Looking at the [TicketEntities] table it seems that I can see the link between the TicketID and the EntityID. So what do you think about creating something like this…

function(getBalanceFromTicketIdOrEntityName) {
   if (TicketId is NULL) {
      // This ticket is not yet committed to the DB, but we know that the EntityName value must be current, so we can safely use EntityName
      SQL(getBalanceFromEntityName)
      },
      {
      // This ticket is in the DB, the EntityName may have changed since the ticket was created, so lets find the entityID from the ticketID and subsequently find the account balance
      SQL(getBalanceFromTicketID)
   }
}

This is obviously not real code, just an outline of what I am thinking. Can you guys see a problem with this approach?

Why not create a new field with a customer account number which will not change once set up and use this as your anchor for your search?

I understand your dilemma, but nothing is going to change in SambaPOS sources at this point, and I previously explained why things are the way they are in a previous topic, so you need to work around it.

What I have been “poking” for is: how are you calculating Entity Balance? Are you using the [Accounts] table? [AccountTransactionValues]? Blah, blah?

I’m trying to offer hints at some Tags that will get you where you want to go. You can also maybe use {REPORT ENTITY DETAILS:X}, maybe not.

If the Ticket is not committed, then it is not part of a balance. In fact, if the Ticket has not been paid by (Customer) Account, then it shouldn’t be part of a balance either, so not sure what the first part of your pseudo-code is going for.

Get the Entity Name.
Use that ^ to get the associated Account Name using {ENTITY ACCOUNT NAME:<EN>}.
Use that ^ to get the Account Id using SQL.
Go to town with calculating the Entity Balance…

This is an unnecessary burden on the user. We don’t have account numbers, we identify customers by their names, and if it comes to it, countries and email addresses. SambaPOS already creates an EntityID, so it seems over-the-top to ask the sales team to make up an account number for every client.

Yep, I know that, but it’s OK. We just like to know what the balance is prior to making a new ticket (do we really want to sell something else to a customer who already owes a lot of money???).

I know this functionality can be added to the ticket header info and that there are options for how positive and negative balances are displayed, but we need a button to jump to the customer’s account anyway, so similar code is required to reliably find the account for a customer and navigate to that account.

Bu this will only work if the Entity Name has not been updated, if I look at an old ticket it won’t work will it, since that Entity Name won’t exist anymore??

True.

How about use {ENTITY NAME} or {ENTITY NAME:<ET>} and SQL to get the Entity Id, and the Account Id.

It won’t matter of the Entity Name changed, because the Entity Id and Account Id will not have changed, ever.

You could also use the above Account Id to join to the [Accounts] table to get the Account Name (which should also not change), then use that with {ACCOUNT TOTAL:<AN>} or {ACCOUNT DEBIT TOTAL:<AN>} with {ACCOUNT CREBIT TOTAL:<AN>}.

Yes, but this is exactly the problem I am facing… If John Smith (ID: 17) changes his email address from jsmith@gmail.com to johnsmith@hotmail.com then {ENTITY NAME} will return whichever email address he was using at the time the ticket was issued. So if I use SQL to try to find his ID based on his current email, it will work no problem, but when I am looking at an old ticket, and {ENTITY NAME} returns jsmith@gmail.com, I won’t find that in the Entities table and therefore I can’t retrieve the ID using SQL.

How do you have you primary key set-up? If it based on customers’ information? How do you deal with them updating email addresses or phone numbers (if these form part of your primary key)?

Typically you wouldn’t change a primary field.
But surely this is not a common issue.
How often so you change an email and need to look back at a old ticket who’s emails changed and review their balance from the old ticket…
Is the option to restrict the button to only show on current tickets?

But then what should I use? People change names, numbers and email addresses.

It’s actually been happening quite a lot. We often get bookings from agencies where we know the customer’s name and we create an account and book a bunch of diving and charge it against their customer account so we have a record of what they owe. Unfortunately, these agencies often don’t provide the customer email or phone number, but as part of the check-in process when they arrive we take these details and update them in SambaPOS, but that means when we access their old ticket to show them what they owe the account button no longer works.

I can see that it’s not going to be possible to access the Entity ID directly - So I think I will try and use the pseudo code I outlined here and I’ll report back.

I use Customer Name as Primary field. I don’t have enough regular Customers to care about any of this :wink:

Herein lies the rub. A primary field should not change.

This is why the suggestion regarding using a unique, never-changing Customer/Client/Member Identifier is probably the way to go. You don’t need your Staff to generate this Id; it can be automated during Customer creation. You could probably even use the [Id] field out of the [Entities] table. Or it could just be another sequential number, maybe with more category-like information such as A0000-000001, A0000-000002, B0000-000023, etc, where the A/B prefix could mean something to your system (future-purpose).

You could script this for your existing Clients to update their data.

I know - and the real primary key, the EntityID, doesn’t change. This entire problem exists because of this pseudo-primary key concept.

But to do this I need to create a completely customised customer entry form, correct? - I can’t just use the standard functionality?

I think you could just use standard functionality.

My guess is you could capture event Entity Updated and use Update Entity Data to attach the Customer Number. Not sure, need to play with it to see if it possible.

Then again, a custom form isn’t a bad idea either…

To be honest it sounds like to me you should use a unique customer number for primary and everything else be custom data. You could automate creation of customers and use one of many methods to assign unique numbers. That would be a lot of work to convert to that type of system. But once you did it would solve most issues.

1 Like