Calculate values from extra lookup columns in choices tab

Support
1

1. What is the problem? Be very detailed.
I'm creating a form to collect price data.
I have a list of items in my 'choices' tab. I then have additional columns in my choices tab such as 'quality' and 'unit' that further specify the item being monitored.
I want to place a repeat in the form that will loop through each item and collect data on it.

Within the repeat group, I'm putting a calculation to calculate the item 'label', the item 'quality' and the item 'unit', this information is in a kind of lookup table in the choices tab. This is so I can be very specific about what we're collecting price data on.

I'm kind of seeing it as an alternative to a "pulldata" calculation, so I don't need an external csv.

2. What app or server are you using and on what device and operating system? Include version numbers.
ODK Collect v1.28.4

3. What you have you tried to fix the problem?
I found an article on the forum earlier today where @LN shared a Google Sheet showing how to do this - but the calculation formula wasn't in a repeat group, so I think my syntax needs some help. I never usually write in this style of syntax, so just needing a bit of guidance...

So the formulas I currently have in there are:

instance('item')/root/item[itemindex=${item-counter}]/label
instance('item')/root/item[name=${item}]/quality
instance('item')/root/item[name=${item}]/unit

4. What steps can we take to reproduce the problem?
See attached xlsform
item_info.xlsx (35.0 KB)

5. Anything else we should know or have? If you have a test form or screenshots or logs, attach below.
Thank you for your help!

2

Reverse cascading choice - #2 by LN and Loop over pre-established list - #6 by LN have more about the general strategy.

The first issue with your form is that you're not forcing pyxform to create instances. This is needed for this approach (more here). One simple way to do this in your form is to add true() to the choice_filter column of the stock-item question.

Then you're taking label in item and trying to compare it with name in the following two lookups. I'd recommend using the index for all three expressions.

You're also running into a bug which I've documented at https://github.com/XLSForm/pyxform/issues/490 which means you won't be able to mix and match XPath and ${} notation in this case. You'll have to expand your three queries to:

instance('item')/root/item[itemindex=current()/../item-counter]/label
instance('item')/root/item[itemindex=current()/../item-counter]/quality
instance('item')/root/item[itemindex=current()/../item-counter]/unit