nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Check if value is in a table and return a different value

LindseyStone

I'm trying to accomplish a calculation that I'm not sure how to do or if possible. I think I should be using the If statement and pull data but not sure how to put it together. I'm trying to determine if the Customer Address that was filled out in the Survey is contained as a record in the Account Address field of a hosted table. If it is, then I want it to return Y, if not then return N.

This is what I got so far but it doesn't update the attribute on the field to Y or N.

if(pulldata("@layer", "getValue", "attributes.acct_address", "https://.../FeatureServer/3", ${cust_address}), 'Y', 'N')

Find more posts tagged with

Accepted answers

LindseyStone

@Neal_t_k

Your idea of the helper field worked! I had add a little extra code into my pull data layer that wasn't there because I'm comparing text fields and I was missing the fields to compare for the getValue syntax. So I'm listing it out here in case anyone else needs it.

calculation in helper field called med_val:

pulldata("@layer", "getValue", "attributes.acct_address", "https://...../FeatureServer/3", concat("acct_address='",${cust_address}, "'"))

calculation in field retaining my Y/N values

if(${cust_address} = ${med_val}, 'Y', 'N')

Also note for anyone utilizing Addresses...my Cust Address is using an Address Locator and my Acct Address in the table has the address typed exactly as the result of the Address Locator so they will match exactly and provide a correct answer of the Y/N.

All comments

Neal_t_k

Depending on where you are deploying, you may consider pulling out the concat() function into a second helper field. Sometime it works fine and sometimes it breaks till you pull it outside the pulldata().

LindseyStone

@Neal_t_k

calculation in helper field called med_val:

pulldata("@layer", "getValue", "attributes.acct_address", "https://...../FeatureServer/3", concat("acct_address='",${cust_address}, "'"))

calculation in field retaining my Y/N values

if(${cust_address} = ${med_val}, 'Y', 'N')

Neal_t_k

Yes, set the appearance to hidden and bind::esri:fieldType to null, then it just exists in the survey and captured in the data. I suggest the helper field as a starting as sometimes complex calculation will fail with the pulldata calculation. But If you get that to work with the helper field, then you could try to consolidate it to just the one question something like:

If (pulldata("@layer", "getValue", "attributes.acct_address", "https://.../FeatureServer/3", ${cust_address})!='','Y','N')

If (string-length(pulldata("@layer", "getValue", "attributes.acct_address", "https://.../FeatureServer/3", ${cust_address}))>0,'Y','N')

LindseyStone

@Neal_t_k , Thanks for the reply...I should have added that bit of information on the Address validation as I know that an end user will fill it out a billion different ways. I have them selecting their address by utilizing our in house Address locator. Then the Customer Address fields is then auto-calculated off the Address they selected to fill the address for the data table. The Customer Address field is recording the address record. Then in my hosted table, I have the addresses entered, exactly as it would appear in my Address Locator, so what is selected and what is in the table will match exactly.

So would the helper field just be a field that I add to the survey that is hidden and doesn't keep the data in it, after submitted?

Neal_t_k

Is the user filled out Customer Address question standardized in any way? If it is open text entry, you will most likely run into issues where the address entered does not match exactly what is in the hosted table.

You may want to consider some other options like maybe a search autocomplete appearance which would pull from your hosted table, This way you could ensure the address is contained and don't have to validate what they filled in.

https://community.esri.com/t5/arcgis-survey123-blog/dynamic-choice-lists-using-search-appearance/ba-p/1060838

You could have a geocode appearance to your address question. Then if you have to validate, the response maybe a little more standardized.

https://community.esri.com/t5/arcgis-survey123-blog/survey123-tricks-of-the-trade-addresses-and-the/ba-p/1099132

If you still want to go the direction of having a user provide an answer and then validate, you probably need a helper field with the pulldata first and then use the if statement.

AddressVal: Calc = pulldata("@layer", "getValue", "attributes.acct_address", "https://.../FeatureServer/3", ${cust_address})

Then if statement separate, If(${cust_address} = ${AddressVal}, 'Y', 'N')

However the user provided address has to match exactly or you will get 'N'. Is there anything else the user can provide for identification in the table like some sort of user ID to match against instead of open text?

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.