Problem with where clause in pulldata("@layer"...)

[ZenMasterZeke]

Having trouble with the where clause in a pulldata("@layer"...) calculation. I have two surveys: one where a user is given a user name (field name user_name) for logging in and enters their information such as name, address, etc. Records in the first form will be unique users.

The second form is where the user logs in with the same user name (field name user_login) and enters resources they have. There can be multiple records per user in this form.

I want to pull a separate field from the first form (organization_name) where the username from the second form matches the username from the first form. (This is basically creating related tables/joins, but the Web Designer doesn't support them, afaik.) In other words, return fom1.organization_name where form1.user_name = form2.user_login.

My calculation is

pulldata("@layer", "getValue", "attributes.name", "https://services.../FeatureServer/0", "user_name=user_login")

But whenever I run this, I get an error that there is no survey element named ${user_name}. I've tried using the ${} format on one field, both, the other, switching the order of the fields, etc. Is there a way to do this? Thanks!

[Neal_t_k · Accepted solution]

try putting the where clause in a helper field 

whcl = concat ("user_login = '",${user_name},"'")

pulldata("@layer", "getValue", "attributes.name", "https://services.../FeatureServer/0", ${whcl})

user_login = the field in the feature layer you are querying

user_name =  the name of the question in the form you are using to match.

[ZenMasterZeke]

Thanks @Neal_t_k . I'm not getting an error anymore, but it's not returning anything either. The where clause - 

type    name    label           calculation
hidden	whcl	Where clause   	concat ("user_name = '",${user_login},"'")

returns: user_name = 'ZenMasterZeke', which seems correct. The pulldata field is

type  name  label  calculation
text  org   org    pulldata("@layer", "getValue", "attributes.name", "https://services8.arcgis.com/IGysY44dU9lNnAZQ...FeatureServer",  ${whcl})

but nothing is returned. I've verified that name is the correct attribute,  the feature service id is correct, and user_name in the @layer feature layer = user_login in the querying layer. The full section of the xlsform is

type            name            label          calculation
username	user_login	Login name																		hidden	        whcl	        Where clause   concat("user_name = '",${user_login},"'")
text	        org	        Org	       pulldata("@layer", "getValue", "attributes.name", "https://services8.arcgis.com/IGysY44dU9lNnAZQ.../FeatureServer",  ${whcl})	
note		               ${whcl}									

Can't figure out what's wrong 😕

[Neal_t_k]

@ZenMasterZeke 

"https://services8.arcgis.com/IGysY44dU9lNnAZQ/arcgis/rest/services/service_007055ac6d3d440e8914455a7.../0"  

I think you need the sub layer ID in there as well. 

[ZenMasterZeke]

@Neal_t_k ,

Thanks, but that didn't work either

Edit -hmm, user (me) error. Survey is on one portal, I was using layer from a different one. Duh... Thanks though!

[Neal_t_k]

When you look at the service is query enabled? 

[ZenMasterZeke]

I will check that as well.