Email Attachment

[TalkNet_TechnoS]

I have 3 Tables:
Products (It has 1pdf or image of each product)
Orders (Parent)
Items of the Order (Child)

I have a Workflow that sends the Order with the Items Detail
and I need to attach the pdf that correspond to the Items. or other attachments.
What I can not achieve is to relate the 3 tables in the expression “Other Attachment file name”

SELECT (Products [PDF], CONTAINS (Items, [_ THISROW]. [Order Id]))

I understand that I would have to be able to extract from the Table of Items, the Id Products that correspond to the Order Id.

But I do not realize how to do it!

Some help?

From already thank you very much!

[Phil]

Can you clarify how the Order or OrderDetail record refers to the Products record?
Typically I would expect the OrderId record to contain a Ref field that refers to a record in the Products table.

[TalkNet_TechnoS]

Products (It has 1pdf or image of each product)
Orders (Parent)
Items (Orders Child)
Ref>Orders[Order Id]
Ref>Products[Product Id]
I try this, but dont work

SELECT(Productos[PDF],SELECT(Items[Product Id],[Order Id]=[_THISROW].[Order Id]))
or
SELECT(Productos[PDF],[Related Items].[Producto Id])

[Steve]

Hmm… Try this:

SELECT(
  Products[PDF],
  IN(
    [Products Id],
    SELECT(
      Items[Product Id],
      IN(
        [Item Id],
        [_THISROW].[Related Items]
      ),
      TRUE
    )
  ),
  TRUE
)

1. SELECT(Products[PDF], ..., TRUE) gathers a list of PDF column values from rows of the Products table that match the given criteria (..., see (2)). TRUE removes duplicates from the list.

2. IN([Products Id], ...) matches only rows (in the Products table, per (1)) with a Product Id column value that matches an item in the given list (..., see (3)).

3. SELECT(Items[Product Id], ..., TRUE) gathers a list of Product Id column values from rows of the Items table that match the given criteria (..., see (4)). TRUE removes duplicates from the list.

4. IN([Item Id], [_THISROW].[Related Items]) matches only rows in the Items table with an Item Id column value that matches an item in the current order’s Related Items list.

Steps (3) and (4) generate a list of distinct Product Id values from the items in the order. Steps (1) and (2) then generate the list of distinct PDF column values from the products with those Product Id values.

[Phil]

Hi Steve Coile,
You are the king of expressions. Can you help with this one?

[TalkNet_TechnoS]

I made many combinations of Select and IN, I knew that was the way but I did not have the mental clarity that you have. Just great
But I have another problem:
When I do the TEST of the expression, it returns the path of the correct PDFs but when I execute the Workflow, it fails and I get the following error:

“Errors”: "Error: Workflow attachment file ‘3’ not found for UserId: 461773, UserEmail: ‘at.talknet@gmail.com’, Provider: ‘google’, DataSource: ‘google’, Path: ‘DocId = 1vevAJh_ZEh9 -8zaj3ycJkXSzKi-iZX0IeFf6fh8qWqk ‘, Table:’ Products’ [Shared], ProbableCause: ‘Failed: Path:’ 3 ‘, Message:’ Searching for ‘3’, Cache entry not found ‘.’ “,
“Warnings”: “Warning: App version 1.000530 is not deployed.” All workflow emails are therefore being sent to the app creator This email should have gone To 'at.talknet @ gmail.com, simasanjuan @ gmail.com, jahernandez @ gmail .com ‘CC’ed to’ ‘and BCC’ed to’ '”,

If I click on the file icon in the app, open the pdf without problems, there is no route error, but I am intrigued by that number 3: “Failed: Path: ‘3’, Message: ‘Searching for’ 3 '”
I do not understand where it comes from, it’s as if one of the files had the route as 3.
Some of the products do not have an associated pdf, and the cell of the route is blank, first I thought the error was caused by not finding any route, but I copied the route of another product in the cell of those that had no route and He kept giving the error.
Thanks in advance for the help

[Steve]

@Phil, any ideas?

[TalkNet_TechnoS]

Resolved! was another attachment that generated the error. Thank you