ACCOUNT TRANSACTION DETAILS:X can it be done on "Per USER" basis?

[plume]

Good morning,

I am making a custon report based on “per waiter” on the current (not ended) work period.
Indeed I have various waiters whose are also “cashiers” working during the work period (the whole working day).

The “waiter” will return his own movements which are the sales of course but also all the CASH TRANSACTIONS he had done during the day, like to give money to buy milk, getting back the change, …
When he is logged in, the waiter is the “SETTING:CURRENTUSER”, so he could see his own sales/transactions report, print it and makes the closing of his “shift”.

For the sales, no problems, using SETTING:CURRENTUSER with REPORT PAYMENT DETAILS will do:

@ {SETTING:CURRENTUSER}
[Venta $1:1, 1, 1]
{REPORT PAYMENT DETAILS:P.Name,P.Amount.Percent,P.Amount.Sum:(PU={SETTING:CURRENTUSER})}
'>Total Income|{REPORT PAYMENT DETAILS:P.Amount.Sum:(PU={SETTING:CURRENTUSER})}

For the cash transaction (Customer pays account, Waiter receives money, Waiter buys milk), I’d like to use ACCOUNT TRANSACTION DETAILS:X, but cannot figure out how to make it based on CURRENTUSER.

Marc

[mjb2000]

I don’t know ACCOUNT TRANSACTION DETAILS very well, bt I imagine the problem is that the waiter/current user is not something that is accessable from that report.

Take a read of @QMcKay’s answer to one of my questions a while ago. You can use the result of one report to feed in to another report. So you might do something like retrieve a list of ticket numbers per waiter from REPORT PAYMENT DETAILS and then feed these numbers in to the ACCOUNT TRANSACTION DETAILS report.

Q explains it better than me, so go and have a read, it should give you some hints…

Use ticket lister, or entity grid to display some customers

Ok enough about Tasks. You mention trying to use an Entity Grid or a Ticket Lister, but your filtering options with those Widgets can be limited. So let me draw you back to something I mentioned in my previous post: Reports in Table mode. Using a Report to supply parameters to another Report Reports can be filtered in many ways as well, and it depends on the Report Type you are using such as Ticket, Order, Entity, or Transaction Reports. You can also combine certain Reports and use @{REPORT:…

[plume]

Very interesting.

I think indeed Report in Table Mode can help.

Is there a place where I can find the “Table/Report/Transaction/Ticket/Entity” Structure in order to be able to know what are actually the possible “Fields to return”.

In the post you suggest I can see “Fields to reutrn” in:

– Find customers who have non zero balances
– :Fields to reutrn
– | :Conditions (select the entity type I want to look for)

But how do you know the name of these fields.
More specifically for transaction report.

Thanks,

Marc

[plume]

I found an information by @emre:
https://forum.sambapos.com/t/syntax-to-know-which-user-handled-the-accounts-transaction-documnet-custom-report-tag/4188/2?u=plume

It is from feb 2015, v4.

Is it still the case?

Thanks

[mjb2000]

This post will help, but I have found a couple of times that undocumented fields are also available…

Custom Report Tags in detail

We can prepare any type of reports with SQL and display results as a Custom Report. Alternatively we can use Custom Report Tags to prepare reports. If you need more information about preparing a custom report you can review this tutorial. ###Introduction Basically there are four types of tags. DETAILS TAGS: These tags contains DETAILS keyword in it and allows us to prepare table reports. TOTAL TAGS: If a tag contains TOTAL keyword in it we can use this tag read sum of a value. COUNT TAGS C…

[QMcKay]

@plume

Is it still the case?

The User is not stored with Transaction data.

Your only option here is to create a custom Transaction flow using the Create Account Transaction Document Action, and modify the Name parameter in that Action to contain a reference to {:CURRENTUSER}.

Using the Default Account Transaction Document Templates (buttons) will not allow you to do this. You still need the Document Type configured, but leave the Header blank so the button does not appear on-screen on the Accounts screen.

• Define an Automation Command and map it to the Account Screen.
• Have a Rule to capture the click of that button.
• Put an action for Update Program Setting containing a [?prompt] to get the amount and store it.
• Then fire the Create Account Transaction Document Action where you use the value from the Program Setting as the Amount, and {:CURRENTUSER} as value for the Description.

For Reference, I have a Doc Type and Tx Type that look like this:

---

##Automation Command

---

##Account Screen (Expenses)

When that ^ button is clicked on the Account Screen, it will send the selected Account Id as the [:CommandValue].

---

##Rule for button

That Rule calls on this script:

function getAccountNameById(accountid) {
  var qry = "SELECT [Name] FROM [Accounts] WHERE [Id]="+accountid;
  var r = sql.Query(qry).First;
  return r;
}

---

##Rule for Amount Prompt and Source Account

Then it fires this Rule, where we prompt for the amount, a description, and asks which source Account to use for the payment:

---

##Rule for Create Account Transaction Document

Then it fires the following Rule, which fires the Create Account Transaction Document Action. The field outlined in RED is where you could append the User:

[:CommandValue] {SETTING:ATx AccountType} Tx {SETTING:ATx Currency} ({SETTING:ATx Desc}) {:CURRENTUSER}

[QMcKay]

Here is how it works:

---

Then you have a Report:

[Account Transactions:1,1,1,1,1, 1]
>>docType|SRC|TGT|docName|txName|amount
{REPORT ACCOUNT TRANSACTION DETAILS:T.DocumentType,T.Source,T.Target,T.Name,T.TransactionName,T.Amount}

[plume]

Well @QMcKay that’s quite a lot of information to digest.
Thank you very much (and how can you post such a long answer in so little time !)

Let me a couple of days to try it,

Thanks

Marc

[QMcKay]

@plume

how can you post such a long answer in so little time

I already use that setup for paying Expenses, so it was ready to go. All I did was add {:CURRENTUSER} to the Description parameter.

I still have not figured out how to filter the Report for the User though. The Report field T.TransactionName contains the User Name, but I don’t know how to constrain it for a “Contains” condition.

[mjb2000]

@plume

how can you post such a long answer in so little time

And because he’s awesome, that’s why.

@QMcKay has help me soooo much with my setup. I should be going live soon and I can’t wait

[QMcKay]

@QMcKay

I still have not figured out how to filter the Report for the User though.

Ok, if I modify the Description to append USER~{:CURRENTUSER}, like this:

[:CommandValue] {SETTING:ATx AccountType} Tx {SETTING:ATx Currency} ({SETTING:ATx Desc}) USER~{:CURRENTUSER}

Then I can run this SQL to filter the User

DECLARE @user varchar(50) = 'Q'

SELECT
[txName]
,sum([Exchange]) as [Amount]
,max([AccountSRC]) as [AccountSRC]
,max([AccountTGT]) as [AccountTGT]
,[txDate]
FROM (

SELECT
tv.[Name] as [txName]
--,[Debit]
--,[Credit]
,0 as [Exchange]
,null as [AccountSRC]
,a.[Name] as [AccountTGT]
,[Date] as [txDate]
FROM [AccountTransactionValues] tv
JOIN [Accounts] a on a.[Id]=tv.[AccountId]
JOIN [AccountTypes] atyp on atyp.[Id]=tv.[AccountTypeId]
JOIN [AccountTransactionTypes] attyp on attyp.[Id]=tv.[AccountTransactionTypeId]
JOIN [AccountTransactions] tx on tx.[Id]=tv.[AccountTransactionId]
where 1=1
AND tv.[Debit]!=0
--AND tv.[Credit]!=0

UNION

SELECT
tv.[Name] as [txName]
--,[Debit]
--,[Credit]
,[Exchange]*-1 as [Exchange]
,a.[Name] as [AccountSRC]
,null as [AccountTGT]
,[Date] as [txDate]
FROM [AccountTransactionValues] tv
JOIN [Accounts] a on a.[Id]=tv.[AccountId]
JOIN [AccountTypes] atyp on atyp.[Id]=tv.[AccountTypeId]
JOIN [AccountTransactionTypes] attyp on attyp.[Id]=tv.[AccountTransactionTypeId]
JOIN [AccountTransactions] tx on tx.[Id]=tv.[AccountTransactionId]
where 1=1
--AND tv.[Debit]!=0
AND tv.[Credit]!=0

) u

WHERE [txName] LIKE '%USER~'+@user+'%'

GROUP BY
[txName]
,[txDate]

Which produces this:

---

So I can change the Report to use {REPORT SQL DETAILS:X} …

[Account Transactions:1,1,1,1, 1]
@Q,
>>Date|Type|SRC|TGT|Amount
{REPORT SQL DETAILS:
SELECT
txName
,sum(Exchange) as Amount
,max(AccountSRC) as AccountSRC
,max(AccountTGT) as AccountTGT
,txDate
FROM (

SELECT
tv.[Name] as [txName]
--,[Debit]
--,[Credit]
,0 as [Exchange]
,null as [AccountSRC]
,a.[Name] as [AccountTGT]
,[Date] as [txDate]
FROM [AccountTransactionValues] tv
JOIN [Accounts] a on a.[Id]=tv.[AccountId]
JOIN [AccountTypes] atyp on atyp.[Id]=tv.[AccountTypeId]
JOIN [AccountTransactionTypes] attyp on attyp.[Id]=tv.[AccountTransactionTypeId]
JOIN [AccountTransactions] tx on tx.[Id]=tv.[AccountTransactionId]
where 1=1
AND tv.[Debit]!=0
--AND tv.[Credit]!=0

UNION

SELECT
tv.[Name] as [txName]
--,[Debit]
--,[Credit]
,[Exchange]*-1 as [Exchange]
,a.[Name] as [AccountSRC]
,null as [AccountTGT]
,[Date] as [txDate]
FROM [AccountTransactionValues] tv
JOIN [Accounts] a on a.[Id]=tv.[AccountId]
JOIN [AccountTypes] atyp on atyp.[Id]=tv.[AccountTypeId]
JOIN [AccountTransactionTypes] attyp on attyp.[Id]=tv.[AccountTransactionTypeId]
JOIN [AccountTransactions] tx on tx.[Id]=tv.[AccountTransactionId]
where 1=1
AND tv.[Credit]!=0

) u

WHERE [txName] LIKE '%USER~$1'+'%'

GROUP BY
[txName]
,[txDate]
:F.txDate,F.txName,F.AccountSRC,F.AccountTGT,F.Amount
}

So that ^ uses an @LIST for Users:

@Q,

And it replaces the $1 variable here:

WHERE [txName] LIKE '%USER~$1'+'%'

… to produce this:

[plume]

Ok.
I will probably do this but as I am not you … it will take some time.

Do you mind if I ask you if the (more basic) following is possible?

(I know that this is far from been perfect, I already found some bad points, like the waitress can delete her name, …)
Based on your idea, I put:

Cash Payment - {SETTING:CURRENTUSER} in the Description Template of the Customer Cash Document Type
Like this:

So now when my waitress Karina use the default Customer Cash button in the customer account to clear that account, the following transaction is created:

So now I have all the information I need in the transaction table
Customer account, Transaction Type, Waitress and amount.

Based on your post and on this post, the new version can now do:

[REPORT:1, 1]
{REPORT ACCOUNT TRANSACTION DETAILS:T.Target,T.Amount.Sum:(ATT=Payment Transaction)}

like in your post:

@QMcKay

[Account Transactions:1,1,1,1,1, 1]
>>docType|SRC|TGT|docName|txName|amount
{REPORT ACCOUNT TRANSACTION DETAILS:T.DocumentType,T.Source,T.Target,T.Name,T.Transact

My questions for reporting are:

1. What are the other filters (other than ATT) we can apply on REPORT ACCOUNT TRANSACTION DETAILS?
2. Can we do sth like REPORT ACCOUNT TRANSACTION DETAILS:T.Source,T.Target,…:(T.Source=$1)
3. And finally, sth like: REPORT ACCOUNT TRANSACTION DETAILS:T.Source,T.Name,…:(T.Name**%LIKE%**$1)

I don’t know if my questions make sence I am new with the reporting syntax.

Maybe a last one, I am in a place where internet is bad and I don’t imagine getting the GB of that Ms SQLServer tool, how to get the structure of the DB, Tables and so the names of the columns?

Marc

[QMcKay]

@plume

Based on your idea, I put:

Cash Payment - {SETTING:CURRENTUSER} in the Description Template of the Customer Cash Document Type

Wow. I had no idea that was possible. I did not think that would work. It saves a lot of trouble.

@plume

What are the other filters (other than ATT) we can apply on REPORT ACCOUNT TRANSACTION DETAILS?

Don’t know. I searched but could find no details on filtering. That’s why I went the SQL route.

@plume

Can we do sth like REPORT ACCOUNT TRANSACTION DETAILS:T.Source,T.Target,…:(T.Source=$1)

Probably that will work, but lose the (brackets) for that type of filter.[quote=“plume, post:12, topic:14830”]
And finally, sth like: REPORT ACCOUNT TRANSACTION DETAILS:T.Source,T.Name,…:(T.Name**%LIKE%**$1)
[/quote]

No, not that I know of. The LIKE keyword is not supported. We have a .Contains syntax instead.

@plume

how to get the structure of the DB, Tables and so the names of the columns?

You really should get SSMS. It will make your life much easier. There is a post somewhere containing a picture of the DB that I did… need to find it.

[QMcKay]

Here is the current Diagram for V5 (5.2.2) …

DBdiagram.zip (484.0 KB)

---

[plume]

Muchas Gracias @QMcKay !!
It will make things thousand times clearer !!!

(I have just created a new topic concerning the syntax of account transaction detail report, which is new in SambaPOS 5.1.62 Release to avoid deviation of this topic)

Marc

[plume]

Solved.

1. I add {SETTING:CURRENTUSER} in the Description Template of the Customer Cash Document Type

@plume

Cash Payment - {SETTING:CURRENTUSER} in the Description Template of the Customer Cash Document Type

2. I use @QMcKay SQL report with 2 light modifications:

• I replace Q by {SETTING:CURRENTUSER} in the first line of his SQL report:

@QMcKay

[Account Transactions:1,1,1,1, 1]
@Q,
>>Date|Type|SRC|TGT|Amount

becomes

@QMcKay

[Account Transactions:1,1,1,1, 1]
@{SETTING:CURRENTUSER},
>>Date|Type|SRC|TGT|Amount

• I scratch USER~ from the WHERE clause:

@QMcKay

WHERE [txName] LIKE ‘%USER~$1’+'%’

becomes

@QMcKay

WHERE [txName] LIKE ‘%$1’+'%’

I have now a “CheckIn/Out report” or a “shift” report per waiter:

• His sales
• His void
• His cash transaction details

Than you very much,

Marc