In this screen account details, is possible add a automation command/button?
Not sure if it was from the account screen, his tutorial might have been via a custom entity screen within POS but achives a custom account statement for an entity.
Anyway automation commands are dded to an account screen in the account screen options tab;
2 Likes
Wow… Many thanks 
How to print in template in this way?
Not debit or credit, but whith date and balance and the total…
Look here …
2 Likes
Hello . I finished the tutorial, but I have a problem … I need to also print Custom Field Name. I can not. What am I doing wrong?
Try:
{ENTITY DATA:Intestazione}
And remove this part:
[LAYOUT]
{ENTITIES}
No is the same.
I try this:
EDIT:blank with this
Show the Template for that Report.
Your Report Template has no Header, like:
[ReportName:1,1,1,1,1,1,1]
And how can we know what @@XTicketListByCustomer is supposed to do?
Even guessing, you are not feeding a $parameter into the SQL via the Report to specify the “Clienti” name, which by assumption would be required by the SQL so it knows which Customer to pull data for.
ok, I made the tutorial on a new database and the problem was mine, because of the cliente = Customer. However it works,
I Need to Show Ticket Tag for anyone ticket: Ricevuta Fiscale No:1981. But in template {TICKET TAG:Ricevute N°} dont show one for each other but only the last. Can I find it in Sql? Like @TicketDate
Getting Ticket Tags or Order Tags via SQL is not straight-forward. It is easier to use Reports Tags for that, for example:
{REPORT TICKET DETAILS:TT.Ricevuta Fiscale No}
{REPORT TICKET DETAILS:TT.Invoice No}
So you can try that ^ after the closing } brace for {REPORT SQL DETAILS:X}
1 Like
works ok, but so the print all. 1986-1988-1989–> > etc but only 1984 and 1985 are of that account…
I have an idea.
I changed GetStatement Script as follows. What I basically did is including Ticket Tags.
declare @entityType varchar(255) = 'Customers'
declare @entityId int = 0
declare @dateFilterBeg datetime = GETDATE()
declare @entName varchar(255) = '@1'
declare @entId int = 0
SET @dateFilterBeg = (SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='AS Date Filter Beg')
SET @entId = (SELECT isnull([Id],0) FROM [Entities] WHERE [Name]=@entName)
IF @dateFilterBeg = ''
BEGIN
SET @dateFilterBeg = '2000-01-01'
END
--SET @dateFilterBeg = @dateFilterBeg + 'T00:00:00.000'
--declare @entityName varchar(255) = ''
--declare @accountId int = 0
--declare @accountName varchar(255) = ''
SET @entityId = (SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='AS Entity Id')
IF @entId > 0 SET @entityId = @entId
--SET @entityName = (SELECT [Name] FROM [Entities] WHERE [Id]=@entityId)
--SET @accountId = (SELECT [AccountId] FROM [Entities] WHERE [Id]=@entityId)
--SET @accountName = (SELECT [Name] FROM [Accounts] WHERE [Id]=@accountId)
--UPDATE [ProgramSettingValues] SET [Value]=@entityName WHERE [Name]='AS Entity Name'
--UPDATE [ProgramSettingValues] SET [Value]=@accountId WHERE [Name]='AS Account Id'
--UPDATE [ProgramSettingValues] SET [Value]=@accountName WHERE [Name]='AS Account Name'
declare @txcount int = 0
declare @i int = 1
declare @balance decimal(7,2) = 0.00
declare @tbl_tx table (
[Id] INT IDENTITY(1,1) NOT NULL
, [TxDate] Datetime null
, [TicketId] int null
, [TicketNo] int null
, [TicketTags] varchar(max) null
, [Description] varchar(255) null
, [Amount] decimal(6,2) null
, [Balance] decimal(7,2) null
)
INSERT INTO @tbl_tx ([TxDate], [TicketId], [TicketNo], [TicketTags], [Description], [Amount], [Balance])
--SELECT TOP 1 @entityId as [Date], @entityName as [TicketId], ' ' as [TicketNo], @accountId as [Desc], @accountName as [Amount] FROM [Entities]
--UNION
SELECT
tv.[Date] as [Date]
,tkt.[Id] as [TicketId]
,CASE
WHEN tkt.[TicketNumber]>0 Then tkt.[TicketNumber]
ELSE d.[Id]
END as [TicketNo]
,tkt.[TicketTags]
,CASE
WHEN [Credit] > 0 Then 'Payment [' + d.[Name] + '] ' + tx.[Name]
WHEN [Debit] > 0 Then 'Purchase'
END as [Desc]
,[Debit]-[Credit] as [Amount]
,0 as [Balance]
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] and te.[EntityTypeId] IN (SELECT [Id] FROM [EntityTypes] WHERE [Name]=@entityType)
-- 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] = @entityName)
AND tv.[AccountId] in (SELECT TOP 1 [AccountId] FROM [Entities] WHERE [Id] = @entityId)
AND tv.[Date] >= @dateFilterBeg
ORDER BY [Date] ASC
SELECT @txcount = count([Id]) FROM @tbl_tx
WHILE @i<=@txcount
BEGIN
SET @balance = @balance + (SELECT [Amount] FROM @tbl_tx WHERE [Id]=@i)
UPDATE @tbl_tx SET [Balance] = @balance WHERE [Id]=@i
IF @balance = 0 UPDATE [ProgramSettingValues] SET [Value]=(SELECT [TxDate] FROM @tbl_tx WHERE [Id]=@i) WHERE [Name]='AS Entity Date_ZeroBalance'
SET @i = @i + 1
END
select [TxDate], [TicketId], [TicketNo], [TicketTags], [Description], [Amount], [Balance] from @tbl_tx ORDER BY [Id] DESC
.and I used internal JSON parser to parse the needed Ticket Tag value.
[LAYOUT]
<L00>Statement TEST
{REPORT SQL DETAILS:@@GetStatement;John Navy:
F.TxDate.desc,F.TicketTags(TN=Payment Info)TV::
<J00>{0}|{1}}
I highlighted the parser part with yellow color. You can replace Payment Info part with your desired tag name. I also hardcoded entity name (John Navy) for testing but you can use related printing tag to parse the Entity Name.
@QMcKay do you think that may work fine?
2 Likes
Very cool @emre!
I did not know we could do that. Rather, I forgot about the JSON Parser feature.
2 Likes
Many Thanks!! This work fine.
EDIT: For add Total I use {SETTING:AS Entity Balance} , but is whitout ,00 . Is possible to add
Use FN() function, something like:
[=FN('{SETTING:AS Entity Balance}')]
You might need to use that in the Formatting section instead, so please show your full Template. For example, it might be like:
<J00>{0}|{1}|[=FN('{2}')]}