Solved: Multiple choices input (like enum list) for a Ref ...

Cristian_Maria · 02-04-2020 08:24 AM

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

A_Computer_Solu

I was able to find the error, thanks. I named a form in the UX same as I had in the sql and that was the error. Moving forward, I now need to query, what they are are looking for is indeed available and then save the record.

View solution in original post

Cristian_Maria

No one?

Is my question unclear? Do you need extra details?

LeventK

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

Cristian_Maria

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

LeventK

@Cristian_Maria
Can you elaborate?

Cristian_Maria

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

LeventK

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

Cristian_Maria

@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

LeventK

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

Cristian_Maria

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

LeventK

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

Cristian_Maria

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

LeventK

You’re welcome, glad you have solved it

A_Computer_Solu

Need your help here pls.
I have a form which is part of a relationship and a field called routid, which is a dropdown list of items based on the relationship. However, when where i am trying to filter and use the field from the dropdown list, in this case the last line, my virutal column does not work. it is like it is not passing the value selected in the dropdown list. here is the code

filter(“routes”, AND (
( [departdate] = [_thisrow].[departdate] ),
( [DepartTime] = [_thisrow].[Departtime])
([routename] = [_thisrow].[routeid])
))

please help, once i remove that last line the filter works fine. I even tried to use another virtual column to show what is selected automatically and to no avail.

Steve

Missing comma?

Pranav_Subraman

Thanks You! @LeventK

I too had the same issue. Got solved. Could you mark this as the solution for this thread. I guess the solution marked to this thread is different for some other question.

Zmudio

Hello everyone! each app has their own structure, sometimes you find exactly what you need, other times we're force to try harder. this time i got the second. Thank you all, for your comments that really helped me

My case:

Log (Table):

[Type] //EnumList type, Base Type text

[Related Supply]// Ref Is Part of from Supply Table

Services(Table):

Description (Key) Type: text

Supply (Table):

[Id] Type Ref from Table Log

[Description] Ref From Service Table

For column [Service] in Valid If :

In([_thisrow].[Id].[Type],select(Services[Description],[Type]=[_thisrow].[Id].[Type]))

explained by parts:

[_thisrow].[Id].[Type] enumlist in Log Form

select(Services[Description],[Type]=[_thisrow].[Id].[Type] Services List that matches the Enumlist above

by enum list allows to filter the Ref type column in this case [Services], hopes that helps.

Steve

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

LeventK

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

Manish_Jain11

Hello ,

I am facing the same problem . I want value (Label Field) to be displayed and store Key(S) , So I changed this field type to Ref instead of Enumlist. It works fine but I get to choose only one option . When I change the Base Type to Ref, the Dropdown again starts displaying the Keys instead of value and still no multiselect option. What should I be doing ?

Marc_Dillon

This part makes it sound like you have not correctly set the referenced table option. Note that, as Levent already mentioned in the linked post, you have to save the editor before this option even appears.

Regarding the inability to select multiple, I have no idea without further info from you (screenshots and such).

Koichi_Tsuji

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

Cristian_Maria

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

Koichi_Tsuji

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], 😎
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.

siusaikwan

I was able to follow this solution, but it seems that the loading time is quite long, having to compute for each individual rows for its ref rows… Is this what you may have encountered as well @Cristian_Maria?

Cristian_Maria

@siusaikwan I’m sorry, at this point I couldn’t tell you what makes my app load slowly :))

It’s grown quite big and complex since then.

A_Computer_Solu

I felt like an idiot for missing that, but no that did not fix it.

Steve

Please post a screenshot of the configuration screen for this virtual column.

A_Computer_Solu

anyone know how to handle this? Im new to this so not sure what is the best route

Marc_Dillon

More information, in order to solve the issue, has already been requested from you:

Additionally, your initial post doesn’t make tons of sense.

How do you mean by a form that is part of a relationship?

Do you mean that [routeid] is a Ref type column, pointing to the routes table?

How does it not work, it just doesn’t return the correct value, or…?

When you have a large expression that needs troubleshooting, the general best approach is to strip away each part and test things individually. Try to test the FILTER() expression with each individual condition, instead of all 3 in an AND(), and confirm that the returned results are what you expect.

Elaborate on this please.

A_Computer_Solu

Tried that, its only the highlighed one that does not want to work and i am not sure why. The routename is from the database and and the routeid is a dropdown list from the routes table. However, when I select a route from the drowndown and make sure it is equal to routename based on the date and time in the database, it does not return a value. in additon, i tried using a virtual column to see if it will display what i selected, i get nothing also. It is imporatnt to note that the form send data to the mysql database remotely with no problems. just that this last piece of the code is giving me some issues.

Marc_Dillon

If [routeid] is a Ref column, then it will be holding the value of the key column from one record of the Routes table. Is [routename] the key column, or is something else?

Further, if [routeid] is a Ref column, then there’s zero reason to be running this FILTER expression.

A_Computer_Solu

the expression is to query the database and display results in a virtual column. routename is not the key in the database, routeid is the key in the database. What is your suggestion to narrow the result based on the user input?

Marc_Dillon

Display what result?

So, obviously [routename] = [_THISROW].[routeid] will NEVER return a TRUE value, yes? You need to change “[routename]” to whatever is the key column for the routes table.

I still have very little idea what you are doing as you have not answered the majority of the asked question. But given your FILTER() expression, it seems it will just be outputting the exact same thing as what is already in the [routeid] column, and thus is essentially useless.

Steve

Are you trying to be difficult? If so, mission accomplished!

A_Computer_Solu

no, not at all, figured out the error and fixed it. My only question now is how to create a future schedule, or duplicated the record for a period of dates or times. any ideas?

A_Computer_Solu

I was able to find the error, thanks. I named a form in the UX same as I had in the sql and that was the error. Moving forward, I now need to query, what they are are looking for is indeed available and then save the record.

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