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?

[Griff]

Sounds like it would be a much used template if somebody were to build it. Great idea!

[Griff]

I have a new question that is really an extension of this one. Before I spend time testing an extended requirement, I think I would be able to do this unless I create child and grandchild tables but I am after an opinion.

Let’s say I create a table called Tasks with the following variables: [Task Cat] and [Task Detail] that I want to link to my Orders table in a similar manner to my original question. The diffference is that [Task Cat] is not unique, each there may be several records with the same [Task Cat] with each having an associated [Task Detail] is different. I had planned to use [TaskId] as my Key field based on UniqueId(). e.g.
Task Cat Task Detail TaskId
CatA Detail 1 abc
CatA Detail 2 gef
CatB Detail A hgc

In this scenario I would like to create an ENUMLIST in my Orders table that enables the user to select from a list of [Task Cat]s. From that I would like to create a report that lists each selected [Task Cat] and its associated [Task Details].

The way I see it, I can force the ENUMLIST to display Unique [Task Cat]s by some code in the ValidIf constraint. However if I understand it correctly, my ENUMLIST field will need to store the Key field from the related Tasks table, however my ENUMLIST will not display the Key field, just a unique Task Cat. As a result I don’t think I will be able to reverse ref back to the multiple matching [Task Cat]'s in the Tasks table to create my report.

I’ve tried really to explain this as best I could so I hope it is clear. Comments would be appreciated, before I spend time trying to make it happen.

[Marc_Dillon · Accepted 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?

Griff:

However if I understand it correctly, my ENUMLIST field will need to store the Key field

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

[Griff]

Thanks Marc. Your assumption is correct. Now I know that will work I will start work on it. Appreciate your help

[Griff]

Not sure if I should be raising a new question - if so please let me know.
Confirming the structure and workflow

A user would open the detail view of some Order. Then, going into a Form view, the user would select one or more Task Categories ([Task Cat]) from the EnumList field. At which point an email would go out listing the Tasks in that Order which match the Categories.

My Tasks table has a Key field called [TaskId] based on uniqueId(), so it’s effectively meaningless from a user perspective. The field I want to have in my EnumList in the Orders table is [Task Cat] from the Tasks table but [Task Cat] is not unique in the Tasks table - there may be several identical Task Cat’s

In my Orders table I have a field called [Related Tasks] (named that way to better align with the suggestion that has been made). It is an EnumList field with a Base Type of Ref referencing the Tasks table. Using your suggested approach I have tried to use a Valid If statement of [Tasks][Task Step] and made the EnumList field a Ref Base Type referenencing the table Tasks. And as suggested, the EnumList has a Valid if set to [Related Tasks][Task Cat]. The formula is construed as Valid, however the entire field does not display on the form and if I run a Test on the ValidIf it comes up blank for each field. I assume that’s because the Valid If is looking for a reverse reference on itself which will never exist.

I realise that naming the EnumList [Related Tasks] is not a standard approach but I did it to test the suggestion. Should I, rather, name that field differently and should the reference to [Related Tasks][Task Cat] instead be to something else?

[LeventK]

Griff:

Then, going into a Form view, the user would select one or more Task Categories ([Task Cat]) from the EnumList field.

@Griff
What Form view is that? A new one or is the user editing that order (the order that they are seeing in the detail view)

[Marc_Dillon]

Griff:

In my Orders table I have a field called [Related Tasks] (named that way to better align with the suggestion that has been made). It is an EnumList field with a Base Type of Ref referencing the Tasks table.

This is wrong. [Related Tasks] should be a REF_ROWS() virtual column, auto-generated via the Ref type column in the Tasks table.

Reading further it sounds like perhaps [Tasks] is that auto-generated REF_ROWS() VC, that you have just renamed?

If that is the case, the valid_if for the user-selectable task category EnumList would be [Tasks][Task Cat]. This EnumList should NOT be a base type Ref.

And in general, your descriptions of the columns is a bit hard to follow, and would greatly benefit from a screenshots of the column definitions page in the editor.

[Griff]

Apologies for the delay in completing this but we’ve had a stressful week at work.

I have created an image that contains a collection of what I currently have in place.

1. My Tasks table looks like the top left of the image. There are more columns but two are enough for the purpose of getting this working

2. The Key field is a Text Field called TaskId with a default value of UniqueId(), not shown in the image

3 The task table has a field called Task Cat which is a Text field that may have multiple repeats. This is shown at the top of the Imae

4. The table has another field called Existing shown in image.

5 In the Orders table I have an Enumlist field called Tasks that is a REF field referenced to the Tasks table. The ValidIf condition is intended to ensure duplicate value Task Cats are not displayed. The intention is for users to choose which Task Cats are applicable to the entry.

I then have a WorkFlow that sends an email based on a template that looks like the one below. I need it to show all the Task Cats and associated Existings

I have been trying to test it this weekend but ran inother problem that has now been referred to support. At this stage the EnumList selections works but the EnumList entry form has yellow triangles. My Select statement is not yet working either. I would like to persevere but I’d like to get past the triangle problem and get the Select working. Any suggestions welcome

.

Test

Orders

Who For: <<[Who For]>>

Where: <<[Where]>>

Order Title: <<[Order Title]>>

Tasks

Task Cat	Day Sheet Id
<<START: SELECT( [Tasks][TaskId] , IN( [Task Cat] , [_THISROW].[Tasks] ))>><<[Task Cat]>>	<<[Existing]>><>