Export menu items to excel

V5 Question
1

Hello,
I understand that I can export menu items to excel using reports.

But can someone kindly help me with the script to generate the report.

I need the following information in excel for all menu items.

Product name, Product group, portion and Price.

Thanks
Waqar

2

You can use Reports > Data Export feature. Create a new Data Export as follows:

[DataExport:0]
"Product","Portion","Group Code","Price"
{REPORT SQL DETAILS:
    SELECT
        product.[GroupCode] AS [Group],
        product.[Name] AS [Product],
        portion.[Name] AS [Portion],
        price.[Price] AS [Price]
    FROM [MenuItems] product 
    LEFT JOIN [MenuItemPortions] portion 
    ON product.[Id] = portion.[MenuItemId] 
    LEFT JOIN [MenuItemPrices] price
    ON price.[MenuItemPortionId] = portion.[Id]
    ORDER BY product.[GroupCode]
    :F.Group,F.Product,F.Portion,F.Price::"{0}","{1}","{2}",{3}
}

We have to use an SQL report in order to get all the data for each product on a single line.

NOTE: To make sure it prompts you to save it as a CSV file instead of a TXT file, you can specify the default save location with *.csv as the filename. When you run the report, the file dialog will appear and prompt you for the name of the CSV file to save to.

You have to provide a path for the filename if you want to use *.csv, so for example to set to the default SambaPOS5 folder in Documents:

C:\Users\<yourname>\Documents\SambaPOS5\*.csv

(replace <yourname> with the user’s folder name)

You can of course just set an exact filename then it will save automatically, and you can use printer template tags like {DATE:X} to automatically generate a custom filename with the date/time.

More details about saving with file dialog using custom file type:

3 Likes
3

Is there a way to do this for recipes? @markjw

4

He is using sql so yes you could. Just need to use the appropriate sql code to get what you want. Format it similar to how he did with the export formatting in the report tag

1 Like
5

Hi,

I’m using this code to create an export of all my menu items and it works great, Thank you.
I’m Trying to add in a line to also include the text from the product barcode field, but can’t seem to find the right way in the SQL part (not my strong suit). Does anyone know off hand what the string is to reference the barcode in this?

Many thanks.

6

Something like this? You can find column names in dbo.MenuItems table in the database

[DataExport:0]
"Group","Code","Product","Portion","Price"
{REPORT SQL DETAILS:
    SELECT
        product.[GroupCode] AS [Group],
        product.[Barcode] AS [Code],
        product.[Name] AS [Product],
        portion.[Name] AS [Portion],
        price.[Price] AS [Price]
    FROM [MenuItems] product 
    LEFT JOIN [MenuItemPortions] portion 
    ON product.[Id] = portion.[MenuItemId] 
    LEFT JOIN [MenuItemPrices] price
    ON price.[MenuItemPortionId] = portion.[Id]
    ORDER BY product.[GroupCode]
    :F.Group,F.Code,F.Product,F.Portion,F.Price::"{0}","{1}","{2}","{3}","{4}"
}
7

Actually this is perfect thank you!

Sorry to ask a noob question but how do I actually view the dbo.MenuItems table ?
Just getting to grips with this recently.

Many Thanks

8

You would need SQL Server installed and SQL Server Management Studio installed to start doing direct queries and check your databases tables and other properties.

I would suggest you to not do any of this on the production database until you are familiar with all this.