I got it to work with a very small tweak!
@JTRTech The elusive secret mystery of the terminating comma has finally been revealed!
[Price List:5 ,3,3, 1, 2]
@{REPORT SQL DETAILS:SELECT DISTINCT [GroupCode] FROM [MenuItems] ORDER BY [GroupCode]:F.GroupCode::{0},}
>Product | Portion | Band | Price
$1 ==============
{REPORT SQL DETAILS:
SELECT
product.[GroupCode] AS [GC],
product.[Name] AS [Product],
portion.[Name] AS [Portion],
price.[PriceTag] AS [Price Band],
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]
WHERE 1=1 and product.GroupCode='@1'
ORDER BY product.[GroupCode], product.[Name], price.[Price];$1
:F.Product,F.Portion,F.Price Band,F.Price::{0} |{1}| {2}| {3}
}
The Trick is the comma in the list (at the end of the first line in the format section). Once I realized that the “list” was not comma-separated as I thought, I decided to try the comma at the end of the format output. VOILA! It works!
@JTRTech The elusive secret mystery of the terminating comma has finally been revealed!
The only other thing I did was add a WHERE clause in the SQL, which I had tried before, but without the COMMA, we were not working with list items, we were working with concatenated GroupCodes.
