Using lookup table to filter options

Syntax
#1

I am trying to filter the weight descriptions for food items captured by a household using a lookup table. My lookup table uses fooditem are in the rowcode field and I have a weight category
field. I have over 800 food items from the COICOP.

I have read the lookup table article and forum answers but I am not understanding how to write this filter code

the current example code I have is

wgt_fil[@rowcode].wgt_lock == 1 && @optioncode.InRange(1,7) ||
wgt_fil[@rowcode].wgt_lock == 2 && @optioncode.InRange(8,11)

I am not understanding how to associate the single select food_item codes to the weight_type using rowcode from the lookup table?

Any suggestions ?
I have attached a

image
image776×134 6.31 KB

#2

Dear @neville_csoit, you can create lookup table where you can set optioncode as rowcode.
This need for quick access to lookup table data by optioncode.

In this case you can use it in filter
wgt_fil[@optioncode].wgt_lock == 1
this expression will be run for each option, and in result you can see only options in column “wgt_lock” equals to 1.

#3

Thanks for your reply,
I am not understanding you answer for my situation, I don’t think I explained my situation correctly. I have 21 options that repeat for different food items.

So my lookup table had the itemcode for food item as rowcode
112100

so I have weighted measures (Kgs, gms, llbs ounces) and other items which use the weight measures plus things like bundles or only for the item.

So I am trying to limit the weight measure for each food item

I have

#4

Sorry was editing my reply and it posted it
I am not seeing how spuv’s answer will work for me.

My proposed lookup table has the food item code in rowcode and the weight filter lock column 1
food label white flour rowcode 111 and weight code 1 would refer to option codes kgs, gms, llbs, Ozs
rowcode wgt_lck
111 1
121 2
where 121 is bottled water for example.so its option codes would be L, mls, Gallon etc
as shown below

image
image813×421 19.6 KB

image

Thanks Neville

#5

My code works,

I had to change the code to below
Wgt_fil[(int)food_item].wgt_lock == 1 && @optioncode.InRange(1,7) ||
Wgt_fil[(int)food_item].wgt_lock == 2 & @optioncode.InRange(8,11) ||
Wgt_fil[(int)food_item].wgt_lock == 3 & @optioncode.InList(8,9,10,11,21) ||
Wgt_fil[(int)food_item].wgt_lock == 4 & @optioncode.InRange(20,21) ||
Wgt_fil[(int)food_item].wgt_lock == 5 & @optioncode.InList(12,21) ||
Wgt_fil[(int)food_item].wgt_lock == 6 & @optioncode.InList(21) ||
Wgt_fil[(int)food_item].wgt_lock == 7 & @optioncode.InList(8,9,10,11,17,21)

I just have to get the options and weight codes for all my items.

Thanks for all the help.
HAPPY NEW YEAR COMMUNITY
Neville

#6

Hello @neville_csoit ,

congratulations on figuring this out. It’s hard for others to advise here, since whatever the “weight filter lock column” is, is probably very specific to your survey.

Still note that the use of the “&”-operator is almost certainly wrong in Survey Solutions in general and here in particular. You should receive a warning regarding this in the Designer when you compile the questionnaire with such a condition.

Also, consider simplifying the whole expression that you’ve posted to a single equality check by using a second lookup table.

#7

Hello Sergiy,

I don’t understand how I would use the second lookup tablet. What would be the fields would be in it?

and what would the code look like ?

Regards