Account Statements with Template Layout

Sorry not following?

OK. I’ve finally finished my inventory stuff (I hope) now we can focus on this.

First of all @JTRTech thank you very much for detailed explanation of your need. Seems like Entity Print with Transaction Details report will solve your issue. I’ll create needed detail report to see if it works fine for that or not.

Report tags works to list in work period values but this report tag will list entire transactions for selected account.

1 Like

@emre looking forward for the statement which an print in A4 paper and send to clients …samba keep growing like tree

1 Like

Hey gsreddy… I’m taking samples as shown on the post I mentioned below. I’ll be glad to read if you have something specific not covered there…

@JTRTech It is not very clear what you mean with tags like {ENTITY NAME:Rooms}, {ENTITY DATA:Account} etc… What entities you have and which entity you’re planning to use to print that statement? Room or Customer entity? Can you post your entity custom fields here?

@gsreddy a new printer type ‘Document Printer’ was introduced which allows greater formatting for invoices.

Here is what I have done using this Printer Type. It would be great if this header formatting could be included In the Account statement. Report tags that can be included in printer template would be excellent but some way of filtering by date or even last month will be required. Entire transaction list over a couple of years could be 30 pages long.

Even if was possible to edit the account transaction template I think it would help
Here is a sample statement

Here is the statement builder on my accounts package. I know something like this is beyond the scope of this project. Its just to give you an idea of the data imput usually required


It depends on what your new statement report allows.
The booking on I did used room entities and booking entities.
Booking entities were used to have seperate account per booking.
The setup I made updated the room entity with the booking entity name in the account field in the hope that a statement could be produced using only the room entity as the booking entity/account name would be defined in the room custom field of Account.

The room entityies were the ones on the entity screen but creating a ticket with/selecting room entity also updated the ticket with the booking entity using the booking number set in the booking number custom field.

That will probably confuse the hell out of you LOL

Ideally the transaction details report tag would want to accept date rang expession and account name which can be fed using other tags or a script.

The big thing everyone seems to want is that we can format the template/layout to show business and customer info.

Had a thought today (I sure whenever I say that you all thing God what now!)
What if there was an additional reference on account transactions - a bit like description - that we can filter to?
The room accounts could be filtered to this using booking number as a reference/filter rather than date (although date would be good)
What this could also lead to is a solution for @pauln plan of sub accounts, customer account is overall but the ‘sub’ accounts were filtered using this reference?

Just an idea…

@silentbob that’s great looks what I need too , is it possible to share the setup thakns.

@emre have you had chance to sort a solution for customisable statement/account transactions tag?

He is working on something that will blow your mind. literally :stuck_out_tongue:


Serious or you messing with me :stuck_out_tongue:
Something on this topic or something else which will blow minds?

Im actually serious.

Come on, don’t toy with me, give us a hint at least… :smile:

I did :slight_smile: it’s being tested and worked on internally I’m sure he will release something soon.

Was verry subtle hint!!!
‘Blow your mind, literally!’ = self distructing samba? :-p

Oh no… literally broken grammar :wink:

1 Like

Was this released in .55?

Yes it was in .55 it’s the REPORT SQL DETAILS tag

This is one of the latest uses of that tag:

<J00>Customer:{ENTITY NAME}|Account:{ACCOUNT NAME}
<J00>Date| TID| TNo| Description| Amount
,tkt.[Id] as [TicketId]
,tkt.[TicketNumber] as [TicketNo]
  WHEN [Credit] > 0 Then 'Payment'
  WHEN [Debit] > 0 Then 'Purchase'
 END as [Desc]
,[Debit]-[Credit] as [Amount]
FROM [AccountTransactionValues] tv
LEFT JOIN [AccountTransactions] tx on tx.[Id] = tv.[AccountTransactionId]
LEFT JOIN [AccountTransactionDocuments] d on d.[Id] = tv.[AccountTransactionDocumentId]
LEFT JOIN [AccountTransactionTypes] tt on tt.[Id] = tv.[AccountTransactionTypeId]
-- Accounts
LEFT JOIN [AccountTypes] at on at.[Id] = tv.[AccountTypeId]
LEFT JOIN [Accounts] a on a.[Id] = tv.[AccountId]
-- Ticket
LEFT JOIN [Tickets] tkt on tkt.[TransactionDocument_Id] = d.[Id]
LEFT JOIN [TicketEntities] te on te.Ticket_Id = tkt.[Id]
-- Entity
LEFT JOIN [Entities] e on e.[Id] = te.[EntityId]
LEFT JOIN [EntityTypes] et on et.[Id] = e.[EntityTypeId]
AND tv.[AccountId] in (SELECT TOP 1 [AccountId] FROM [Entities] WHERE [Name] = '{ENTITY NAME}')
:F.Date.desc,F.TicketId,F.TicketNo,F.Desc,F.Amount::<J00>{0}| {1}| {2}| {3}| {4}
<J00>Balance:|{ENTITY BALANCE}

Now I use Automation > Scripts and put the query in @@GetStatement, but the idea is the same. You would use this in a Printer Template, as such:

This was my final result, using a Custom Entity Screen …

Show Statement and Print Statement are Automation Commands that have been added to the Customer Account Screen, with their parameters set to Entity.

When you click Show Statement, it loads a Custom Entity Screen containing details for the highlighted Account. This screen contains a Report Widget that uses the same SQL to generate the detail as you would see on a printed statement, but it also has some other features such as methods for paying the account or displaying the Ticket.

When you click Print Statement, it fires an Execute Print Job Print Entity Action that is set to use an Invoice Printer and the Account Statement Printer Template.