Hi,
I have looked at several Arcade related postings and trying to string together some code to perform the following operation:
1) Intersect a point feature layer with related table with regional polygon feature layer (LLS Layer is where the Arcade expression is being displayed in pop-up):
This portion works ok, but I may need to know the count of 'species sampled'. f.species is within the feature layer.
2) I wish to summarise records in the point feature layer related table:
End result is pop-up will show:
I am thinking I will need one common expression tailored for:
Advice greatly appreciated.
Hi Craig Poynter ,
For the summary you could use the GroupBy function. This will allow you to summarize on a featureset and get the count of microbes. To learn more about the GroupBy function I recommend you to read the blog post by Paul Barker: https://www.esri.com/arcgis-blog/products/arcgis-online/mapping/whats-new-in-arcade/
I also have a document explaining the use of GroupBy in a more detailed way, but this is in Spanish: https://community.esri.com/docs/DOC-13951-incluir-reportes-estadísticas-en-las-ventanas-emergentes-c...
Hi Xander Bakker,
Thank you for the links. I have had a read, but syntax is confusing me with trying to summarise from layer file and associated records in related table. $map, $datastore, $feature, $layer usage and pulling from related table fields is proving difficult.
I can add you to a group if you wished to look at data if that helps.
Regards,
Craig
Hi cpoynter ,
Sure, having access to the data would help a lot to see what is possible! My AGOL user is "xbakker.spx".
Hi cpoynter ,
Thanks for sharing. Hopefully later today I have some time to look at the data. I must say that creating a customized HTML output, will requiere multiple Arcade expressions and will increase complexity. I will focus primarily on a single expression that will return a "report" using the GroupBy I mentioned earlier, but I will also provide some pointers on how to advance from there.
Hi Craig Poynter ,
Doing a little test on your data, I think there might be another challenge: performance. As far as I understand you want to click on the polygon, query the intersecting points and retrieve all related data stored in a table of those selected points.
Have a look below what this would look like:
var intersectedpoints = Intersects(FeatureSetByName($map,"CUMULATIVE REGIONAL ANTIBIOGRAM"), $feature)
Console("points found: " + Count(intersectedpoints));
var dct_species = {};
var dct_microbes = {};
for (var pnt in intersectedpoints) {
var specie = pnt.species;
if (HasKey(dct_species, specie)) {
dct_species[specie] += 1;
} else {
dct_species[specie] = 1;
}
//Console("pnt: " + pnt.job_name + " | " + pnt.species);
var fs = FeatureSetByRelationshipName(pnt, "form_microbe_repeat");
for (var f in fs) {
var microbe = f.microbe;
if (HasKey(dct_microbes, microbe)) {
dct_microbes[microbe] += 1;
} else {
dct_microbes[microbe] = 1;
}
}
//Console(" - related records: " + Count(fs));
}
Console(dct_species);
Console("");
Console(dct_microbes);
var result = "Species:";
for (var s in dct_species) {
result += TextFormatting.NewLine + " - " + s + ": " + dct_species[s];
}
result += TextFormatting.NewLine + TextFormatting.NewLine + "Microbes:"
for (var m in dct_microbes) {
result += TextFormatting.NewLine + " - " + m + ": " + dct_microbes[m];
}
return result;
The result is a single string containing:
Species:
- equine: 45
Microbes:
- actinobacillus_pasteurella_sp_: 1
- blood_culture_fluid_mixed_intes: 4
- bordetella_bronchiseptica_: 2
- enterobacter_cloacae_: 3
- escherichia_coli_: 1
- escherichia_coli_mucoid_: 1
- escherichia_coli_non_haemolytic: 4
- gram_negative_cocci_coccobacill: 1
- gram_negative_coccobacilli_: 2
- gram_negative_pleomorphic_cocco: 1
- lactobacillus_sp_: 1
- pasteruella_aerogenes: 4
- pasteurella_like_organism_: 2
- staphylococcus_aureus_: 8
- staphylococcus_aureus_intermedi: 1
- staphylococcus_sp_: 1
- streptococcus_dysgalactiae_subs: 4
- streptococcus_equi_subsp_zooepi: 6
- streptococcus_sp_alpha_haemolyt: 3
- streptococcus_sp_gp_c_: 3
However, executing time is horrible, since it consults for every specie the related records. There are at least 46 requests sent to the server and that results in a horrible response time.
I think there might be another way, but let me check that first.
Hi Craig Poynter ,
Something that wil reduce the number of requests to the server and will increment the performance is a scenario where the globalid is used to query the parentglobalid in the related table. It believe it still takes too long, but now it is 12 seconds instead of 30:
var intersectedpoints = Intersects(FeatureSetByName($map,"CUMULATIVE REGIONAL ANTIBIOGRAM"), $feature);
var guids = [];
for (var pnt in intersectedpoints) {
guids[Count(guids)] = "'{" + Upper(pnt.globalid) + "}'";
}
var result = "Species:"
if (Count(guids) > 0) {
var lst = Concatenate(guids, ", ");
var sql = "parentglobalid IN (" + lst + ")";
var fs = Filter(FeatureSetByName($map,"service_e12489f2b6a940b4b95e858ebaa42a27 - microbe_repeat"), sql);
var stats_species = GroupBy(fs, "microbe",
[{name:"count", expression:"microbe", statistic:"COUNT"}]);
for (var stat in stats_species) {
result += TextFormatting.NewLine + " - " + Trim(Proper(Replace(stat.microbe, "_", " "))) + " (" + stat.count + ")";
}
} else {
var result = "No related records..."
}
return result;
Result:
Hi Xander Bakker,
These are a great result. Is it possible to have a result so that it lists Species (eg. equine) > Microbe (eg. Blood Culture) > Treatment > Sensitivity result? If I had a template for one combination I could repeat for each of the combinations I would need to compile for adding to a html table (many copies of the same expression to build my table).
I am thinking this would require filtering by species, then microbe and then treatment and result. Accessing the related tables and the associated syntax had me struggling. Is this a possibility?
I appreciate all you have achieved so far.
Regards,
Craig