Valid If expression for SELECT Ref list AND column comparison

[Shaun_Stringer]

Hi,

So I am using an ENUM base Ref and Valid_If expression rather than a REF type column to provide the specific list of items I want people to choose from, but I would also like to use the Valid_If expression to make the selection invalid if it does not match a previous column in the form. Can I do both?

Ex: SELECT(RefTable[Key], [Column] = "xyz", FALSE)    AND    [Previous Column] = [_THIS]

If the choice from the provided list does not match the value from the previous column, this should be marked invalid and show the Invalid Value Error. If it does match, all is well.

@Steve - You have frequently solved my problems. Help from anyone is appreciated.

[VOLODYMYR_TYROL]

Hello, if I'm understanding right you would like to create dependent-dropdown

Please read this article https://help.appsheet.com/en/articles/961554-dependent-dropdown 

https://support.google.com/appsheet/answer/10107665?hl=en&ref_topic=10102053 

[Shaun_Stringer]

This seems on the right track. I cannot create a dependent dropdown in the way they did in the video as I do not have two steps in which to perform the filters. The IN() expression has got me closer, but not there yet. 

How would I write the Valid_If IN() expression where I am selecting a list from Table A - SELECT(TableA[Key], condition, TRUE) and also requiring [Column A] = [_THIS] in Table B. 

One of my closer attempts ==>

IN([_THISROW].[Column A]=[_THIS],
SELECT(TableA[Key], Condition, TRUE))

In this case, the list shown is correct, when [Column A] does not equal [_THIS] it correctly shows as invalid, however, it is also invalid when Column A = [_THIS], which is a problem.

[SkrOYC]

I think you shouldn't use Enum basetype Ref unless you know what you are doing. I mean, you need a real reason for it and the need for a custom list is not one of them, Ref columns can also be used when a custom list of allowed values is needed.

I think you should separate what you expect to hapend clearly. If you share your tables schema I could suggest a more realistic expression

[Shaun_Stringer]

Thanks. I have used EnumList base Ref in the past when I needed to use an ORDERBY(SELECT()) expression and choose multiple options from that list. It seemed to work well in that situation.

I am happy to set the column as a ref in this case instead of an enum base ref, however, I am not sure how this will change the Valid_If expression. I cannot share the table schema at this time but I can try and spell it out more clearly.

Table A, Column 1 = List of items to reference

Table B = Form I am filling out, where Columns 2 and 3 are both Ref columns to Table A, Column 1. The idea is to choose a value from the filtered Ref list in Column 2 and then repeat the process in Column 3. If the choice in Column 3 does not match the choice made in Column 2, I would like it to be invalid and show the invalid message. However, ff the choice in column 3  does match the choice in Column 2, then the user can continue on with the column being valid.

Both formulas on their own work. I am just having trouble writing a formula that allows them to work together.

Formula 1: [_THIS] = [Column 2]

Formula 2: SELECT(TableA[Key], condition, TRUE)

Sorry that this is all I can provide. 

[VOLODYMYR_TYROL]

you are on the right track, like @SkrOYC mentioned it will be easier to explain 

 If you share your table's schema we could suggest a more realistic expression

[VOLODYMYR_TYROL]

I'm not sure you need to use ref type 

[Steve]

I don't understand your condition. The second condition, [Previous Column] = [_THIS], renders the first condition entirely irrelevant. That second condition says this value must be the same as the previous column, so there is exactly one valid choice. Perhaps you haven't fully explained your needs?

[Shaun_Stringer]

I have found that users tend to accidentally pick the wrong item from a list quite often. The goal with having them select from the list twice and requiring these choices to match just confirms their original choice and has significantly reduced the edits I need to make on the backend.

I just need to show this referenced list (which gets updated by others from time to time) twice and require the choices to match. 

[VOLODYMYR_TYROL]

Would you like to do something like that?

[Shaun_Stringer]

I appreciate all the help and I guess I am not explaining well. That is essentially what I had originally, where the Valid_If expression for Column B was [Column A] = [_THIS]. However, I also need to show only a select group of Items from the Items List.

Items	Category
1	A
2	A
3	A
4	B
5	B
6	B
7	C
8	C
9	C

In this case, say I only wanted to show Items in Category A in the dropdown. I could do that with the Valid_If expression SELECT(TableA[Items], [Category] = "A", TRUE). I need the user to only see Category A items in the Column A and B dropdowns, and I need their choices to match, just like in your screenshots.

[VOLODYMYR_TYROL · Accepted solution]

It's was difficult but, i find it and it's work like you asked 

 

[Shaun_Stringer]

Thank you! I was hoping it would be something simple. A suggested values expression is all I needed.