SQL query please

(written with cell phone)
Slept 2 hours, tired, could you please tell me the SQL query (for script) to get the value of:

  • CPT (Ticket Tag)
  • CodeId (Custom field of ENTITY TYPE “SUNAT” ; NAME = Sunat)
    Where TICKET ID = ‘+settingname+’

It would help me :blush: @QMcKay ?


Pretty sure ticket tags are stored in JSON within the SQL which makes extracting them harder with direct SQL. Might be easier with GraphQL if your up to date - unfortunatly I am yet to delve too deap into that to be able to speil off any sugestions on that front.
How are you initiating the script? From what rule event that you need to store the ticket id in a setting rather than feed into the script as a variable?

pleaaase the sql :disappointed_relieved:
going live in 2 hours

That’s no one’s fault but yours.
I don’t have the answer you need so was trying to help with possible alternative, don’t want it then your choice, will leave you to it.
Q is states based I beleive so it’s between 2-6am over there so expect he is sleeping.

Thanks …
I’ll wait that states wakes up.

To use SQL to get that you need JSON support turned on in SQL express 2016 or 2017. Do you have that part done?

You mean that I cannot get that information with some standard SQL (without jason)?

(yes i have 2016, no JSON support is not turn on yet)


Pretty sure we answered that.

There are other ways to get ticket tags using custom report tags. Maybe explain with screenshots what your doing and we can provide a few solutions.

Yes. To take advantage of full power of JSON install and setup 2016 .
Don’t know JSON, no time to learn how to take full advantage of JSON inside SQL query, just know basic SQL.
As SambaPOS V5 works with any version of SQL, it must be possible to extract that info using “normal” SQL, am I wrong?


Your wrong I’m sorry you can wish it all you want but won’t change anything.

BTW that is normal SQL. The ability to do that came out with SQL Express 2016.

Please explain what your doing we likely can help you accomplish it without SQL

I am trying without SQL.

B=Boleta : a type of bill; it has its own serial number
F=Factura : another type of bill; it has its own serial number

I am using a GEN printer that prints ToFile.
That file has a dynamic filename, like

^^^         ^^ ^^^^^^^
Company #      Serial number of bill
            Type of bill

Company # is global, no problem

type of bill is ticket related (ticket tag)
Serial number is global but it depends on the type of bill
And these 2 ticket related variables are accessible while in ticket screen, reports, printer template, but are not accessible in the GEN printer configuration setting (here after)

Note that I realize that the script/query based on “TICKET ID” will not work because this variable “TICKET ID” is also not accessible in the “memory space” (don’t know how to name it) of that printer configuration setting. Only global variables are available.

I created a “tempory” global variable tmpCPT as a work around, but I don’t like it because I had lots of troubles with the updates/reset of these global variables, not so global.

And here it comes again …

That SFS button runs the rule that contain these actions:

  • Set tmpCPT to the value of Ticket Tag CPT with sql UPDATE [ProgramSettingValues] …
  • Buid the filename with the makeSFSFilename function that takes as parameter {GLOBAL SETTING…}
    which does not work, I’ll try with SQL now to see if it helps.

Not sure if I’m misunderstanding but why have a script to set the program setting then using a nother script with the setting as a variable? Why not just feed to ket tag into the script?

See, before the tmpCPT global setting, I was using directly the TICKET TAG CPT.

the rule is executed in the ticket screen and itcalls multiple times the same function db.xxx:

  • first action JUST to display a message (TICKET TAG:CPT) => result OK (0 or 1 is display)
  • second action is the exec print job whose “filename configaration setting” contains also the same function, showing the same message, exept that it showed => it display {‘TICKET TAG:CPT’} instead of 0 or 1

So I deduce that “filename configaration setting” only recognises the global setting (and not the TICKET TAG), that’s why I am using (with trouble) tmpCPT in programsetting to store temporallily the ticket tag CPT.

dono if I am clear

Ok, but pretty sure based on that you don’t want to use global setting but local else you could get cross over between multiple tills.
Not that it should make a difference but not sure why you would use a script to update program settings when there is action for this but either way.
To your previous question you can in theory strip tag values out of the JSON without the JSON functionality but it is very hacky. My PMS intergration I did this for ticket states using splits to slice the data out of the JSON in it’s string/unparsed format. But it’s a dirty way to do it and only did it that way as was early days in my scripting knolage and SQL hadn’t added the JSON functions it has now.

Sorry just jumping in here without fully reading the topic, but even if you don’t use SQL 2016 or above with JSON support, another way you could access JSON data is in a script, you have access to JSON.parse() and you could load the relevant field in using sql.Query() or another SQL function. Read more here…


Very true, good point mark.
I remember struggling at that time and opting for the dirty hack as a quick fix. Need to clean up in v2 but it’s working fine for now.

1 Like