Can I turn an ENUMLIST into a Select() to include in a Workflow

I have a Column named [Specs Required] using an ENUMLIST with a Type of REF linked to a Specs Table. Using this list the user can select which Specs are needed for the Job.

When the user has saved the record I want to create a workflow that will send an email with information as required for the record just updated. That email needs to have an embeded sub-table with columns taken from the Specs table.

Ordinarily I would use a statement something like the following to create the sub table from which I could then use the other columns:
<<Start: Start:SELECT(Specs[Spec],(Specs[Spec]=[_ThisRow].[Spec Required)))>>

However that won’t work because [_ThisRow].[Spec Required] is a List not a value. Is there any way of making this work or do I need to pursue other solutions?

Simple:

<<START: [Spec Required]>>

3 Likes

Not sure I understand.

Specs Required is a list of the Specs the user selected. They correspond to the Key field values of the records in the Specs table. In the email I need to include 8 of the other columns from the Specs table for each selected Spec Key.

For example If I need to list the columns URL1 and URL2 for the Specs Required, how do I modify the following code to do it?
<<START: [Spec Required]>> <<Spec[URL1]>> <<Spec[URL2]>> <>

image

1 Like

Thanks for the suggestion. It’s helped me discover some errors in the way I configured my data but it’s still not working as it should. Instead of listing just the column values for the items selected from the ENUMLIST, it’s displaying a list of the column values for every row in the Specs table

Please double check that you actually do have the base type set as Ref for your EnumList, and that you have selected the correct table, in the column definition.

1 Like

Yeah, that’s the error I made as mentioned in my last post but it didn’t solve the problem. For clarity I will post below an image of the gdoc that I am using, with most of the fields removed, it that helps. Maybe I am doing something glaringly wrong and I’m just too tired to see it.

Orders

Who For: <<[Who For]>>
Where: <<[Where]>>
Order Title: <<[Order Title]>>
Specs Required: <<[Specs Required]>>

Specs

Spec <<START: [Specs Required]>>
Desc1 <<Specs[Desc1]>>
URL1 <<Specs[URL1]>>
Desc2 <<Specs[Desc2]>>
URL2 <<Specs[URL2]>>

Looks like you edited your post after I had already read it?

Again, remove the table name from your column references:

image

1 Like

Ok, so here is what I changed it to

Spec <<START: [Specs Required]>>
Desc1 <<[Desc1]>>
URL1 <<[URL1]>>
Desc2 <<[Desc2]>>
URL2 <<[URL2]>>
Desc3 <<[Desc3]>>
URL3 <<[URL3]>>
Desc4 <<[Desc4]>>
URL4 <<[URL4]>><>

And this is what the Specs Required field is set up as

I then tried running it but it comes up with the following errors:

Expression ‘[Desc1]’ is invalid due to: Unable to find column ‘Desc1’, did you mean ‘Tab1’?. Error: Workflow rule ‘Email JSA Specs Under Development’ action ‘Email Specs under development’ Body template. Expression ‘[URL1]’ is invalid due to: Unable to find column ‘URL1’, did you mean ‘Tab1’?. …

The field it’s suggesting, Tab1, is from the Orders table, whereas Desc1, URL1 etc are from the Specs table. How do I code it so that it tries to find the data in the Specs table?

Within a <<Start>>/<<End>> pair, column references (e.g., <<[Desc1]>>) refer to the rows identified in the <<Start>> tag. In your case, the <<Start>> tag is <<Start: [Specs Required]>>. [Specs Required] refers to a column of type EnumList of Ref to the Specs table. <<[Desc1]>>, then, refers to the Desc1 column value of a row of the Specs table. Expression Assistant is telling you the Specs table has no column named Desc1.

2 Likes

Thanks Steve, that’s what I thought may be happening. So does that mean I cannot achieve what I need to, or is there another way?

@Griff
Where is those [Desc(x)] columns are located? In which table? Are those columns in the child table (in this case Specs) or are they located in the parent table? Moreover, from which table are you triggering the PDF template/workflow rule?

Also, have you checked this page?

1 Like

I have no idea. I’ve been unable to understand how your data is structured from reading all of the above.

3 Likes

My child table is Specs.
The Key column is called [Spec] and the table also contains the columns Desc1 to Desc4 and URL1 to URL4.

My parent table is called Orders
Each order, in my Orders table, needs one or more associated Spec so it has a column called [Specs Required] which is an ENUMLIST containing one or more Key fields ([Spec]) from the child Specs table.
When an order is generated/edited I need to send an email to nominated users with an attachment that has the order details and the associated spec details. E.g.

Order by: [Who For]
Specs Required:
Desc … URL
[Desc1] [URL1]
[Desc2] [URL2]
[Desc3] [URL3]
[Desc4] [URL4]

Ordinarily I would use a statement like the following to achieve what I need:
<<Start: Start:SELECT(Specs[Spec],([Spec]=[_ThisRow].[Spec Required])))>>
i.e. Specs associated with or required to satisfy this order

However that would only work if there were many Specs matching a single Spec Required, i.e. [_ThisRow].[Spec Required] needs to be a single value, not a list of values

I am trying to create a list of Specs associated with each of the elements in my ENUMLIST and I don’t understand the way to do that with a Select statement

I hope this makes it a little clearer

Could you please attempt with the following start expression?
<<Start:SELECT(Specs[Spec] , IN([Spec], [_THISROW].[Spec Required]))>>

Thanks for your suggestion Suvrutt. Here’s what I entered (minus some of the fields from the Orders table.

Orders

Who For: <<[Who For]>>
Where: <<[Where]>>
Order Title: <<[Order Title]>>

Specs

Spec <<Start:SELECT(Specs[Spec] , IN([Spec], [_THISROW].[Specs Required]))[Spec]>>
Desc1 <<Specs[Desc1]>>
URL1 <<Specs[URL1]>>

The output was like this (cut down version). So it’s not showing Specs associated with the order but All specs.
Orders
Who For: AGEM Property Group
Where: TEST Data only
Order Title: Test!!
Specs
Spec , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,
, , , , , , , , , , , , , , , , ., , , , , , , , , , , , , , , , , , , , , , , , , , ,
, , , , , , , , , , , , , , , , ., , , , , , , , , , , , , , , , , , , , , , , , , , ,
Desc1 MSDS , MSDS , MSDS , MSDS , MSDS , MSDS , MSDS , MSDS , MSDS , MSDS , MSDS , MSDS , MSDS , Part A , MSDS , MSDS , MSDS , MSDS , MSDS , MSDS , MSDS , MSDS , MSDS , MSDS , MSDS , MSDS , MSDS , MSDS , MSDS , Part A , Part A , Part A , MSDS , MSDS , MSDS , MSDS , MSDS , MSDS , MSDS , MSDS , MSDS , MSDS , MSDS , MSDS , MSDS , MSDS

Thank you. Any specific reason , you are using the last [Spec] in the expression highlighted above?

Also as recommended by @Marc_Dillon before, you may not use Specs[Desc1] but just [Desc1]

Ok, I totally simplified my output to the following

Orders

Who For: <<[Who For]>>
Where: <<[Where]>>
Order Title: <<[Order Title]>>

Specs

Spec <<Start:SELECT(Specs[Spec] , IN([Spec], [_THISROW].[Specs Required]))>>
Desc1 <<[Desc1]>>
URL1 <<[URL1]>>

<>

This is the error in the log.
“Errors”: “Error: Workflow rule ‘Email Specs Under Development 2’ action ‘Email Specs under development’ Attachment template. Expression ‘[Desc1]’ is invalid due to: Unable to find column ‘Desc1’, did you mean ‘Tab1’?. Error: Workflow rule ‘Email Specs Under Development 2’ action ‘Email Specs under development’ Attachment template. Expression ‘[URL1]’ is invalid due to: Unable to find column ‘URL1’, did you mean ‘Tab1’?.”,
"

That’s why I changed away from the earlier recommendation. Clearly there’s something here I am just not grasping.

@Griff
I believe your START expression shall be:

<<Start:SELECT(Specs[Spec] , IN([Spec],[_THISROW].[Specs Required]))>>

Spec Value
Spec <<[Spec]>>
Desc <<[Desc]>>
URL <<[URL]>>

<<End>>

OR

<<Start:[Specs Required]>>

Spec Value
Spec <<[Specs Required]>>
Desc <<[Specs Required].[Desc]>>
URL <<[Specs Required].[URL]>>

<<End>>

1 Like