Does pulldata() or instance lookup accept calculated field in filter expression?

Support
,
1

**1. What is the issue? Please be detailed.

Pulldata and instance lookup, both failed to return value when:

${participant_name} is a calculated value eg. concat()
**
pulldata('participants', 'phone_number', 'name', ${participant_name})

instance('participants')/root/item[name= ${participant_name}]/phone_number

**2. What steps can we take to reproduce this issue?

Given** participant_name with calculation concat($first_name}, ' ',${last_name}) and trigger $first_name},${last_name}**

Test 1: Lookup field1, with same trigger** $first_name},${last_name}
pulldata('participants', 'phone_number', 'name', ${participant_name})

Test 2: Lookup field2, with same trigger $first_name},${last_name}
instance('participants')/root/item[name= ${participant_name}]/phone_number

Test 3: Lookup field3, with same trigger $first_name},${last_name}
pulldata('participants', 'phone_number', 'name', concat($first_name}, ' ',${last_name}) )

Test 4: Lookup field4, with same trigger $first_name},${last_name}
instance('participants')/root/item[name= concat($first_name}, ' ',${last_name})]/phone_number

3. What have you tried to fix the issue?
Limited knowledge from ODK Docs AI, documentation and forum
If lookup based on select_one :
The true() in the choice_filter column for the select_one in the example above is necessary to be able to look up participants' phone numbers. This is currently needed to overcome a pyxform bug.
https://docs.getodk.org/form-logic/#dynamic-defaults-from-form-data

Concat can be used as second parameter of pulldata, supported in odk but not enketo.
Strange Enketo Error ( Form Logic error ) - Support - ODK Forum

4. Upload any forms or screenshots you can share publicly below.

2

Thanks. Would be great for further testing, if you could provide a short XLSForm example, please.

3

Where exactly are your lookups failing?

I created a short form and included some name values that had spaces between first and last name and some with an underscore, and two separate triggered instance lookups with space & underscore concats, and both worked for the lookups that matched - this covers Test 4. I also added a separate concat and used that field value for the lookup which covers Test 2. All worked in WF & Enketo.

I didn't bother with pulldata tests as I don't use it :grin:

Note that having name values with a space in them (as indicated by your tests that are looking up against name with a concat that has a ' ') is not a great idea and will cause select_multiple issues, better to use a different delimiter like _ or ·

lookupconcat.xlsx (84.0 KB)

4

Thanks @ahblake for the mockup example.

I slightly increase the complexity, and test it with webform and odk:

Adding a title with select_one, concat the first and last name with ' | ' delimeter and each name might contains space and hyphen:

If you test in the following sequence:
the first three failed to return value

| abc-48-SL | 5–10 mL/L | sun-flower |
|----|
| abc-48-SL | 5–10 mLL | sun-flower |
| abc-48-SL | 5–10mLL | sun-flower

after removing the hyphen from 5-10mLL, the instance lookup only functioning well
|

| abc-48-SL | 510mLL | sun-flower |
|----|
| abc-48-SL | 510mLL | sunflower |

lookupconcat.xlsx (82.8 KB)

5

Your name values sometimes have a – but others have a -, and using a keyboard input you will only get -. different unicode values

eg this one has both: abc-48-SL | 5–10mLL | sun-flower
If I copy/paste a – and type the rest, the lookup works fine.

Edit: if you have no control over name values OR inputs being one or the other, you could add a translate to make them all the same. This won't solve entering Sunflower vs sunflower, but you can also use translate to do the same thing with case. (incomplete example: translate(string , '–ABC...Z' , '-abc...z')

instance('participants')/root/item[translate(name,'–','-') = translate(concat(${title},' | ',${first},' | ',${last}),'–','-')]/phone