SELECT with IN

[redhothat]

Hi. I have an ENUMLIST field in table A that is designed to enable the user to 'tag' multiple records from table B. That is working fine but in table B, I want to be able to show all the records in Table A that have been tagged for that record as a list reference (like REF_ROWS but you can't use that when comparing a text field with a list).

The solution that I thought would work is 

select(TableA[Field),in(TableB(TextField],TableB[ENUMLISTField)]))

The thinking being that this would bring back all records of Table A where the contents of TableB[TextField] match any of the items in the TableB[ENUMLISTField]. It doesn't work. If it finds the contents of the Table B text field in Table A it brings back all the records. Not sure what is wrong and can't find any other solutions in the help. Hope the above makes sense.

Any assistance is welcome. Thanks.

[jyothis_m]

Try FILTER("Table A",IN([Table_B_KeyColumn],[Table_A_EnumListRef_Column]))

[redhothat]

Thank you. Have tried that and got the same result. i.e. where it finds the table B value in table A, it returns all the records of table A.

[redhothat]

Just a quick follow up to that. I noticed that you had EnumList_Ref column for Table A. I have it as a text field with Suggested Values from Table B. I've tried changing it to a Ref field and it errors. "Unable to update row in table A - Invalid request provided to Appsheet database. Maybe if I find a solution for that my original problem will be solved too?

[jyothis_m]

In Table A Column you need to select multiple items right? Then REF cannot be used in that case. You can use ENUMLIST type with base as REF

[redhothat]

Thank you. This is the setup in the database.

This is the setup in the app.

with the Valid If clause added. Key Stakeholders is Table B.

I'm still getting the error when I try to add data to that field. i.e. "Unable to update row in table A - Invalid request provided to Appsheet database". It's like it's getting a data clash.

[jyothis_m]

I have not used Appsheet Database that much. But can you try changing the column name to something simple like STAKEHOLDER (better to avoid special characters like bracket in column names) and correct the expressions accordingly

[redhothat]

Hi. I changed the name of the field but no change unfortunately. I deduced that there was an issue with the 'Valid If' statement. Found the solution on another thread - it had to be 'filter("Stakeholders",True)' to work with an Appsheet database. So, I can now select from the list and it will save.

Frustratingly however, this has not fixed the original problem. No records are coming up on the filter function on table B. I suspect there is something special required because it's an appsheet database. I built a test model to see if I got the same result and it is returning all records in table A for every record in table B which is a different result to my app, even though they both appear to be set up the same. Very confusing. Will keep working at it.

[redhothat]

Have just looked at the database relationships and the tables are not connected even though I'm using a REF. So, at least I know where the problem is. 

[scott192]

On Table B create a formula field

SELECT(TableA[KeyIDField],IN([_THISROW].[TableBID],[TableAEnumListField]))

 That should give you a list of all table A records where Table B record is in the EnumList

[redhothat]

Thank you for the suggestion. I have created a simple test app to see if I can get it to work. There are two tables - Days & Jobs. The Jobs table has the ENUMLIST REF field which refers to the days table.

I have entered the formula as per your suggestion - Select(Jobs[Row ID],in([_THISROW].[Day],Jobs[Day]))

Is this correct?

I have entered two records into the Jobs table. One with Tuesday selected, one with Wednesday and Thursday selected. The formula returns both records for Tuesday, Wednesday and Thursday. So, it seems to be returning all rows if it finds that Day in any record.

Any help is welcome.

[scott192]

No, this is not correct.  You have mistakenly referenced the whole of your A table by using the Jobs[Day] expression rather than just [Day]

It should be

Select(Jobs[Row ID],in([_THISROW].[Day],[Day]))

[redhothat]

Thank you. Have made the change but not getting any records coming up now. Screen shots below.

[scott192 · Accepted solution]

Everything you have done is perfectly correct now, however there may be a misunderstanding in what data is actually being stored within your Enumlist field on your Jobs table.

So can you do a screenshot from the spreadsheet on the Jobs table that clearly shows your [Day] column with some of enumlist data that is being stored in that column.

It would also help to know which column is your Key column on your Day table?

Actually, I have taken a closer look at your screenshot and can see that you are not using [Day] on your Days table as a key, you are using [Row ID]. So....

Select(Jobs[Row ID],in([_THISROW].[Row ID],[Day]))

The above is now the correct expression to refer to your Key field as the comparator with the list of Key Fields stored in the Enumlist[Day]

[redhothat]

That's worked!! Amazing! I have been trying to fix this for ages and it's the final piece of the app that I needed to get it working. A huge thank you to you Scott, and thank goodness there are people like you out there. I wish I could return the favour. All the best. Chris.