Hello, I'm stuck trying to figure out how to ...

(Zee W.) #1

Hello, I’m stuck trying to figure out how to format an email attachment according to specific template. Expressions in image #1 produces the output in image#2 (which is working well). But the team wants information displayed according to image #3. I am at a loss writing the correct expressions that will get data from the two tables to display correctly. The information are coming from two tables - Orders and Order Details.

Orders table has the following columns: Order Id [Key] Order status Customer name Dealer Order Date Requested Delivery Date Sales Rep Tractor Model Hyd Spool Qty Total Welding Hrs Total Assembly Hrs

Order Details table has columns: OrderDetailId [key] Order Id Category (ex Tanker, PTO, Pump,

Product Id Product Code Door Rear view Remarks

Pls, what is the correct expression to use?

(Marc Dillon) #2

It looks to me like you do not need to use a Start expression. Create the table in your template document with exactly how many row & cols as shown in your picture. Then you’ll have to go through each cell and specify the data to display.

Please correct me if I’m wrong, but the data you were previously displaying from the Start expression (rows in order details), you now want to show up in the 2nd table in the 3rd picture? Below where you have the <<[Tractor model]>> and <<[Hyd sppol qty]>>??

I’m thinking you’ll have to do a Lookup expression for each cell. For example in the Pump cell put: <<LOOKUP(“Pump”, [Order Details] , “Category” ,

“Product”)>> The 4th value is the return value, I wasn’t sure what you wanted to be displayed there, just thought the Product column was likely. You may want to create a new virtual column in order details table that concatenate the display value that you’re eventually going to want. For all other cells just replace the first argument (“Pump”), with the appropriate category to display)

(Zee W.) #3

@Marc_Dillon Thanks for the suggestions, Marc.

I tried LOOKUP in the Word attachment template but it only returns the formula :frowning:

I also tried variations of LOOKUP, e.g.

  1. LOOKUP(“Tanker”, Order Details, Category, Product Id) 2. ANY(SELECT(Order Details[Product Id], [Category] = “Tanker”))

and get the same output.

Pls let me clarify - my sales reps are creating made-to-order machinery on the app. I won’t be able to know how many items will be in an order so I’m unable to fix the size of the table in the template.

What am I doing wrong?

(Marc Dillon) #4

Can you post a picture of your new template you’ve tried?

You can use a Start expression to generate an unknown number of rows, but not columns. It looks like you want to split the number of products across two columns (half in first column, half in second). I’m not sure if that will be possible.

(Zee W.) #5

@Marc_Dillon I played around with a few more expressions and was able to get an output for just one of the categories. However, it doesn’t seem to work with other.

Here is the template with expressions

(Zee W.) #6

@Marc_Dillon Here’s the output of the expressions. The value for Tyres is obviously incorrect. Just not sure how to write it so it’ll work!