Using FeatureSetBy functions in Arcade to drill-down to other layers and tables

61933
135
12-12-2018 02:24 PM

Using FeatureSetBy functions in Arcade to drill-down to other layers and tables

Today a great blog was posted by Paul Barker‌ on the new FeatureSet functions: What’s new with Arcade: Taking a stroll through FeatureSets (Part 1)  and also Kelly Gerrow‌ shared a great document on this topic: https://community.esri.com/community/gis/web-gis/arcgisonline/blog/2018/12/10/overlapping-features-i... . 

I didn't wanted to be left behind, so I thought I would share some examples using hydrants, the maintenance history and sub sectors and hydraulic sectors to show what you can achieve with the new Arcade functions released in ArcGIS Online on December 5th

Consider we have a web map with several layers and a table:

  • Hidrantes is a point layer with hydrants
  • Sub Sector is a polygon layer with a sub division of the area of interest
  • Sector Hidraulico is a polygon layer merging sub sectors into hydraulic sectors
  • Hist Mantenimiento is a table with the historic maintenance records for each hydrant

First of all, let’s apply some Arcade to visualize the hydrants based on their age. This was already possible in the previous release, so no new things here yet. We have the installation date stored in the field FECHAINSTALACION and we can use the DateDiff and Now functions to determine the age in years, which happens on the first two lines. After this we classify the value into significant ranges:

var fecha_inst = $feature.FECHAINSTALACION;
var edad = DateDiff(Now(), fecha_inst, "years");
var clase_edad = "";
if (edad > 50) {
    clase_edad = "Mayor a 50 años";
} else if (edad > 40) {
    clase_edad = "Entre 40 y 50 años";
} else if (edad > 30) {
    clase_edad = "Entre 30 y 40 años";
} else if (edad > 20) {
    clase_edad = "Entre 20 y 30 años";
} else if (edad > 10) {
    clase_edad = "Entre 10 y 20 años";
} else if (edad > 5) {
    clase_edad = "Entre 5 y 10 años";
} else {
    clase_edad = "Menor a 5 años";
}

return clase_edad;

The result is a text describing the age, for which we can define representative symbols (here using the Firefly symbols):

No fancy symbols for the other two polygon layers to avoid distracting the attention from the hydrants.

We would like to show in the pop-up window of each hydrant the list of all maintenance carried out on the hydrant. Both the maintenance history table and the hydrant layer contain the ID of the hydrant. The table also contains the dates and type of maintenance.

In the configuration of the pop-up of the hydrants we can add an Arcade expression like this one:

var tbl = FeatureSetByName($datastore,"Hist_Mantenimiento");
var codigo = $feature["COD_HIDRANTE"];
var sql = "COD_HIDRANTE = '" + codigo + "'";
Console(sql);
var mantenimientos = Filter(tbl, sql);
var cnt = Count(mantenimientos);
var historia = "";
if (cnt > 0) {
    historia = cnt + " Mantenimiento(s):";
    for (var mantenimiento in mantenimientos) {
        var txt_fecha = Text(mantenimiento.Fecha_Mantenimiento, ' - (Y/MM/DD) ');
        var txt_man = txt_fecha + mantenimiento.Mantenimiento;
        historia += TextFormatting.NewLine + txt_man;
    }
} else {
    historia = "No hay mantenimientos";
}

return historia; 

First of all, we access the table with the maintenance history and assign it to a variable tbl. Then we read the id of the current hydrant for which we want to create the pop-up. We define a SQL expression using the hydrant ID which is then used to filter the history table. The result is stored in a variable called mantenimientos. We can do a Count to obtain the number of maintenance carried out on the hydrant. If the count (cnt) is larger than 0, we start reading out the details and construct a list of maintenance including the date and type of maintenance.

The result will appear like this after we configure a simple custom html to show the attributes and result of the Arcade expression in the pop-up:

Now let’s continue with doing something with these new Arcade functions to get some data hydrants at the sub sector level. Since the number of maintenance obviously depend on the age of the hydrant, it would be nice to get some details on this at the sub sector level.

In the pop-up configuration of the sub sectors we can add the following Arcade expression:

var fs = FeatureSetByName($datastore,"Hidrantes");
var hidrantes = Intersects(fs, $feature);
var cnt = Count(hidrantes);

var edad_tot = 0;
for (var hidrante in hidrantes) {
    var edad = DateDiff(Now(), hidrante.FECHAINSTALACION, "years");
    edad_tot += edad;
}

var resultado = ""; 
if (cnt > 0) {
    var edad_media = edad_tot / cnt;
    resultado = "Hay " + cnt + " hidrantes en el sub sector";
    resultado += TextFormatting.NewLine + "La edad media de los hidrantes es de " + Round(edad_media, 2) + " años";
} else {
    resultado = "No hay hidrantes en el sub sector";
}

return resultado;

This expression will:

  • Access the Hydrants layer
  • Do an intersect with the current sub sector feature
  • Count the number of hydrants in the sub sector
  • Calculate the age for each hydrant and some all ages
  • Calculate the mean age of the hydrants and format the text with this information

The result will appear like this:

In this case there are 93 hydrants in the sub sector and the mean age is 20.64 years.

This is fun, but can we also show the names of the sub sectors that are contained by a hydraulic sector using these new Arcade functions? The answer is yes. Let’s add the following expression to the hydraulic sectors:

var fs = FeatureSetByName($datastore,"Sub Sector");
var subsectores = Intersects(fs, $feature);
var cnt = Count(subsectores);

var resultado = "Listado de los " + cnt + " Subsectores:";
for (var subsector in subsectores) {
    resultado += TextFormatting.NewLine + subsector.IDSAP;
}

return resultado;

Just access the sub sectors layer and Intersect with the current hydraulic sector and voila…, rights? No, not so right. When you use an Intersect between two polygon layers, it will probably return too many features due to the fact that when two polygons have a matching boundary they will probably be included in the result, leading to more sub sectors than we want.

A way to avoid this is to use a small negative buffer on the hydraulic sector and do the Intersect with that buffered geometry like this:

var fs = FeatureSetByName($datastore,"Sub Sector");
var feature_buf = Buffer($feature, -100, 'meters');
var subsectores = Intersects(fs, feature_buf);
var cnt = Count(subsectores);

var resultado = "Listado de los " + cnt + " Subsectores:";
for (var subsector in subsectores) {
    resultado += TextFormatting.NewLine + subsector.IDSAP;
}

return resultado;

Notice the Buffer with a negative distance that is created on line 2 and used on line 3 to get the rights polygons.

The resulting pop-up will show like this:

To be honest, I don’t like the unsorted list of sub sectors, so let’s order this list using OrderBy:

var fs = FeatureSetByName($datastore,"Sub Sector");
var feature_buf = Buffer($feature, -100, 'meters');
var subsectores = OrderBy(Intersects(fs, feature_buf), 'IDSAP ASC');
var cnt = Count(subsectores);

var resultado = "Listado de los " + cnt + " Subsectores:";
for (var subsector in subsectores) {
    resultado += TextFormatting.NewLine + subsector.IDSAP;
}

return resultado;

Which will result in a sorted list of sub sectors:

I hope that these examples will help you to understand what is possible using Arcade with the latest update in ArcGIS Online, but before you jump in and start creating fabulous Arcade expressions I want to quote Uncle Ben (Spider-Man) for a moment:

With great power comes great responsibility...

As Paul Barker mentions in his blog, be careful and always validate that the performance of your expressions yield in an acceptable response time for your end-user. 

Comments

Hi Naomi Begg,

I made a small change on lines 12 and 13, assessment should be assessments. I would like to see what is being printed to the Console to see what else might be going wrong. Can you share that?

var tbl = FeatureSetByName($map,"Tree_Assessment_Map - Assessments");
Console(Count(tbl));

for (var row in tbl) {
    Console("#" + row.globalid + "#");
}

var code = $feature["globalid"];
var sql = "tree_id = '" + code + "'";
Console(sql);

var assessments = Filter(tbl, sql);
var cnt = Count(assessments);
var history = "";

if (cnt > 0) {
    history = cnt + " Assessment(s):";
    for (var assessment in assessments) {
        var txt_date = Text(assessment.Date, ' - (DD/MM/Y) ' );
        // var txt = txt_date + assessment.Assessment;
        history += TextFormatting.NewLine + txt;
    }
} else {
    history = "No assessments";
}

return history;

Thanks Xander Bakker‌ I was debating where the plural and non plural versions should sit!

This is the output I am getting.

Use Console Function to output messages.  12 #921486cd-cdf1-4636-addb-139487a548b6# #f3781c16-3099-4383-a03c-4229c85e1244# #211f53bd-257b-4a11-a379-911d21a21a8c# #7cce4a82-d2d1-4aa4-b31a-1b5885313c0a# #0c97996b-62f5-4c28-b8e4-e47720fcae6b# #43cc2b0c-e14a-4bc0-a6fa-ab3e3ba7b393# #7c1d9de5-54e9-4110-8ee0-6c083cf9e1c5# #d4b065a9-7e3d-4d61-8827-8daeeaf308f4# #5d654890-11df-4131-8342-b629072c70e2# #08def34b-5b71-4bf5-8cdf-1c1e9c8180df# #4d5157ea-377b-46bf-8369-d30f432fdac8# #35545d53-13ac-47b0-aa5e-7ebd6c9a6f9a# tree_id = 'd1ab7e5b-c8ba-4c89-b74c-4ff86dd85372'

Can you add an additional Console statement to return the count (cnt), just after line 12? I think you are not getting any results back. This might be due to the SQL. Is it possible to have access to the data? That would make any validation a lot easier. 

It doesn't seem to recognise the count.  I've added you to my help group   Tree ID 345 is an example of a tree which has assessments attached to it.

Hi njbegg 

I think I found a solution. First of all I read this support case for a querying a GlobaID: https://support.esri.com/en/technical-article/000015294. That made me wonder if the sql should be constructed differently (using uppercase and "{}"). Once that was working I also noticed that you uses a variable txt on line 21 which should be txt_date. Give it a try and let me know if it works.

var tbl = FeatureSetByName($map,"Tree_Assessment_Map - Assessments");
Console(Count(tbl));
//for (var row in tbl) {
//    Console("{" + row.globalid + "}");
//}
var code = $feature["globalid"];
var sql = "tree_id = '{" + Upper(code) + "}'";
Console(sql);

var assessments = Filter(tbl, sql);
var cnt = Count(assessments);
Console(cnt);

var history = "";
if (cnt > 0) {
    Console("list results...");
    history = cnt + " Assessment(s):";
    for (var assessment in assessments) {
        var txt_date = Text(assessment.Date, ' - (DD/MM/Y) ' );
    //    var txt = txt_date + assessment.Assessment;
        history += TextFormatting.NewLine + txt_date;
    }
} else {
    Console("no results");
    history = "No assessments";
}

return history;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Ahh that makes sense   Thanks so much!

How do I reference another field for the table layer (which annoyingly has a space in its name) so that I can get the inspector name.  I've tried assessment.Inspector but that has failed.

The best way to find out is to navigate to the data source in the Arcade expression window. On the rigt side you see the $map (which is how you access the table):

Click on the larger than sign to the right (">") which will show you the sources that you have available in your map:

One of which is the "Tree_Assessment_Map - Assessments". Click again on the larger than sign to the right amd this will reveal the fields that are available in the data source:

A field name will never have spaces, but when you click on the blue text you will get the name of the field copied to your code. Make sure that you use the object name in front of it just the way you did with the "Assessment" field: assessment.Inspector

Great.  Thanks so much!

I'm not sure how I had missed seeing the arrows at the end of the $feature, $layer, $map.  I'm also loving how I am learning a little bit of Spanish by your screenshots  

I've been working on this scenario for a few weeks now and had tried a few different ways but kept getting stuck and left a few questions on geonet leading in different directions.  This has finally solved what I was trying to do (I thought it should have been pretty simple).  It's hopefully also given me the base for the next expression I would like to build as an extension to the application which would show changes in the trees general information comparing the latest assessment to the current tree info.

I'm glad to have helped you and that you are learning some Spanish on the way . I have a couple of mostly Spanish documents that I have published on different uses of Arcade expressions in case you are interested:

https://community.esri.com/people/xander_bakker/content?query=arcade&filterID=contentstatus%5Bpublis...

If you need some help with your next challange, just create a question and tag me and I will see if I can help in any way.

I took your code and modified it to work for me, I had an idea while I was creating this expression.

My idea was to display info of each building associated with a parcel. I have a web service that is access by (Craven Building ) passing it 3 parameters. So i thought I could also build a hyperlink with the arcade expressions. To link the users to this service if needed. The popup does not seem to read the text as a hyperlink. it just displays the string.

Basically I just wanted to do the same thing you did but I wanted build a hyper link also.

var tbl = FeatureSetByName($datastore,"Appraisal.CRAVEN.tax_building")
//var tbl = FeatureSetByName($map,"tax_building");
var PREFN = $feature.PREFN
var sql = "BREF = '" + PREFN + "'";
//Console(sql);
var building_table = Filter(tbl, sql);
var cnt = Count(building_table);
var buildings = "";
var hyperlink = '<a href="http://gis.cravencountync.gov/maps/building.htm?prefn=';
if (cnt > 0) {
buildings = cnt + " Building(s):";
for (var building in building_table) {
var txt_fecha = Text(building.BDSTYP);
var txt_PREFN = Text(building.BREF);
var txt_man = txt_fecha + " " + building.BNUBR+ " " + building.BREF ;
hyperlink = hyperlink + Text(building.BREF) + " seqnum=&"+Text(building.BSEQ)+"&card="+(building.BCARD) + '"></a>';
buildings += TextFormatting.NewLine + " " + txt_man + TextFormatting.NewLine + hyperlink ;
//hyperlink = hyperlink + Text(building.BREF) + "&seqnum=" +Text(building.BSEQ)+"&card="+(building.BCARD);
}
}else {
buildings = "There are not any buildings";
}

return buildings;

When you return a string as HTML from an Arcade expression it will not work. The HTML is either stripped off or will just show as string. You should use custom HTML for the pop-up and use an expression to return the URL. Normally, this will require multiple expressions if you want to use a custom text for the link too.

Hi Xander,

I am trying to intersect two overlapping polyline layers to bring attributes of one layer to other layer's pop up, but unable to get any results. I guess my solution is hidden in using buffers but I don't know how to use that due to my very limited knowledge of Arcade. Can you please add a buffer in the following code to achieve what I want.

var intersectLayer =Intersects(FeatureSetByName($map,"Route"), $feature)
for (var f in intersectLayer){
return f.systemname
}

Strange enough that exactly similar code works for point over polygon, and line over polygon layers. It also shows "custom" field in the pop up window. But for line over line, same code doesn't even show custom field in the pop up window. Is there something else required to do other than buffer?

Thanks

Hassan

Hi Muhammad Hassan , 

Before going into using buffers, can you try something? Paste the code below and let's see what the console returns:

var intersectLayer =Intersects(FeatureSetByName($map, "Route"), $feature);
var cnt = Count(intersectLayer);
Console(cnt);
var result = "";
if (cnt > 0) {
    result = "There are " + cnt + " intersecting lines:";
    for (var f in intersectLayer){
        result += TextFormatting.NewLine + " - " + f.systemname;
    }    
} else {
    result = "No intersecting lines...";
}

return result;

One of the things you were doing is using return inside a for loop. That is not a good thing to do. Instead, use a variable (like I did with the variable result) that you declare before the loop and you add values inside the loop. I also use the Count to get a count of the number of intersecting features and print that to the console. 

Can you post back the result? Maybe there is an issue with intersects when using lines, but let's try this first.

Thanks for replying Xander!

I get a count of intersecting lines and their names (System Name).

Thanks

Hassan

SUPER helpful and incredibly powerful!  I'm trying to put it all together. 

Is there a way to refer to the index within a for loop?  I'm going to pull the top 5 most recent results from a related table, but I need to do 5 separate expressions in order to use them as hyperlinks. So I want to return each of the top 5 results to a separate expression variable.

I'm getting "Cannot call member property on object of this type."

....
for (var i in intel) {
               var n = 0;
               // Create the text for the report
               var intelInfo = TextFormatting.NewLine + i.hlink;               
               // Add on the final report text
               intelReport += intelInfo;
               Console("Print: " + intel[n])
               n++
          }‍‍‍‍‍‍‍‍‍
....‍‍‍‍‍‍‍‍‍‍‍

Should I use IndexOf and check if it equals the number I want, one for each of the first 5?  How else could this be done.

Hi David Treering ,

Glad to hear that this document has been useful. So, if I understand you correctly you need to use the "same" expression 5 times to get 5 hyperlinks from related records. If that it correct I would probably go for something like this:

Function GetHyperlink(records, index) {
    var record = records[index];
    return record.hlink;
}

// define query
var sqlExpression = ""; 

// get related records probably by using a Filter
var related_records = Filter(FeatureSetByName($map, 'Name of related table'), sqlExpression);

// get top 5 records from sorted results
var intel = Top(OrderBy(related_records, 'FieldName ASC'), 5);

var hyperlink = GetHyperlink(intel, 1);‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
return hyperlink;
  • Get your related records (probably using a filter)
  • Get the TOP 5 records from the sorted list of related records
  • Use the function defined at the start to get a certain record by the specified index

You should probably include something to avoid an overflow of the index (when there are less than 5 related records).

Thanks for the reply and idea. My FeatureSet does not seem to behave like your FeatureSet. I cannot retrieve an element by index. I also see now that Top and OrderBy have no effect.

Hi David Treering ,

Is it provide to share access to the data to have a look what you have or explain in more detail the source of the data?

Hello Xander Bakker‌ !

I a trying to follow along but am needing some help. I have a feature layer I generated from a "Join" analysis. It is called "Leasehold" - I renamed it in my map... does that matter?

I have a data table with "Water Depths" for every "Lease Number" in the Gulf of Mexico.

I am hoping to add the "Water Depth" data from my "Water Depth" table to the pop up box for my leasehold. 

Here is a picture...

When the lease number matches up... I want the water depth to be pulled and added to my popup. 

Any help?! THANK YOU!!

link to layer: https://llog.maps.arcgis.com/home/item.html?id=1881ac44470c407897a75533e8c7e1f6#overview 

If I add you to my org to check it out, you'll need to be Creator level, correct?

Hi Laura Preidis ,

Renaming a layer in your map, when you try to access the layer using the $map (not the $datastore) does matter, since you should use the exact name as used in your map. Can you share the code you have so far?

If you want to share the layer with me, you can create a group, share the map and layers in that group and invite my AGOL user (xbakker@esri.co_utility_esri_co) so I can have a look.

var bridge = Filter(FeatureSetByName($map,"IntelBridge",["ReportID","relateArea","relateReport"],false),("relateArea = '" + $feature.GlobalID + "'"));
var intelAll = First(Reverse(Top(OrderBy(Filter(FeatureSetByName($map,'reportAll',['ReportID','Title','GlobalID'],false),("GlobalID = '" + bridge[2].relateReport + "'")), 'ReportID DSC'), 3)));
var intelItem = Left(intelAll[0].Title, 70) + "...\n";
return intelItem;‍‍‍‍‍‍‍‍‍

Xander Bakker I'm still unable to use the FeatureSet[index] notation with my FeatureSets.  Does it work on non-spatial tables?

When I try Console(bridge); on line 2, and remove the rest, I get object, FeatureSet.

When I try Console(bridge[0]); on line 2 and remove the rest, I get Execution Error:Runtime Error: Cannot call member property on object of this type.

Hi David Treering ,

From what I can see, "bridge" is a featureset (a collection of features), although you do not want to have the geometry, so your first Console statement returns what would be expected. On the second line, I see you nest 6 functions and it seems you are trying to get the third feature. (top 3, reserve, first) Can you explain to me why?

The problem as you noted is likely due to the "bridge[2].relateReport". To get the third item of the featureset bridge you may have to loop through it (or also use the Top 3, reverse, first structure). Why would you want to have the third item BTW?

The third line might also run into problems. Don't use intelAll[0] but better use First(intelAll). Also in your expression when there are no 3 items or no result in intelAll, this might cause errors. You may have to check the number of results returned. 


Although you are doing a good job with nesting functions to reduce the number of petitions (optimize performance), you may want to write it out completely to validate the result, before you nest them on a single line.

Hi Xander Bakker‌,

I was trying to avoid iterating over the FeatureSets since the array[index] should work.  Can you confirm the problem is that there is no geometry, ie. a flat table?

The third feature is only important because I am getting the first and second results in separate expressions.  I'm getting the title and url for each of the top 3 (meaning 6 separate expressions), then making the 3 titles linked in the popup.  Sorry for the confusing randomness of the third record!

Formatting HTML in the Arcade expression would save a huge amount of work and server calls.  I found a few ArcGIS Ideas that support this idea.

I'll try laying it out without nesting and implement iteration, then let you know.

OK, Xander Bakker,

By using a simple counter "p" and conditions, I was able to get the job done. 

For the first non-empty result, instead of the counter, I just return the attribute. 

....
    for(var b in bridge){
          var intelTech = Filter(intelAll, "GlobalID = '" + b.relateReport + "'");
          if (!IsEmpty(First(intelTech))){
               return First(intelTech).hlink;
          }
     }‍‍‍‍‍‍
....

For the second, third, and nth non-empty result, I specify the number in the second if statement.

....
    var p = 0;
     for(var b in bridge){
          var intelTech = Filter(intelAll, "GlobalID = '" + b.relateReport + "'");
          if (!IsEmpty(First(intelTech))){
               p++;
          }
          if (p == 2){
               return First(intelTech).hlink;
          }
     }‍‍‍‍‍‍‍‍‍‍‍
....‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The non-empty part is important because I have a few different ways the reports need to be filtered, but the bridge table is bringing all of the intersecting feature's related results.  In the loop, there will be "b in bridge" that match one of the filters and also some that don't, returning an empty result. 

Hi Xander, 

I am back with another scenario which is one step further from my last request. Now that we have multiple overlapping features being displayed in pop ups, I want to display only one most relevant route name on one selected pipe segment. For that, I have a common attribute in both feature classes, "Pipes.EngrouteID", and "Route.RouteID". And I want this function to work as, when I click on a pipe segment it matches its EngrouteID attrbute with RouteID attribute in Route Feature class, and displays its relevant record from "RouteName" attribute of Route Feature Class. How can we achieve that with arcade?

Thanks

Hassan

Hi H.Muhammad ,

I think you should be able to do something like this:

  • read out your EngrouteID
  • create a sql query
  • access the routes and filter by the query
  • if there are any results, access the first and return the route name

var EngrouteID = $feature.EngrouteID;
var sql = "RouteID = '" + EngrouteID + "'";
var routes = Filter(FeatureSetByName($map, "Route"), sql);
var routename = "";
if (Count(routes)> 0) {
    var route = First(routes);
    routename = route.RouteName;
}
return routename;

Hello,

I am wondering in someone could help me with this. I am not getting any syntax errors, but there is also nothing displaying in the results. I am trying to intersect a polygon layer with a point layer and then sum the values of a field in that point layer. I got the expression from here (ArcGIS Online Webmap: Summarize values of overlapping polygons in quantitative symbology - Geographi... ) and they seemed to get it to work. I am wondering why it isn't working for me or if there is another way of doing it.

This is what I have now, and when I click Test nothing happens. I can't even click OK. I have to click Cancel and lose my expression. Any help would be great. Thanks!

Hi Taylor Robin , 

Normally when this is happening (you click on Test and nothing visually happens), the system is still working on it. How large is your data? How many points are there and how complex are your polygons?

You could probably speed it up a little by combining multiple lines and reduce them to a single request like this and limit the number of fields:

var pts_int = Intersects(FeatureSetByName($map, "PermitCounter", ["USER_NUMBEROFAPPLICATIONS"]), $Feature);
Console(Count(pts_int));
var vSum = 0;
for (var permits in pts_int) {
    vSum += permits.USER_NUMBEROFAPPLICATIONS;
    Console(vSum);
}

return vSum;

I also placed some Console statements, so you would be able to see what is happening.

Thank you! That worked. I guess it was too much data to go through.

Now this is probably asking too much, and I'm not sure it's meant to work like this. But if I bring that map into a dashboard and set up a date filter. I was hoping the pop-ups would reflect the number of applications in that date range. It is still showing the total number for the whole polygon. Is this "live filter" of the pop-up possible?

Thanks Xander, it works perfectly!

It looks like using a variable specified outside the Arcade expression is not possible.

Xander Bakker

A colleague and I are working to create a popup that includes a field or two from the most recent record in a 1-to-many related table. We found this post to use as a starting point. Being a novice with Arcade, I commented out most everything, then worked from the top until I ran into errors. I receive values in the console for the tbl count, the id, and the sql expression; however, when I incorporate the record count (line 14), I receive the error: Execution Error:Unable to complete operation. I thought that I was running into a problem with the filter, but when I comment out lines 13 and 14 I do not receive the error.

I will send you an invite to a testing group to which the map in which I'm working is shared. I'm working on this in Portal 10.7. Let me know if you can help, and if there is further information that I can provide.

Thanks,

Eli

Hi Elias Martinson ,

I'm not sure what is causing the error in your expression. Are you using 10.7.0 or 10.7.1? Access to the data would help to understand what is happening. To get the most recent record, I guess it would be good to use OrderBy (descending on your date) and to the first record using First.

Thanks for the response. We're using 10.7.0. I'm working on getting you access to the data.

Xander Bakker - Can you please tell me if it's possible to create an arcade expression that will return a value from a layer's parent layer? 

I have a feature layer created in Survey123 with two layers.  Layer 0 has a "projectnumber" attribute in it and I would like to filter layer 1 (the repeat layer) to only display features for a certain project number, with that project number value being pulled from it's parent feature. 

Hi Derrick Westoby,

If you want to filter a layer using the interface for filtering a layer in the TOC, ou can't use Arcade for that. I your idea is to show information in a pop-up, you can define an Arcade expression on the parent layer and only show the relevant information in the pop-up. Can you elaborate a little more on where you want to show what information?

Thanks for the quick reply, Xander.  

The end goal is making a project-specific dashboard, which would include multiple Survey123 (company-wide) surveys.     Our field staff uses a S123 form, called "Daily Field Report", on more than one project.  What we'd like to be able to do is having those feature layers filtering into project/client specific dashboards based on project numbers.  

On this survey, Layer 0 contains general site information for that trip (client name, project number, site location, weather, etc) and layer 1 is a repeat that contains photos and comments about specific observations (found contaminant in room 27, etc).    

Internally, our project managers and field staff can view the private "Daily field report" dashboard to see what all of our field staff are doing and what sites they're working on.  Externally, we'd like to be able to offer these same dashboards to clients, but only showing data for their specific site.   

Hi Derrick Westoby ,

I get the impression that there would be no need for Arcade to achieve this. The Dashboard configuration allows you to filter your data or you could make use of Hosted Feature Layer Views to create and handle views of the data based on different areas. Perhaps you could even use a single Dashboard and a parameter provided in the URL to set the area. 

Hi Xander

I am interested in looping through as well and returning hyperlinks in the popup where the user can click like any other anchor tag.

I have done these before but never with a related table. For example, if one feature has five hyperlink associated with it, can I show five separate hyperlinks and the user can click on each one?

Thanks
Reza

Hi Reza Tehranifar ,

I would recommend you to wait with that since Arcade does not support returning HTML yet (in a webmap). In ArcGIS Pro you can do this today. In the web map currently, you would need a whole bunch of expressions that would do a similar query (think about performance) and custom HTML pop-up to accomplish this. 

Thanks Xander. Would this be a problem if I decide to use the arcGIS JS library to build out the map?

I am assuming that it’s easy to build the necessary HTML using JS. But may not. 

Hi Reza Tehranifar ,

It is possible to do so. However, it might not be the best thing to do. This would require programming against a certain version of the API and could require more maintenance in the future, while if it is something that could wait, you could do easier with simple configuration and not having to worry about any updates since it will continue to work. So the question is, do you need it urgently and are you willing to put in the effort to build this functionality and implement updates of the API when they become available?

Xander, when is the Arcade API going to allow HTML in Enterprise web maps? This would be a game changer, for sure.

Hi DTreering@invenergyllc.com_invenergyllc ,

Sorry, but I only know it is only the roadmap, I don't have a date.

Hello,

If I wanted to display just one filed in a table into the popup what expression could I use to list for example a list of names?

Thank you!

Hi Trisha Shepley ,

Can you elaborate a little more on your use case? Do you want to access a related table or another layer and extract  information from a single field and show that in the pop-up? How are the layers related? Do you need to relate them spatially or can you use a a relationshipclass or do you need to apply a filter (SQL)?

I want to access related table and extract information from a single field and show that is popup.  For example: here are my tables

{relationships/0/attendess} {relationships/1/reserve_personnel} 
I want to list in the popup below

Attendees

Name in Field

Name in Field

I hope this helps,

Thank you!

Hi Trisha Shepley ,

So to get the information from a related table you can use the function "FeatureSetByRelationshipName". This will return a featureset with all the related records. You can limit the size of the result by specifying the relevant fields (see "RelevantFieldName" below) you want to use and not requesting the geometry is there is any (using "false" as last argument):

var fs = FeatureSetByRelationshipName($feature, 'Exact Name of the Relationship', ['RelevantFieldName'], false);

Since this will return a featureset ("fs" in example above) you will have to loop through the records to get access to the individual names of the attendees. This can be done as follows:

var result = "Attendees:";
var cnt = Count(fs);
if (cnt > 0) {
    // start loop through related records
    for (var f in fs) {
        // for each f (=feature) in related features, add attendee to the result
        result += TextFormatting.NewLine + f.RelevantFieldName;
    }    
} else {
    // overwrite result with text to indicate that there are no attendees
    result = "No attendees";
}

return result;

If you have multiple relationships, you will have to get the related info for each related table and include it in the resulting string.

Hi Xander Bakker‌, I'm back with another question.

I am trying to use one of our open data layers as the visual layer and then do an intersect for an ugly layer underneath it.  However for some reason I cannot use the $map reference for this layer : https://maps.taupodc.govt.nz/server/rest/services/property/Reserve/MapServer .  

Why won't it work for this layer?  Is there a way around it?

Thanks.

Version history
Last update:
‎12-12-2021 03:46 AM
Updated by: