Filter answer in Single Select By Reference Table (Lookup)

Hello,
We have a question Unit that show all units for a category of product. If we are in product, we want to just show the unit of that product.
For example, as indicated on the image below, if we are in product (CodeProduit == 1), we want to show the options having these code (100, 136, 138).
How we can do it in the FILTER?

  1. Question

  2. Reference table TailleUnique

1 Like

I’d say switch it around so that from your excel file you have each option 100, 136, etc and then next to it all of the roster rows you want it to display for. Then something like the below code in the filter box…

(@optioncode==106 && @rowcode.Inlist(1,2,3)
||
(@optioncode==136 && @rowcode.InList(1,3,6,10)
||

etc for all of the options in your question. There’s probably a more efficient way to do it with less code I’m sure…

Thanks!

2 Likes

Thanks you for your suggestion. But i think it will be difficult to do it because because we have many option_code and many rows in the lookup tables. A more dynamic syntax will be better.
Inspiring on your suggestion i have some ideas:

  1. Select all rows in lookup table with condition CodeProduit == 1 or 2 (depending the product)
  2. And select Option where @optionCode is in the column CodeUnit for rows selected in 1

Howe we can translate it in syntax ?

1 Like

Was playing around with the filters - if you want the roster to dictate choices in a question the following code seems to work really well -

@rowcode ==1 & @optioncode.InList(1,2)|| @rowcode ==2 & @optioncode.InList(3,4)

1 Like

How many combinations of PRODUCTS with UNITS are there in your table?

Hi, I’d like to re-visit this problem after some time. I searched the forum and couldn’t find an answer. So it relates to the crop / unit combination. Where we want to restrict the units displayed depending on the type of crop. The crop is the roster occurrence and there are often more than 100 different crops in an Agricultural survey so it makes sense, I think, to use a lookup table to filter the relevant answer options for units.

I’m aware it can be done with a long condition specifying manually the option codes for each crop, however this is very time consuming to setup, mistakes can easily creep in. Additionally these units can be requested to change throughout the process as results from pre-testing etc. So controlling it through a spreadsheet and lookup table would be very nice.

What I was thinking was to have a lookup table where each @rowcode is the crop and then a series of columns for each unit holding a 1 if it should be displayed or a 0 if it should not be for that @rowcode. My programming skills aren’t good enough to convert this type of lookup table to the syntax for the filter option - so hoping somebody could help me out!

Thanks!