# Account Statements with Template Layout **URL:** \ **Category:** V5 Question\ **Created:** [November 9, 2015, 11:37am UTC](https://forum.sambapos.com/t/account-statements-with-template-layout/8029 "2015-11-09T11:37:14Z")\ **Posts on this page:** 20\ **Page:** 3
**Author:** ![emre](https://forum.sambapos.com/user_avatar/forum.sambapos.com/emre/32/39073_2.png) [@emre](https://forum.sambapos.com/u/emre)\ **Post date:** [November 11, 2015, 2:13am UTC](https://forum.sambapos.com/t/account-statements-with-template-layout/8029/41 "2015-11-11T02:13:30Z")
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. ---
**Author:** ![gsreddy](https://forum.sambapos.com/letter_avatar_proxy/v4/letter/g/e9c0ed/32.png) [@gsreddy](https://forum.sambapos.com/u/gsreddy)\ **Post date:** [November 11, 2015, 2:33am UTC](https://forum.sambapos.com/t/account-statements-with-template-layout/8029/42 "2015-11-11T02:33:37Z")
@emre looking forward for the statement which an print in A4 paper and send to clients …samba keep growing like tree ---
**Author:** ![emre](https://forum.sambapos.com/user_avatar/forum.sambapos.com/emre/32/39073_2.png) [@emre](https://forum.sambapos.com/u/emre)\ **Post date:** [November 11, 2015, 2:37am UTC](https://forum.sambapos.com/t/account-statements-with-template-layout/8029/43 "2015-11-11T02:37:18Z")
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… > [@Account Statements with Template Layout](http://forum.sambapos.com/t/account-statements-with-template-layout/8029/12): > > Transaction list would show payments also, so if not paid in full. Its a statement of account, thats it. Example of what I mean; 06/11/15 12:03 Ticket 428 £ 54.20 07/11/15 14:12 Ticket 497 £ 24.50 07/11/15 16:01 Ticket 502 £ 32.80 07/11/15 16:01 Payment Cash -£ 111.50 08/11/15 18:46 Ticket 521 £ 54.20 So a statement/bill would look something like this; Although… ---
**Author:** ![emre](https://forum.sambapos.com/user_avatar/forum.sambapos.com/emre/32/39073_2.png) [@emre](https://forum.sambapos.com/u/emre)\ **Post date:** [November 11, 2015, 11:49am UTC](https://forum.sambapos.com/t/account-statements-with-template-layout/8029/45 "2015-11-11T11:49:52Z")
@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? ---
**Author:** ![silentbob](https://forum.sambapos.com/letter_avatar_proxy/v4/letter/s/3ab097/32.png) [@silentbob](https://forum.sambapos.com/u/silentbob)\ **Post date:** [November 11, 2015, 12:08pm UTC](https://forum.sambapos.com/t/account-statements-with-template-layout/8029/46 "2015-11-11T12:08:09Z")
@gsreddy a new printer type ‘Document Printer’ was introduced which allows greater formatting for invoices. > [@SambaPOS 5.1.54 Release](http://forum.sambapos.com/t/sambapos-5-1-54-release/7816): > > Download Link. arrow_down [http://sambapos.com/?wpfb\_dl=135](http://sambapos.com/?wpfb_dl=135) Here are new features that comes with 5.1.54 release. ###Displaying duration on table buttons {ENTITY STATE MINUTES} tag implemented for displaying time passed on table buttons. Here is a sample display format that used New Orders table state. bulb New Orders state enables when we create a new ticket for a table and it changes table color to orange. $1\
\${REPORT TICKET DETAILS:T.RemainingAmount:(TEN.Table=$… 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. ![](https://forum.sambapos.com/uploads/default/original/3X/e/7/e74b4fe1952dee9839e0dd467a73f8794a30411f.jpg) Even if was possible to edit the account transaction template I think it would help Here is a sample statement ![](https://forum.sambapos.com/uploads/default/original/3X/e/a/eaa21c83d1edb2e846987cfe4a24c8b51eef816f.jpg) 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 ![](https://forum.sambapos.com/uploads/default/original/3X/a/0/a017faacf00202a31a9028cf70ebb9619cb2e97d.PNG) ---
**Author:** ![JTRTech](https://forum.sambapos.com/user_avatar/forum.sambapos.com/jtrtech/32/21270_2.png) [@JTRTech](https://forum.sambapos.com/u/JTRTech)\ **Post date:** [November 11, 2015, 12:14pm UTC](https://forum.sambapos.com/t/account-statements-with-template-layout/8029/47 "2015-11-11T12:14:29Z")
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. ---
**Author:** ![JTRTech](https://forum.sambapos.com/user_avatar/forum.sambapos.com/jtrtech/32/21270_2.png) [@JTRTech](https://forum.sambapos.com/u/JTRTech)\ **Post date:** [November 11, 2015, 2:58pm UTC](https://forum.sambapos.com/t/account-statements-with-template-layout/8029/48 "2015-11-11T14:58:49Z")
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… ---
**Author:** ![gsreddy](https://forum.sambapos.com/letter_avatar_proxy/v4/letter/g/e9c0ed/32.png) [@gsreddy](https://forum.sambapos.com/u/gsreddy)\ **Post date:** [November 12, 2015, 3:39am UTC](https://forum.sambapos.com/t/account-statements-with-template-layout/8029/49 "2015-11-12T03:39:06Z")
@silentbob that’s great looks what I need too , is it possible to share the setup thakns. ---
**Author:** ![JTRTech](https://forum.sambapos.com/user_avatar/forum.sambapos.com/jtrtech/32/21270_2.png) [@JTRTech](https://forum.sambapos.com/u/JTRTech)\ **Post date:** [November 14, 2015, 2:30pm UTC](https://forum.sambapos.com/t/account-statements-with-template-layout/8029/50 "2015-11-14T14:30:01Z")
@emre have you had chance to sort a solution for customisable statement/account transactions tag? ---
**Author:** ![Jesse](https://forum.sambapos.com/user_avatar/forum.sambapos.com/jesse/32/61493_2.png) [@Jesse](https://forum.sambapos.com/u/Jesse)\ **Post date:** [November 14, 2015, 2:30pm UTC](https://forum.sambapos.com/t/account-statements-with-template-layout/8029/51 "2015-11-14T14:30:16Z")
He is working on something that will blow your mind. literally 😛 ---
**Author:** ![JTRTech](https://forum.sambapos.com/user_avatar/forum.sambapos.com/jtrtech/32/21270_2.png) [@JTRTech](https://forum.sambapos.com/u/JTRTech)\ **Post date:** [November 14, 2015, 2:31pm UTC](https://forum.sambapos.com/t/account-statements-with-template-layout/8029/52 "2015-11-14T14:31:12Z")
> [@Jesse](#): > > He is working on something that will blow your mind. literally 😛 Serious or you messing with me 😛 Something on this topic or something else which will blow minds? ---
**Author:** ![Jesse](https://forum.sambapos.com/user_avatar/forum.sambapos.com/jesse/32/61493_2.png) [@Jesse](https://forum.sambapos.com/u/Jesse)\ **Post date:** [November 14, 2015, 2:51pm UTC](https://forum.sambapos.com/t/account-statements-with-template-layout/8029/53 "2015-11-14T14:51:12Z")
Im actually serious. ---
**Author:** ![JTRTech](https://forum.sambapos.com/user_avatar/forum.sambapos.com/jtrtech/32/21270_2.png) [@JTRTech](https://forum.sambapos.com/u/JTRTech)\ **Post date:** [November 14, 2015, 9:03pm UTC](https://forum.sambapos.com/t/account-statements-with-template-layout/8029/54 "2015-11-14T21:03:01Z")
Come on, don’t toy with me, give us a hint at least… 😄 ---
**Author:** ![Jesse](https://forum.sambapos.com/user_avatar/forum.sambapos.com/jesse/32/61493_2.png) [@Jesse](https://forum.sambapos.com/u/Jesse)\ **Post date:** [November 14, 2015, 9:04pm UTC](https://forum.sambapos.com/t/account-statements-with-template-layout/8029/55 "2015-11-14T21:04:05Z")
I did 🙂 it’s being tested and worked on internally I’m sure he will release something soon. ---
**Author:** ![JTRTech](https://forum.sambapos.com/user_avatar/forum.sambapos.com/jtrtech/32/21270_2.png) [@JTRTech](https://forum.sambapos.com/u/JTRTech)\ **Post date:** [November 14, 2015, 9:05pm UTC](https://forum.sambapos.com/t/account-statements-with-template-layout/8029/56 "2015-11-14T21:05:37Z")
> [@Jesse](#): > > I did 😄 Was verry subtle hint!!! ‘Blow your mind, literally!’ = self distructing samba? :-p ---
**Author:** ![emre](https://forum.sambapos.com/user_avatar/forum.sambapos.com/emre/32/39073_2.png) [@emre](https://forum.sambapos.com/u/emre)\ **Post date:** [November 14, 2015, 10:02pm UTC](https://forum.sambapos.com/t/account-statements-with-template-layout/8029/57 "2015-11-14T22:02:33Z")
> [@JTRTech](#): > > ‘Blow your mind, literally!’ = self distructing samba? :-p Oh no… literally broken grammar 😉 ---
**Author:** ![silentbob](https://forum.sambapos.com/letter_avatar_proxy/v4/letter/s/3ab097/32.png) [@silentbob](https://forum.sambapos.com/u/silentbob)\ **Post date:** [November 26, 2015, 10:19am UTC](https://forum.sambapos.com/t/account-statements-with-template-layout/8029/58 "2015-11-26T10:19:10Z")
> [@Jesse](#): > > I did 😄 it’s being tested and worked on internally I’m sure he will release something soon. Was this released in .55? ---
**Author:** ![Jesse](https://forum.sambapos.com/user_avatar/forum.sambapos.com/jesse/32/61493_2.png) [@Jesse](https://forum.sambapos.com/u/Jesse)\ **Post date:** [November 26, 2015, 12:46pm UTC](https://forum.sambapos.com/t/account-statements-with-template-layout/8029/59 "2015-11-26T12:46:55Z")
Yes it was in .55 it’s the REPORT SQL DETAILS tag ---
**Author:** ![QMcKay](https://forum.sambapos.com/user_avatar/forum.sambapos.com/qmckay/32/27925_2.png) [@QMcKay](https://forum.sambapos.com/u/QMcKay)\ **Post date:** [November 26, 2015, 1:40pm UTC](https://forum.sambapos.com/t/account-statements-with-template-layout/8029/60 "2015-11-26T13:40:01Z")
This is one of the latest uses of that tag: ```auto [ENTITY] ACCOUNT STATEMENT Customer:{ENTITY NAME}|Account:{ACCOUNT NAME} = Date| TID| TNo| Description| Amount - {REPORT SQL DETAILS: SELECT tv.[Date] ,tkt.[Id] as [TicketId] ,tkt.[TicketNumber] as [TicketNo] ,CASE 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] WHERE 1=1 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::{0}| {1}| {2}| {3}| {4} } = 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: ![](https://forum.sambapos.com/uploads/default/original/3X/0/2/025cc454124c181c4b8cbc520bbadeed7c7685ad.png) * * * This was my final result, using a Custom Entity Screen … ![](https://forum.sambapos.com/uploads/default/original/3X/8/f/8fc037cc64c8ccada5b3d67a9358be8d00a85935.png) * * * **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. ---
**Author:** ![silentbob](https://forum.sambapos.com/letter_avatar_proxy/v4/letter/s/3ab097/32.png) [@silentbob](https://forum.sambapos.com/u/silentbob)\ **Post date:** [November 26, 2015, 2:16pm UTC](https://forum.sambapos.com/t/account-statements-with-template-layout/8029/61 "2015-11-26T14:16:10Z")
Thanks that is exactly what I am looking for. Any plans for a tutorial or even a quick guide to give me an idea what exactly you are doing to achieve this? [Previous page](https://forum.sambapos.com/t/account-statements-with-template-layout/8029.md?page=2) [Next page](https://forum.sambapos.com/t/account-statements-with-template-layout/8029.md?page=4)