Just wondering if it is possible to use a local setting in an sql query like I can with settings stored in the database?
SET @entityId = (SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='TS EntityId')
I have an entity screen which lists all tickets for the selected entity but if another terminal selects a different customer then all other terminals update to that entity.
I don’t want to have to create a different automation for each individual terminal.
GraphQL has a Query for getLocalSetting(). You can use that to read the in-memory Program Setting into a JS variable, then use that variable in your SQL.
Or you can supply the Program Setting as a parameter using Execute Script Action and read it in your script with @1, @2, @3, etc.
Or you can supply the Program Setting as a parameter using Refresh Custom Report Widget Action and read it in your script with @1, @2, @3, etc.
Either way can work for you - it just depends on how you are invoking the SQL.
You could also store the setting in the DB, but use a name for the Setting that includes the Terminal Name to keep them separate.
How can I tell in the sql script which terminal is executing the script?
Here is the script:
declare @entityId int = 0
SET @entityId = (SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='TS EntityId')
SELECT Tickets.Id, Tickets.Date, TicketTypes.Name,Tickets.LastModifiedUserName, Tickets.TotalAmount
FROM TicketEntities INNER JOIN
Tickets ON TicketEntities.Ticket_Id = Tickets.Id INNER JOIN
TicketTypes ON Tickets.TicketTypeId = TicketTypes.Id
WHERE TicketEntities.EntityId = @entityId
Basically when I select a customer the report on the left refreshes to display that customers tickets.
You can’t unless you feed {:CURRENTTERMINAL} into the script and retrieve it with @1.
You have a Report that calls a @@SQLhandler which executes the SQL. But the SQL is not coded to use parameters/variables. You can pass parameters with the Refresh Custom Report Widget Action (I assume you are using this), and use those parameters in the script, like:
SET @entityId = (SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='@1')
^^
You can probably just do this instead as well…
Then in your Script:
-- PARM for Entity Type
declare @currentTerminalName varchar(20) = '@1'
SET @entityId = (SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]= @currentTerminalName + '_' + 'TS EntityId')
No I did not use the refresh custom report widget action. I just navigated back to the same entity screen to refresh it.
Will the refresh custom report widget store the values as locally or will it effect all terminals?
I have tried the second method (my preferred method because I have used settings in other automation apart from report widgets) but it does not seem to be passing the current terminal in the report. Is this how I should be doing it?
[#Customer Tickets:1,2,2,2,2]
>Id| Date| Type| User| Total
@@CustomerTickets:{:CURRENTTERMINAL}
Also will this not work for settings {SETTING:{:CURRENTTERMINAL}_TS_CustomerName} to display the selected customers name in a label widget?
Try this instead:
{SETTING:CURRENTTERMINAL}
{SETTING:{SETTING:CURRENTTERMINAL}_TS_CustomerName}
If {SETTING:CURRENTTERMINAL} still does not work within the Report itself, you will need to use the Refresh Custom Report Widget Action. It will definitely work in your case. You can probably still do the Navigate if you need to for some other reason, but you might need to put Navigate before Refresh, or vice-versa.
They are temporary variables assigned and used on-the-fly, so they are not really stored anywhere. They are “in memory” during the Refresh and then discarded, so I guess the makes them “Local”. Actually, they are parameters like you would use for a function - it is just a method to pass parameters to the SQL Script.
{SETTING:CURRENTTERMINAL} does not work in the reports either.
It seems to be breaking after the colon. If I pass the value directly back to the report it just shows as {SETTING
Yes this works fine for the report.
This nesting does not seem to be working but I can use local settings for these.
{SETTING:{SETTING:CURRENTTERMINAL}_TS_TID}
{SETTING:CURRENTTERMINAL} does not work in the reports either
Yes this works fine for this report.
This nesting does not seem to be working
{SETTING:{SETTING:CURRENTTERMINAL}_TS_TID}
Is there another method of passing out a value from a report that would be considered local?
Currently I am using this to pass out the balance so it can be printed on a statement.
I don’t understand the question. Values from a Report are all “Local” in that they are generated when the Report is run and they are displayed or used/consumed in some fashion, then they “disappear”.
However, most values from a Report (SQL or not) involve reading data from the DB, which is a “Global” object by nature.
Sorry forgot to add the statement I was using.
UPDATE [ProgramSettingValues] SET [Value]= (Select SUM(Debit)-SUM(Credit) FROM @tbl_ledgercomplete) WHERE [Name]='GL_Current Balance'
I need to print the current balance on the statement. I was using a global setting but If two terminals try to print statements at the same time the balance could be incorrect on one.
I know I could build another query for this but I was just wondering if there was a better method
Probably there is a better way. Like building another query. It is difficult for me to offer suggestions when everything is without context.
You can’t “pass values out of a SQL script” in a way like you are asking. The values “passed out of SQL” are the final SELECT statement in the script. That’s it. There is nothing else.
So you probably need to use another query.
If you use JScript then you have more flexibility. Because you can access data.Set() (sets a Local var)) and GQL functions (getLocalSetting(), setLocalSetting(), etc), and still run any type of SQL query with sql.Exec().
If you are Printing and using {REPORT SQL DETAILS:X} in your Template, and you want different results in one part of your Template, and another result in another part of the Template, then you need to run 2 (or more) Queries that return different result sets.
There are a multitude of ways to “skin a cat” in SambaPOS. You need to decide what works best for your end-purpose.
Probably there is a better way. Like building another query. It is difficult for me to offer suggestions when everything is without context.
You can’t “pass values out of a SQL script” in a way like you are asking. The values “passed out of SQL” are the final SELECT statement in the script. That’s it. There is nothing else.
So you probably need to use another query.
If you use JScript then you have more flexibility. Because you can access data.Set() (sets a Local var) and data.Get() (reads a Local var), and GQL functions (getLocalSetting(), setLocalSetting(), etc), and still run any type of SQL query with sql.Exec().
If you are Printing and using {REPORT SQL DETAILS:X} in your Template, and you want different results in one part of your Template, and another result in another part of the Template, then you need to run 2 (or more) Queries that return different result sets.
There are a multitude of ways to “skin a cat” in SambaPOS. You need to decide what works best for your end-purpose.
Yes I understand that. I just didn’t want to be executing the same script twice, its fairly long with 6 temporary tables to gather the information required, would this slow things down or should it be ok.
Thats what I was thinking after doing some reading on the forum. I will have a go and try get something together.
Thanks for your help!