Work Period Adjustment for Including Customer Accounts as Debtor Accounts

V5 Tutorial
1

Just thought Id post a sample of a basic work period adjustment incorporating cusomter accounts;

[Ticket Payments:2, 1]
{REPORT PAYMENT DETAILS:P.Name,P.Amount.Sum:((PT=Cash)||(PT=Credit Card)||(PT=Voucher)) AND (Payment.Amount > 0)}
Account|{ACCOUNT TRANSACTION DEBIT TOTAL:Customer Account Transaction}
>Total|{REPORT PAYMENT DETAILS:P.Amount.Sum:Payment.Amount > 0}

[Account Payments:2, 1]
Cash|{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Cash Payment}
Credit Card|{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Credit Card Payment}
>Total|[={ACCOUNT TRANSACTION CREDIT TOTAL:Customer Cash Payment}+{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Credit Card Payment}]

[Payment Totals:2, 1]
Cash|{ACCOUNT TOTAL:Cash}
Credit Card|{ACCOUNT TOTAL:Credit Card}
>Total|[={ACCOUNT TOTAL:Cash}+{ACCOUNT TOTAL:Credit Card}]

Adapted Payment details tag to only show cash, card and voucher and then put account charges on separate combined line rather than listing each used account as a payment type.
This section should balance against sales total (including refunds if any)

Then added a separate table for account payments as this should represent the gap between ticket payments and total cash/card received.

Then have a total cash and card received table.

Initial tests show expected results so far


2 Likes
2

Wait, have what you detailed above work?

This is something which I need desperately, its clogging up my end of day printout.

Matt

3

Hey @JTRTech - why is this a request? You wish to change or add something?

1 Like
4

Oops, good shout, not request.

1 Like
5

awesome, been trying to achieve this for a while!

Thanks :slight_smile:

1 Like
6

Unfortunately since account payments are each named differently you cant even try to exclude accounts allowing all others dynamically.

Would be nice to have a Account Type to constrain against so we could say account type=payment accounts making report dynamic while excluding individual account payments.

7

Umm I could post my syntax here but it pretty “serious” and might just confuse many posters - heck I still try and understand my own work :smile:

In the immortal kendash style here is a hint for that one above @JTRTech:

&& Payment.Name.Contains("Eftpos")

I use that to separate payment types - but obviously you need a well planned naming convention before system build.

8

Yer, thats true, just had a thought though
 1 sec


9

Ok, so thinking something like this;

{REPORT SQL DETAILS: SELECT a.[Name] FROM [Accounts] a JOIN [AccountTypes] t ON a.[AccountTypeId] = t.[Id] WHERE t.[Name] = "Payment Accounts":F.AccountList::{0}:,}

But still working on it


10

If I can add (possibly you doing this already) but if you take away the “@” and just list the output i.e $1 THAT becomes the input for the Report tag - it helped me heaps.

{REPORT SQL DETAILS: x}
$1

Leave $1 outside of any [Report Syntax], right


11

Needed to specify an AS


[Ticket Payments:2, 1]
@{REPORT SQL DETAILS: SELECT a.[Name] AS [AccountList] FROM [Accounts] a LEFT JOIN [AccountTypes] t ON a.[AccountTypeId] = t.[Id] WHERE t.[Name]='Payment Accounts':F.AccountList::{0}:,}
$1|{REPORT PAYMENT DETAILS:P.Amount.Sum:(PT=$1) && (Payment.Amount > 0)}
Account|{ACCOUNT TRANSACTION DEBIT TOTAL:Customer Account Transaction}
>Total|{REPORT PAYMENT DETAILS:P.Amount.Sum:Payment.Amount > 0}

12

Ok, progress so far;

[Sales:1, 1]
{REPORT TICKET TYPES:!PreOrder && TotalAmount >= 0}

[Pre Orders:1, 1]
{REPORT TICKET TYPES:PreOrder && TotalAmount >= 0}

[Refunds:1, 1]
{REPORT TICKET TYPES:TotalAmount < 0}

[Ticket Payments:2, 1]
@{REPORT SQL DETAILS: SELECT a.[Name] AS [AccountList] FROM [Accounts] a LEFT JOIN [AccountTypes] t ON a.[AccountTypeId] = t.[Id] WHERE t.[Name]='Payment Accounts':F.AccountList::{0}:,}
{REPORT PAYMENT DETAILS:P.Name,P.Amount.Sum:(PT=$1) && (Payment.Amount > 0)}
Account|{ACCOUNT TRANSACTION DEBIT TOTAL:Customer Account Transaction}
>Total|{REPORT PAYMENT DETAILS:P.Amount.Sum:Payment.Amount > 0}

[Refund Payments:2, 1, 2]
{REPORT REFUND PAYMENTS}

[Account Payments:2, 1]
Cash|{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Cash Payment}
Credit Card|{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Credit Card Payment}
>Total|[={ACCOUNT TRANSACTION CREDIT TOTAL:Customer Cash Payment}+{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Credit Card Payment}]

[Income Totals:2, 1]
Cash|{ACCOUNT TOTAL:Cash}
Credit Card|{ACCOUNT TOTAL:Credit Card}
>Total|[={ACCOUNT TOTAL:Cash}+{ACCOUNT TOTAL:Credit Card}]

[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}
>Total|{REPORT ENTITY DETAILS:E.Balance.Sum}
{ACCOUNT TOTAL:Customer Accounts}

If can sort balance for customer accounts can add BF and CF lines of account balances and should be good to go I think


13

Hey @QMcKay as SQL pro, does this look like it works?
Seems to give the value I expect after few tests;

SELECT SUM([Exchange]) FROM [AccountTransactionValues] WHERE [AccountTypeId] = 5

This is to get the current balance of Customer Accounts.
Although guess would need to add date constraints if it was expected to be backdateable on reports.

1 Like
14

Getting there, but could definatly do with a cleaner solution;

[Account Balances:2, 1, 1]
@{REPORT SQL DETAILS:SELECT [Name] AS [AccountName] FROM [Accounts] WHERE [AccountTypeId] = 5:F.AccountName::{0}:,}
>Balance BF|[={REPORT SQL DETAILS: SELECT SUM([Exchange]) AS [Balance] FROM [AccountTransactionValues] WHERE [AccountTypeId] = 5:F.Balance::{0}}-{ACCOUNT TOTAL:Customer Accounts}]
{REPORT ENTITY DETAILS:E.Name,E.Balance.Sum;0.00:(EN=$1):{0}|{1}::$2!=0}
>Balance CF|{REPORT SQL DETAILS: SELECT SUM([Exchange]) AS [Balance] FROM [AccountTransactionValues] WHERE [AccountTypeId] = 5:F.Balance::{0}}

15

Debtor Movement;

[Debtor Movement:2, 1]
Balance BF|[={REPORT SQL DETAILS: SELECT SUM([Exchange]) AS [Balance] FROM [AccountTransactionValues] WHERE [AccountTypeId] = 5:F.Balance::{0}}-({ACCOUNT TOTAL:Customer Accounts})]
Debtor Charges|{ACCOUNT TRANSACTION DEBIT TOTAL:Customer Account Transaction}
Debtor Payments|-[={ACCOUNT TRANSACTION CREDIT TOTAL:Customer Cash Payment}+{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Credit Card Payment}]
Balance CF|{REPORT SQL DETAILS: SELECT SUM([Exchange]) AS [Balance] FROM [AccountTransactionValues] WHERE [AccountTypeId] = 5:F.Balance::{0}}

16

Leaving it at that for tonight;

[Sales:1, 1]
{REPORT TICKET TYPES:!PreOrder && TotalAmount >= 0}

[Pre Orders:1, 1]
{REPORT TICKET TYPES:PreOrder && TotalAmount >= 0}

[Refunds:1, 1]
{REPORT TICKET TYPES:TotalAmount < 0}

[Ticket Payments:2, 1]
@{REPORT SQL DETAILS: SELECT a.[Name] AS [AccountList] FROM [Accounts] a LEFT JOIN [AccountTypes] t ON a.[AccountTypeId] = t.[Id] WHERE t.[Name]='Payment Accounts':F.AccountList::{0}:,}
{REPORT PAYMENT DETAILS:P.Name,P.Amount.Sum:(PT=$1) && (Payment.Amount > 0)}
Account|{ACCOUNT TRANSACTION DEBIT TOTAL:Customer Account Transaction}
>Total|{REPORT PAYMENT DETAILS:P.Amount.Sum:Payment.Amount > 0}

[Refund Payments:2, 1, 2]
{REPORT REFUND PAYMENTS}

[Account Payments:2, 1]
Cash|{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Cash Payment}
Credit Card|{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Credit Card Payment}
>Total|[={ACCOUNT TRANSACTION CREDIT TOTAL:Customer Cash Payment}+{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Credit Card Payment}]

[Income Totals:2, 1]
Cash|{ACCOUNT TOTAL:Cash}
Credit Card|{ACCOUNT TOTAL:Credit Card}
>Total|[={ACCOUNT TOTAL:Cash}+{ACCOUNT TOTAL:Credit Card}]

[Debtor Movement:2, 1]
Balance BF|[={REPORT SQL DETAILS: SELECT SUM([Exchange]) AS [Balance] FROM [AccountTransactionValues] WHERE [AccountTypeId] = 5:F.Balance::{0}}-({ACCOUNT TOTAL:Customer Accounts})]
Debtor Charges|{ACCOUNT TRANSACTION DEBIT TOTAL:Customer Account Transaction}
Debtor Payments|-[={ACCOUNT TRANSACTION CREDIT TOTAL:Customer Cash Payment}+{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Credit Card Payment}]
Balance CF|{REPORT SQL DETAILS: SELECT SUM([Exchange]) AS [Balance] FROM [AccountTransactionValues] WHERE [AccountTypeId] = 5:F.Balance::{0}}

[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}
>Total|{REPORT SQL DETAILS: SELECT SUM([Exchange]) AS [Balance] FROM [AccountTransactionValues] WHERE [AccountTypeId] = 5:F.Balance::{0}}

2 Likes
17

Little moment summary;

[Movement Summary:2, 1]
Debtors BF|[={REPORT SQL DETAILS: SELECT SUM([Exchange]) AS [Balance] FROM [AccountTransactionValues] WHERE [AccountTypeId] = 5:F.Balance::{0}}-({ACCOUNT TOTAL:Customer Accounts})]
+ Sales|{REPORT TICKET TOTAL}
- Payments|[={ACCOUNT TOTAL:Cash}+{ACCOUNT TOTAL:Credit Card}]
Debtors CF|{REPORT SQL DETAILS: SELECT SUM([Exchange]) AS [Balance] FROM [AccountTransactionValues] WHERE [AccountTypeId] = 5:F.Balance::{0}}

It is all a little specific, something more generic would be good but does the job for now I think.

I still believe the default work period should have some form of debtor section or if not a separate report for it.
Dont think work period report should treat individual account payments for tickets like it does hence the adaption made above.
It should also incorporate account payments.
I understand the reasoning behind this as there is nothing distinguishing sales and payment accounts from debtor accounts from a data standpoint other than the report itself and transaction configurations handling the relationships.
To resolve this I think you would need to have some other form of higher identification for account type - say class or an option to specify debtor style account types which triggers a BF/CF style reporting.

18

Had a though on this topic about account refund
 Given there is no transaction type for refund (sale or payment) I am guessing a refund is a minus value credit or debit depending on account(still not got the two completely straight in my head) rather than a reverse transaction?
@Jesse you good with accounts, can you confirm that logic?
I ask as using account credit and debit report expressions and just realised I hadn’t checked how refund effects the report


19

Hello, why only two digits appear in total?
Captura

20

Hello I need to make a custom report where the clients who have paid their bills appear, the name of customer, also I need to show what kind of payment the client made, that is, cash or card.