Interesting idea for multiple entities using same account

Not seen previous discussion similar but maybe @pauln you might be interested to make your accounts system even more complex :stuck_out_tongue:

So client is starting a cafe in a gov/training center.
He wants to use fobs to allocate entities and in turn credit accounts.
While setting up I had a thought about what if you had multiple people from same office running a shared account (although not sure he needs this functionality I wanted to give it a try)

By default accounts are created per entity be we can obviously select alternative accounts and in turn say have multiple entities charging to a single account. Got that bit fine although would ideally like the account drop down to be available on edit entity screen in POS.

Ok, the issue Ive hit now is reporting account balances now falsely increases as way it seems to usually be done is reporting E.Balance on report entities however this now will report an account balance multiple times where multiple entities are set to the same account.

I cannot see a way to report a list of accounts … unless I missed it so will opt to use sql to get a list of Customer Accounts and use that list to get account totals…

Any thoughts?

1 Like

What am I doing wrong here?

[Account Balances:2, 1]
@{REPORT SQL DETAILS:SELECT [Name] AS [AccountName]  FROM [Accounts]  WHERE [AccountTypeId] = 5:F.AccountName::{0}}

If I set the value it works…;

[Account Balances:2, 1]
@{REPORT SQL DETAILS:SELECT [Name] AS [AccountName]  FROM [Accounts]  WHERE [AccountTypeId] = 5:F.AccountName::{0}}
Joseph Trice-Rolph|{REPORT ENTITY DETAIL:E.Balance:(EN=Joseph Trice-Rolph)}

Actually simplify to this;
if the $1 would work…

Are you doing that manually?

Have not checked but should there be a coma “,” in the @-Create a list-formatting?

1 Like

Yes setting it manually via manage entity, was a thought that came up while tinkering.
Ideally would be Gould to couple with setting a description so a statement for the master entity/account would show the entity name for the linked entity.
Will try the comma, do you mean after the {0}?

Yep I think so:

[Sales:1, 1]
@{REPORT TICKET DETAILS:T.DepartmentId,T.TotalAmount.sum:(DE={SETTING:DEPARTMENT}):{0}:,}
{REPORT TICKET TYPES:!PreOrder && TotalAmount >= 0 && DepartmentId = $1}

Sample syntax from mine…


That will convert report values to a comma separated list as @parameter format expected.


That did the trick…
How could I reduce the list here to only show non 0 balance?

Tried adding E.Balance!=0 and (E.Balance!=0) but know != is not always useable but even just = doesnt have expected effect…
So was thinking >0 or <>0 is thats an option but no luck there wither.

Are you not missing an ‘S’ in there?


Hmm… apparently, there are both:

I’m guessing the reason E.Balance!=0 does not have the desired effect is because you are combining 2 different Report Tags… the SQL DETAILS tag is not constrained by the REPORT ENTITY DETAILS tag… they are not related.

THe sql report means it only lists actual accountsrather than treating linked accounts as seperate accounts when using entity details.


Joe is set to use Joseph Trice-Rolph account (total customer accounts is 10 not 20.

@{REPORT SQL DETAILS:SELECT [Name] AS [AccountName] FROM [Accounts] WHERE [AccountTypeId] = 5:F.AccountName::{0}:,}
Fixes that by listing accounts not entities and the $1 now works with the comma added;

But now want to ignore 0 balances…

As != doesnt always work I test with just = which should show Smith account only but causes table to not show…
{REPORT ENTITY DETAILS:E.Name,E.Balance:(EN=$1) AND (E.Balance=0)}
{REPORT ENTITY DETAILS:E.Name,E.Balance:(EN=$1) AND E.Balance=0}

If your setup is like mine, the Account Name is not the same as the Entity Name …

SELECT [Name] AS [AccountName] FROM [Accounts] WHERE [AccountTypeId] = 5


The actual linked Entity does not contain 1111 1111- (which is supposed to be a phone number).

In any case, it looks like constraint by E.Balance is “broken”. For example, the followin works, so we know this is proper syntax:

[Account Balances:1, 1]
@{REPORT SQL DETAILS:SELECT [Name] AS [AccountName] FROM [Accounts] WHERE [AccountTypeId] = 5:F.AccountName::{0}:,}
{REPORT ENTITY DETAILS:E.Name,E.Balance:(EN=$1) && E.Name=="VIP1":{0}|{1}}

But this does not work - in fact, as soon a E.Balance is used at all, the report is blank:

[Account Balances:1, 1]
@{REPORT SQL DETAILS:SELECT [Name] AS [AccountName] FROM [Accounts] WHERE [AccountTypeId] = 5:F.AccountName::{0}:,}
{REPORT ENTITY DETAILS:E.Name,E.Balance:(EN=$1) && E.Balance!=0:{0}|{1}}

Also tried:

[Account Balances:1, 1, 1]
@{REPORT SQL DETAILS:SELECT [Name] AS [AccountName] FROM [Accounts] WHERE [AccountTypeId] = 5:F.AccountName::{0}:,}
{REPORT ENTITY DETAILS:E.Name,E.Balance;0.00,[($2)]:(EN=$1) && "$3"!="0":{0}|{1}|{2}}

1 Like

Entity name and account are the same as took out phone number for account name as a dont need phone number in samba and entity names have to be unique anyway so number isnt needed for uniqueness.

@emre is this something that can be easily fixed?

Well I hope not this is Version 5.1.60 syntax in use and working:

[Customer Balances:1,2,3, 1]
{REPORT ENTITY DETAILS:E.Name,EC.Full Name,EC.Address,E.Balance.Sum;0.00;-0.00;0.00:(ET=Customers) && E.AccountId>0:{0}|{1}|{2}|${3}::$4!=0:}
>> TOTAL||| {REPORT ENTITY DETAILS:E.Balance.Sum;0.00;-0.00;0.00:(ET=Customers) && E.AccountId>0:${0}::$1!=0:}

@JTRTech what version are you using?

But @pauln, you are not using E.Balance in the constraint. It displays Balance no problem, but when used in the Constraint/Filter section, it does not work… and maybe there is an underlying reason for that, I don’t know… @emre?

Using latest .61 beta refresh.

1 Like

Here? ::$4!=0:} My report definitely filters out '0' balances…
PS: Changes to $1 as only using 2 columns in the TOTAL.


Hmmmmm, adding .Sum seems to make it work…

[Account Balances:2, 1, 1]
@{REPORT SQL DETAILS:SELECT [Name] AS [AccountName] FROM [Accounts] WHERE [AccountTypeId] = 5:F.AccountName::{0}:,}
{REPORT ENTITY DETAILS:E.Name,E.Balance.Sum;0.00:(EN=$1):{0}|{1}::$2!=0}

Without .Sum it doesnt render the table at all…

Hmmm, next challenge then is a workaround for a total…


Reports double because of linked account…

{ACCOUNT TOTAL:Customer Accounts}

Only reports the difference (also will be good for calculating an opening balance by misusing it from the total balance but need to get that first…


{REPORT ENTITY DETAILS:E.Name,E.Balance.Sum;0.00:(EN=$1):{0}|{1}::$2!=0}
                                                 ^^^^^^^          ^^^^^
                                       constraint/filter          what is this section?

That is more mysterious than the background for the Pyramids in Egypt! I have many times tried to work that out but gave up when I got lucky with a hit!

I assume the .Sum infers Emre had to create a variable (place marker) to do the sum and that’s why we can reference it.