Multiple choices input (like enum list) for a Ref type column

I know how to create a ref type, choose input as dropdown, and add “allowed values” with a validif constrain.

My question is basically how to choose the input type for the form view as an enum list (thus allowing me to choose multiple options), while still referencing another table?

The use case is assigning tasks to team members, in case it helps.

So is there such a thing as maybe "reversed references?

The reason for not simply going with the “enum list” column type is because I want to have a “related tasks” under my “team members” table view

No one? :neutral_face:

Is my question unclear? Do you need extra details? :upside_down_face:

@Cristian_Maria
With your Enumlist Column’s structure:


However there’s a small editor glitch here: after you select the REF as type, you shall see the target table dropdown but it doesn’t appear. Just save it, save the editor and then open the column structure again and you will see the target table dropdown to select as a REF table.

1 Like

@LeventK Did everything, selected the “team members” table as referenced, however, after saving, it doesn’t create the virtual columns for the referenced rows, as usual. Any thoughts?

And thank you for your time.

@Cristian_Maria
Can you elaborate?

@LeventK Sure. When choosing “Ref” for a column type, and “dropdown” as input method, the system automatically creates a virtual column in the referenced table, and adds an expression like “REF_ROWS(“crm_projects”, “owner”)” to the formula field

However, if you choose " Enum List" as column type, and “Ref” as base type, (as per your instructions), the virtual column isn’t created any more, therefore the automatic ref view is also absent.

Could this be a bug? My logic says it should create the virtual columns as well, considering the “Ref” base type.

@Cristian_Maria
It’s not a bug…The reference and de-referencing does not work like that. In fact the logic is simple:

Tablename Column Type Ref Table Dependency REF_ROWS Created Under Relationship Parent Table Child Table
A [Test] Ref B isPartOf = FALSE Table B One-2-Many Table A Table B
B [Test1] Ref A isPartOf=TRUE Table A Many-2-One Table B Table A

To create multiple child records thru an Enumlist, you should create a Workflow for your table first:


# WORKFLOW #


Update Event Condition Do This Preset App Id Tablename
ADDS_ONLY TRUE Webhook Appsheet: Add Row Your App Id Your Table Name

For the Body; you need to construct a JSON body to tell AppSheet create those child records with passing JSON parameters similar to this:

{
   "Action": "Add",
   "Properties": {
      "Locale": "en-US",
      "Location": "47.623098, -122.330184",
      "Timezone": "Pacific Standard Time"
   },
  "Rows": [
    <<Start: Filter(OrderDetails, true)>>
    {
      "Name": "<<Name>>",
      "Qty": "<<[Qty]>>",
      "Price": "<<[Price]>>",
      "Total": "<<[Qty]*[Price]>>",
      "Sales Tax": "<<[Qty]*[Price]*0.085>>",
      "Email": "<<[Email]>>",
      "Product Image": "<<[Product Image]>>"
    },
    <<End>>
  ]
}

It’s a bit tricky work and though it will create the child records, you cannot create a direct REF between the parent record and the created child records. Provided you want to establish the REF between those records, you need to set a Reverse REF (explained in Row#2 above) between your tables. That means when you are creating an Owner, you will notice a NEW button at the end of the form, where you will be able to create multiple CRM Projects for that owner.


To maintain this;
Create an [Owner] column in the CRM Projects table, set its type to REF, select Owners table as the reference table and set isPartOf to ON

Please take a look at my Community Sample App below. In the HOME click on Phil Waite. There is a Product table and whilst creating a product record, you can create multiple Price Bands for a particular product. Hope it will make sense on what you are trying to create.

2 Likes

Enum and EnumList columns with a base type of Ref do not produce the reverse reference virtual columns.

1 Like

Thanks for your reminder @Steve, forgot to specifically mention that…Appreciate your input

1 Like

Do you expect max number of selected persons in your Enumlist?

@tsuji_koichi The EnumList is populated from a column in another table, containing only 9 rows. It is unlikely to select all entries, but very likely to select multiple.

@LeventK
Thank you so much for putting together such a comprehensive solution, I’m sure it will help me for other modules of my CRM project.

However, considering the purpose of my post was to simply display a list of tasks assigned to a certain team member (in the detail view of a row), I’m wondering if this couldn’t be accomplished with a virtual column added to the table TEAM.

I wouldn’t know what formula to write so that the column computes just the tasks that have the owner coinciding with the displayed detail view entry; I hope it makes any sense

Sure, then this might not be perfect solutions, but I believe it partially achieve your goal.

So now you have enumlist type column, let me assume max 9 items.

Then create 9 virtual column with expression

Index([EnumListColumn], 1)
Index([EnumListColumn], 2)
Index([EnumListColumn], 3)


Index([EnumListColumn], 8)
Index([EnumListColumn], 9)

This virtual column is going to get each item out of Enumlist value, meaning splitting Enumlist value and push into a column as single value, representing each column. Beauty of virtual column will dynamically select the value regardless of the number of items your users select.
At the same time, set the vitrual column type to REF and set up target ref table which stands as parent, and isPartOf is enabled. This will create ref from each enumlist item to parent table through this VC.
I know it may be 1 item, 2 items, the count of item varies. So put ShowIF constrain to all those VC as well, if isnotblak([_this]) then show

I have not tested this with my app, but hypothetically it would work.

1 Like

@Cristian_Maria
Construct a Virtual Column with an AppFormula like this. Please make suitable changes in below expression as I have no idea about your column schema and app structure.

SELECT(
	TASKS[KEY],
	IN(
		[MEMBERS],[_THISROW].[ENUMLIST]
	)
)

This VC will be displayed as an inline view in your Detail View.

@LeventK I am sorry, I couldn’t extrapolate it all, here is the table/column structure

CRM_TASKS has columns:
[task id] //key column, text type
[assigned to] //EnumList type, Base Type is Ref, Referenced table name is CRM_TEAM
[other columns] //not impprtant

CRM_TEAM has columns:
[name] //key column, name type
[other columns] //not impprtant

I want the virtual column to be displayed under the detailed view of a [name]

Here is what I replaced, but I don’t understand which column should be [enum list]

SELECT(
Crm_tasks[task id],
IN(
[assigned to],[_THISROW].[EnumList???]
)
)

@Cristian_Maria
Try with this: Construct a VC in your CRM_TEAM table with this AppFormula

SELECT(
	CRM_TASKS[task id],
	IN(
		[_THISROW].[name],[assigned to]
	)
)

AWESOME!!! thank you so much, that’s EXACTLY what I’m looking for !!!

You’re welcome, glad you have solved it