Export Item assistance!

V5 Question
1

Hi all. I’ll apologise in advance as I am 99% sure the answer to this is in the forum, but I am failing to find it despite searching thoroughly.

I am attempting to create an export of all items (per: Export menu items to excel) but with the addition of the Barcode field.

I have tried adjusting the format of the report myself but it ends up with me having a CSV that contains the headers and no data.

Any help to add the barcode field correctly would be hugely appreciated.

Additionally, if any absolute wizard wants to nudge me in the direction of creating a similar export for order tags (name, group, price) that would be just… lovely.

Thanks!

2 
[DataExport:0]
"Group Code","Menu Item Name","Portion Name","Barcode","Price"
{REPORT SQL DETAILS:
        SELECT mi.GroupCode,
            mi.Name AS MenuItemName,
            p.Name AS PortionName,
            mi.Barcode,
            pr.Price
        FROM MenuItems mi
            INNER JOIN MenuItemPortions p
                ON p.MenuItemId = mi.Id
            INNER JOIN MenuItemPrices pr
                ON pr.MenuItemPortionId = p.id
        ORDER BY mi.GroupCode;
    :F.GroupCode,F.MenuItemName,F.PortionName,F.Barcode,F.Price::"{0}","{1}","{2}","{3}","{4}"
}

this should get you going for your menu item report

breaking down the parts so you can build your own order tags report:

"Group Code","Menu Item Name","Portion Name","Barcode","Price"
these are your column headers for the CSV export

{REPORT SQL DETAILS:x:y::z} this is the report expression that will run your SQL query and return the results

x: the SQL query

        SELECT mi.GroupCode,
            mi.Name AS MenuItemName,
            p.Name AS PortionName,
            mi.Barcode,
            pr.Price
        FROM MenuItems mi
            INNER JOIN MenuItemPortions p
                ON p.MenuItemId = mi.Id
            INNER JOIN MenuItemPrices pr
                ON pr.MenuItemPortionId = p.id
        ORDER BY mi.GroupCode;

y: the fields/columns you want to return from the query
F.GroupCode,F.MenuItemName,F.PortionName,F.Barcode,F.Price
the names must match the column names or what name is specified (e.g. AS MenuItemName)

z: formatting the output
"{0}","{1}","{2}","{3}","{4}"
{x} where x is the index of where the column data is found - arrays start at 0 so the first column in the the above list, F.GroupCode is {0}. With the exception of {x} all other characters will be return as typed.

1 Like
3

This is awesome thank you

4

I’m not a SQL guru like you but what are the 2 letters of the field name used mi.Name, pr.Price, etc.? Are they abbreviation’s?

Thanks

5

They’re table aliases. When you specify a table, the characters that follow would be the alias - e.g.dbo.MenuItems mi. Instead of referencing the the column by MenuItems.Barcode one can use the alias.

It’s beneficial in joins to make explicit which table column is requested - and mandatory if there are columns of the same name in tables - e.g. Id, Name, etc.

1 Like
6

Learned something new today, now I can go back to bed. :rofl:

Thanks, @Memo!

1 Like