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

[Griff]

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?

[Marc_Dillon]

Simple:

<<START: [Spec Required]>>

[Griff]

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]>> <>

[Marc_Dillon]

[Griff]

Griff:

<<START: [Specs Required]>> <<[URL1]>> <<[URL2]>> <>

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

[Marc_Dillon]

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.

[Griff]

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]>>

[Marc_Dillon]

Griff:

Yeah, that’s the error I made as mentioned in my last post but it didn’t solve the problem.

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

Again, remove the table name from your column references:

[Griff]

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?

[Steve]

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.

[Griff]

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?

[LeventK]

@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?

help.appsheet.com

Template Start Expressions

Using Start expressions in your workflow templates.

[Steve]

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

[Griff]

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

[Suvrutt_Gurjar]

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

[Griff]

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

[Suvrutt_Gurjar]

Griff:

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

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

[Suvrutt_Gurjar]

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

[Griff]

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.

[LeventK]

@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>>

[Suvrutt_Gurjar]

Totally agree with @LeventK

As per my understanding, the Specs table is not the child table but parent table. This is so because you are referencing the “Specs” table in Orders table.

So instead of the normal workflow’s flow, wherein many children rows are printed/emailed in workflow document corresponding to a single parent, here I believe you are trying to capture multiple parent records (Specs table) corresponding to a single child table (Orders table) . However in the template we are probably trying to use a traditional parent/multiple children approach.

So in if the “Specs” table is referred through a pure reference column instead of an enumlist with ref as base, various parent record fields could be captured in the template through dereferencing expressions such as

[Specs].[Desc1]

Anyway, now that we have reached this far, could you please once try with

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

<<[Specs].[Spec]>>

<<[Specs].[Desc1]>>

<<[Specs].[URL]>>

<< End >>

[Suvrutt_Gurjar]

Hi @LeventK,

You had already posted the correct solution.

Since I had typed in the reasoning behind the configuration, I thought let me post the explanation as well, just in case it helps @Griff

[LeventK]

@Suvrutt_Gurjar
Just the opposite I believe. Orders table is the parent and the Specs table is the child. He is just using a reverse reference with a ref type Enumlist, instead of a isPartOf ref. And if you are calling directly the Specs table with the SELECT, you don’t need to use the de-refs either.

[Suvrutt_Gurjar]

Hi @LeventK,

Oops. Thank you. Yes, a bit of mixed terminology. I agree it is not the traditional reference with the rev_ref column in the parent table. However, since we use dereferencing expressions [Specs].[Desc] etc. that we use in the child table, I referred “Specs” as a parent.

However, I am sure, your guidance will be more precise. Thank you for your guidance. This use case is interesting and learned a great deal from you , @Steve and @Marc_Dillon throughout the thread.

[LeventK]

Hello @Suvrutt_Gurjar
We all do have something to learn from each other at all times and that’s why this community is the greatest knowledgebase ever

[Suvrutt_Gurjar]

I fully agree @LeventK.
Such an exciting community. I always feel, this is a special community with members with expertize from so many fields, industries, businesses contribute. This is a bit different from most communities that are typically dedicated to one single technical subject.

[Griff]

Gentlemen, I could not agree more with the sentiments you express. The level of help provided on this site is overwhelming and you all deserve immense thanks and praise for your contributions. I could not have developed any of the apps I already have without your ongoing support.

Unfortunately, at this point in time, none of the suggestions you have made work with this app.

Below are the two approaches and resulting error messages

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

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

<>

Errors:

“… Expression ‘[Desc1]’ is invalid due to: Unable to find column ‘Desc1’, did you mean ‘Tab1’?.. Expression ‘[URL1]’ is invalid due to: Unable to find column ‘URL1’, did you mean ‘Tab1’?.. Expression ‘SELECT(Specs[Spec] , IN([_THISROW].[Specs Required],[Spec]))’ is invalid due to: Parameter 2 of function IN is of the wrong type… Start expression ‘SELECT(Specs[Spec] , IN([_THISROW].[Specs Required],[Spec]))’ should generate a List of Ref values. Please verify that it generates a List and that the contents of the List are Ref values. Ref values should come from the ‘Key’ column of the referenced table.”,

OPTION 2

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

Spec	<<Start:[Specs Required]>><<[Specs Required]>>
Desc1	<<[Specs Required].[Desc1]>>
URL1	<<[Specs Required].[URL1]>>

<>

Errors:

“… Expression ‘[Specs Required].[Desc1]’ is invalid due to: Column Specs Required in expression ‘[Specs Required].[Desc1]’ does not contain a reference… Expression ‘[Specs Required].[URL1]’ is invalid due to: Column Specs Required in expression ‘[Specs Required].[URL1]’ does not contain a reference.”,

[Suvrutt_Gurjar]

Hi Rob,

Please ignore my immediate above post. I am deleting it.

Griff:

Errors:

“… Expression ‘[Specs Required].[Desc1]’ is invalid due to: Column Specs Required in expression ‘[Specs Required].[Desc1]’ does not contain a reference… Expression ‘[Specs Required].[URL1]’ is invalid due to: Column Specs Required in expression ‘[Specs Required].[URL1]’ does not contain a reference.”,

It sounds that you will need to create a separate reference value out of list of [Specs Required] list of references and then derference based on each individual reference value in the format

[Specs 1].[Desc1] , [Specs 2].[Desc1] and so on where [Specs 1], [Specs 2] are the individual references pointing to thw Specs table.

[Griff]

Just so I understand it - if the user selects say 15 items from the ENUMLIST [Specs Required] then I need to have to identify them individually [Spec1]…[Spec15]? If that is the case, then I can’t use this approach to do what I need because I have no idea how many specs my users will need. Am I understanding you correctly?

That was always my fear but I hoped I was wrong.

[Suvrutt_Gurjar]

Yes, at least as per my understanding it is so. My suggestion will be to await insights from other community members.

[LeventK]

@Griff

---

Email PDF Attachment

---

---

Attachment Template

---

Template URL > https://docs.google.com/document/d/1UR-n5-ITya5k3n_CyfVJXXOIZ3TFw1fxdfczXMrykd8/edit?usp=sharing

---

Proof of Concept

---

Choose your name from the Deck View, fill out and save the form. In the form [Send_To] is an email field.

[Griff]

Well you have, once again, earned the accolades for finding a working solution. I haven’t studied it to see what the error in previous suggested solutions was, but this code worked perfectly. Thank you so very much. This is a function I will be able to use in multiple settings once I figure out how it worked.

Thanks again to all of you for your patience and persistence. Clearly a team effort. I appreciate it very much

Orders

Who For: <<[Who For]>>

Where: <<[Where]>>

Order Title: <<[Order Title]>>

Specs Required

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

[LeventK]

You’re welcome @Griff, totally my pleasure to be helped of. Glad to hear that you have now solved the mystery. AppSheet learning curve is a bit steep and needs a lot of trial&errors, but the satisfaction at the end is pricesless for sure But you know the saying: “No pain, No gain”

[Suvrutt_Gurjar]

Levent the legend

[LeventK]

@Suvrutt_Gurjar
I’m just a humble developer that’s all, but thanks for your appreciation anyway.

[Griff]

I am very indebted to you on this one. I’ve just finished polishing up the report and associated forms and it works brilliantly and is very functional. Thanks again

[LeventK]

You’re welcome

[MultiTech]

It’s amazing how many people I see manually building an order app. Literally, dozens of people all doing the same thing, having the same problems.

You’d think a centralized solution for these people would have been created by now…

[Griff]

MultiTech_Visions:

It’s amazing how many people I see manually building an order app. Literally, dozens of people all doing the same thing, having the same problems.

You’d think a centralized solution for these people would have been created by now…

The way I see it is that every business takes orders but the order and the associated processes are all so different.

I researched 13 off-the-shelf solutions looking for one that would meet the needs of the business I am working for. However, all the systems are a “Jack of all trades, master of none” and its the shortfalls that make them hard to cope with.

Appsheet can’t do everything off-the-shelf systems can do, but its flexibility to handle uncommon requirements often makes up for the shortfalls, especially if workarounds can be found. And this forum is the place that helps developers find the workarounds.

Appsheet’s major shortfall is that it renders the business too dependent on the in-house developer.

[MultiTech]

Well said!

I was primarily commenting as a sleight jab at my other AppSheet expert colleagues

• Your specific technical problem (of order-detail stuff) is one of the most common questions we hear here in the community. Even though your specific scenario will be different, the WAY in which you go about creating that in AppSheet is 100% the same. (Parent-child relationships, de-references, slices, actions, workflows - it’s all the same, just looks different and maybe has more “little bits” here and there that are different.)

Maybe one of these days some starting point solution will be created that’s sufficient enough to catch most of these types of questions.