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?
Solved! Go to Solution.
Your imagined workflow is that a user would open up the detail view of some Order. Then either by a quick-edit column, or going into a Form view, the user would select one or more Task Categories from the EnumList field. At which point an email would go out listing the Tasks in that Order which match the Categories. Is that correct?
Assuming the above is correct, then no you wouldnโt need to store the key values in the EnumList.
Your EnumList valid_if could be:
[Related Tasks][Task Cat]
Your workflow could have a condition of:
AND(
[_THISROW_BEFORE].[enumlist] <> [_THISROW_AFTER].[enumlist] ,
ISNOTBLANK( [enumlist]
)
And the START expression in your workflow could be:
<<START: SELECT( [Related Tasks][taskId] , IN( [task cat] , [_THISROW].[enumlist] )>>
Simple:
<<START: [Spec Required]>>
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]>> <>
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.
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.
Who For: <<[Who For]>>
Where: <<[Where]>>
Order Title: <<[Order Title]>>
Specs Required: <<[Specs Required]>>
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:
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.
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?
I have no idea. Iโve been unable to understand how your data is structured from reading all of the above.
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.
Who For: <<[Who For]>>
Where: <<[Where]>>
Order Title: <<[Order Title]>>
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
Who For: <<[Who For]>>
Where: <<[Where]>>
Order Title: <<[Order Title]>>
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>>
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
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.
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.
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
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.
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.โ,
Hi Rob,
Please ignore my immediate above post. I am deleting it.
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.
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.
Yes, at least as per my understanding it is so. My suggestion will be to await insights from other community members.
Email PDF Attachment
Attachment Template
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.
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
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]>><> |
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โ
Levent the legend
@Suvrutt_Gurjar
Iโm just a humble developer thatโs all, but thanks for your appreciation anyway.
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
Youโre welcome
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.
Well said!
I was primarily commenting as a sleight jab at my other AppSheet expert colleagues
Maybe one of these days some starting point solution will be created thatโs sufficient enough to catch most of these types of questions.
User | Count |
---|---|
18 | |
14 | |
11 | |
7 | |
4 |