Ticket Data Report/Export

V5 Question
1

99% sure this has been asked before and 99% sure there is no solution

Ideally I need all Orders with Order Tags exported/reported

this is so we can build enhanced reports and also bill our customers by importing the data in our finance system.

I know it’s 2 different sets of data and Order Tags are not stored as SQL data joined to the Order table (this would make it so simple) but there must be a way as it displayed on screen so must be able to be exported/reported as it’s really just a ticket.

Date/Time, Customer, Order#, OrderlineID, Product, Qty, Price
Date/Time, OrderLineID, OrderTagName, Qty, Price

I’ve always had this from previous systems and I believe it’s what a lot of people have asked for previously having spent hours searching the forum.

Two resellers have told me it’s not possible - anyone got any ideas

2

order tags are stored in orders table arnt they? Been a while since I looked at tables.
Thought they were stored in json in order tags column.
This is pretty common for flexible content.
SQL 2017 have support for json parsing in queries.
We post itemised charges to our booking system. We don’t show order tags as there own lines, price is included on order price regardless of samba setting but could have been seperate line.
Almost anything is posible with a bit of work.

3

thanks… yes as JSON data inside the Order record.

Happy to pay you if you can pull it apart and provide the data

I believe many have asked for this but I’ve yet to find someone that can provide the solution

4

How are you pulling/pushing/exporting the data?

5

I just want the data so I can import it into our reporting DB and our billing system.

can be done in batch or realtime

6

So could you not use export from custom report.
Use order id to link rows from 2 exports from orders and order tags reports?

7

You can use data exports to do this and regular report syntax.

Where are you accessing the data and how? As CSV?

8

They are stored there as JSON. We can access order tags with custom reports, We have data export feature for exporting that and we can access them with SQL.

9

What you have not told us though is exactly what data do you need and in what format? Just all orders that have order tags? Or are you needing the order tags that have orders? What exactly are you wanting?

10

IS that the format you need it in?

What is OrderlineID?

11

thanks Jesse… Format is not set in stone

OrderlineID was just a way to link product on a ticket to its modifiers (the line above)

Basically that is from a system called Tevalis and I am sure there are may other ways.

12

One moment I will build you an example. Yes this has been asked before and its really simple with data exports. If a reseller told you its not possible then please educate them once I show you this.

13

as per the example all tickets for the day

Date/Time
Account
Product
Qty
Price
and any modifiers for that product

14

Account? What account?

15 
[Report:1,1,1,1,1,1]
@{REPORT ORDER TAG DETAILS:T.Name::,}
{REPORT ORDER DETAILS: 
  O.Date,
  EN.Customers,
  O.MenuItemName,
  O.Quantity,
  O.Price,
  OT.$1,
:
:"{0}","{1}","{2}","{3}","{4}","{5}"}

You can modify that for what you need.

1 Like
16

Only similar topics I can recall is creating an itemised invoice for multiple tickets as a single invoice for an entity account from within samba which would be a challange.
Which in honesty isn’t a 'normal thing when dealing with accounts as samba uses them and tickets are the invoices and you would get an account statement. Suppliers don’t usually give an itemised invoice for multiple deliveries at end of month, they give a statement of account showing the invoices.
If coupled with an external system where data can be manipulated and rendered outside of the built in ticket and reports system its certainly allot easier.

17

Here is the same thing minus the quotes around the values:

[Report:1,1,1,1,1,1]
@{REPORT ORDER TAG DETAILS:T.Name::,}
{REPORT ORDER DETAILS: 
  O.Date,
  EN.Customers,
  O.MenuItemName,
  O.Quantity,
  O.Price,
  OT.$1,
:
:{0},{1},{2},{3},{4},{5}}
18

You can probably extract the account from entity report field
E.AccountId may work

19

it’s close but there are no Order Tags so I am missing data to enable billing

Account = Customer

Also needs Ticket#

really appreciate the help, I’ve offered multiple people money to make this work and no one has been able to get it correct

Below is what Tevalis produced so we could bill our monthly customers/accounts

image
image974×85 5.68 KB

TransactionItemID links the Modifier (Yoghurt) to the product (SM-Green)

20

But there is order tags. Thats what OT.$1 is doing. Something your not telling us.