Arcade expression "if UPI exist"

[Nicole_Ueberschär]

I have a parcel layer and a point layer with surveys coming from Survey123. I would like to do the symbology of the parcel layer based on the surveys that have been filled. Both layers have the attribute UPI in common. So mainly the arcade expression should check if the UPI exists in the survey layer and if yes then return "yes". In Python I would use something like "if UPI exist in survey then update field survey_done for the row of that UPI parcel layer with yes". 

Then I could use the expression result to color my parcels accordingly. 

How can I do this in Arcade? (If possible)

Thanks in advance!

[XanderBakker]

To do this you would have to create a sql query anmd filter the data and count the result. It will look something like this:

// read the UPI of the current polygon feature
var upi = $feature.UPI;

// create the SQL (use correct field name)
var sql = "UPI = '" + upi + "'";

// access the survey data
var surveys = FeatureSetByName($map, "Exact name of survey layer in map");

// query the surveys on UPI
var surveys_in_parcel = Filter(surveys, sql);

// count the result
var cnt = Count(surveys_in_parcel);

// return the result
if (cnt > 0) {
    return "Yes";
} else {
    return "No";
}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[Nicole_Ueberschär]

Thanks a lot Xander! Great approach. I don't seem to be able to use that new expression field for the symbology, is that correct?

When I look in the table the field does not show yes or no but only "show" as a link. Is there a way I can "force" the value to be populated? 

[XanderBakker]

In the symbology profile you cannot make use of the FeatureSetBy* functions. This is due to performance reasons. You can however create a new field and use the Arcade expression in a field calculation. Note that this is not something you  should do when the data is dynamic, since new and updated features will not reflect the changes.

[Nicole_Ueberschär]

What a pity. Unfortunately the Survey layer is dynamic so we would have to calculate it again every time a survey was submitted... Not very userfriendly 😞

Any other idea how my parcel could reflect that the survey for this parcel was done? Both layers are quite dynamic...

[Nicole_Ueberschär · Accepted solution]

I found a solution that is in Beta 😉

I can create the survey as polygon feature class and hand over the parcel as geoshape, and then overlay the parcels with the survey results in the map. Tataa!

Now hoping to have this capability released soon...