Filter ref column results

[KenPollard]

I am using --- FILTER(ConfigRefTable, IN([Truck_Tractor_Trailer], LIST("3","5"))) --- in the "valid if" formula to select certain rows from a ref column. It works well for me so far. I would like to be able to further sort the same ref column result to only show the result from the list"3" if the adjacent Enum Column returns 1, and sort by list "5" if the enum result is "2". Tried a few options, but, 

I want to say 

If [#of trailers}=1, FILTER(ConfigRefTable, IN([Truck_Tractor_Trailer], LIST("3"))), or If [#of trailers}=2, FILTER(ConfigRefTable, IN([Truck_Tractor_Trailer], LIST("5")))

But not sure how to tell that to appsheet.

Thanks in advance again. 

[Suvrutt_Gurjar · Accepted solution]

I think you are on the right track, Your expression could be using IFS()

IFS ([#of trailers]=1, FILTER(ConfigRefTable, IN([Truck_Tractor_Trailer], LIST("3"))),

       [#of trailers]=2, FILTER(ConfigRefTable, IN([Truck_Tractor_Trailer], LIST("5")))

      )

Please take a look at -

https://help.appsheet.com/en/articles/2355961-ifs

Another variation could be 

FILTER(ConfigRefTable,

             IN([Truck_Tractor_Trailer],

                       IFS([#of trailers]=1, LIST("3"), [#of trailers]=2, LIST("5")

                             )

                  )

            )

[Suvrutt_Gurjar · Accepted solution]

I think you are on the right track, Your expression could be using IFS()

IFS ([#of trailers]=1, FILTER(ConfigRefTable, IN([Truck_Tractor_Trailer], LIST("3"))),

       [#of trailers]=2, FILTER(ConfigRefTable, IN([Truck_Tractor_Trailer], LIST("5")))

      )

Please take a look at -

https://help.appsheet.com/en/articles/2355961-ifs

Another variation could be 

FILTER(ConfigRefTable,

             IN([Truck_Tractor_Trailer],

                       IFS([#of trailers]=1, LIST("3"), [#of trailers]=2, LIST("5")

                             )

                  )

            )

[KenPollard]

Thank you for your suggestions, I was unaware of what IFS does, I didn't realize it wa abasically a plural IF. At least , Now I thing it is anyway.....

[Joseph_Seddik · Accepted solution]

Hello Ken,

First, please take note that IN() is used to check whether a particular value exists in a list of values. If you are checking only one value, then you should instead use the equality operator '='. 

Your expression should be: 

SWITCH([#of trailers], 
  1, FILTER(ConfigRefTable, [Truck_Tractor_Trailer] = 3), 
  2, FILTER(ConfigRefTable, [Truck_Tractor_Trailer] = 5), 
  FILTER(ConfigRefTable, IN([Truck_Tractor_Trailer], LIST("3", "5")))
)

This expression checks the value of the "#of trailers" column. If this value is 1, it will return the rows in the ConfigRefTable where the value of "Truck_Tractor_Trailer" is 3. If the value is 2 then rows where TTT is 5 is returned. 

Here you should also accommodate for the case that the value "#of trailers" is neither 1 nor 2. What would you like to have in this case? This is the third line of the SWITCH() statement, where I put the expression that will return all rows with TTT equals 3 or 5. Instead, if in this case you don't want any rows to be returned, then you can replace the third line by an empty list: LIST(). 

[KenPollard]

Thanks for the reply as well. Always good to have several options to do things. And thanks for the explanation and suggestion for other options  in the expression too. I am sure this will be very useful

[Joseph_Seddik]

@Suvrutt_Gurjar Didn't see your update 😫

[Suvrutt_Gurjar]

Hey @Joseph_Seddik  Absolutely no problem. I think when we start typing responses, we do not know who else has replied. Each reply adds its own value.. Your reply has useful insights. 

So more is good. 👍