Field code SQL syntax
Hi all,
I want to use Word mail merge to generate dynamic order summary sheets that list every line item on that order. I have a Word file linked to an Excel file that pulls order data through a database field. Some orders might only have 1 line item while others have dozens. The Excel table goes by line items, so there will be multiple rows corresponding to the same order. So far, my fields look like this:
Summary sheet for order# { MERGEFIELD OrderNumber }
{ DATABASE \d "(database instructions)" \s "SELECT `OrderNumber`, `Data1`, `Data2`, `Data3` FROM `SourceTable$`" }
But that loads the entire table onto each page. I figured I could modify the SQL statement with a WHERE clause to only select data for the corresponding OrderNumber for that specific summary sheet.
Summary sheet for order# { MERGEFIELD OrderNumber }
{ DATABASE \d "(database instructions)" \s "SELECT `OrderNumber`, `Data1`, `Data2`, `Data3` FROM `SourceTable$` WHERE `OrderNumber` = { MERGEFIELD OrderNumber }" }
But no matter how I try it, I always get: Error! Cannot open data source. Am I using the correct syntax? Is it something with data types?
Thank you in advance for any guidance