Not seen previous discussion similar but maybe @pauln you might be interested to make your accounts system even more complex
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ā¦
[Account Balances:2, 1]
@{REPORT SQL DETAILS:SELECT [Name] AS [AccountName] FROM [Accounts] WHERE [AccountTypeId] = 5:F.AccountName::{0}}
$1|{REPORT ENTITY DETAIL:E.Balance:(EN=$1)}
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)}
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}?
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.
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.
ie;
{REPORT ENTITY DETAILS:E.Name,E.Balance}
gives
Joe is set to use Joseph Trice-Rolph account (total customer accounts is 10 not 20.
This;
@{REPORT SQL DETAILS:SELECT [Name] AS [AccountName] FROM [Accounts] WHERE [AccountTypeId] = 5:F.AccountName::{0}:,}
{REPORT ENTITY DETAILS:E.Name,E.Balance:(EN=$1)}
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ā¦
Tried;
{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}
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.
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?
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.
