How to get attribute with last date modified from related table using Arcade
Hi Kathy Smikrud ,
To get the sum of the values in the repeat you can use the following expression:
// Get the related surveys from feature
var surveys = FeatureSetByRelationshipName($feature, "EscapementSurveys");
// get number of surveys
var cnt1 = Count(surveys);
var cnt2 = 0;
// initialize values (ignore SPECIES_CODE)
var mouth = 0; // MOUTH
var tidal = 0; // TIDAL
var live = 0; // LIVE
var dead = 0; // DEAD
// check if there are any suveys available
if (cnt1 > 0) {
// sort surveys on date
var surveyssorted = OrderBy(surveys, 'OBS_Date DES');
// take most recent survey
var survey = First(surveyssorted);
// get repeats for this survey
var repeats = FeatureSetByRelationshipName(survey, "repeat_SpeciesCounts");
cnt2 = Count(repeats);
if (cnt2 > 0) {
// we have repeats, now sum values
for (var repeat in repeats) {
mouth += repeat.MOUTH;
tidal += repeat.TIDAL;
live += repeat.LIVE;
dead += repeat.DEAD;
}
}
}
// create resulting text for pop-up
var result = "";
if (cnt2 > 0) {
// there are repeats, so construct the resulting text
result = "Mouth: " + mouth;
result += TextFormatting.NewLine + "Tidal: " + tidal;
result += TextFormatting.NewLine + "Live: " + live;
result += TextFormatting.NewLine + "Dead: " + dead;
} else {
// no repeats found, return a text to explain that
result = "No counts available";
}
// return the result
return result;See below the result in the pop-up:
In case you want to show the counts per specie code, you can use the expression below:
// Get the related surveys from feature
var surveys = FeatureSetByRelationshipName($feature, "EscapementSurveys");
// get number of surveys
var cnt1 = Count(surveys);
var cnt2 = 0;
// check if there are any suveys available
var result = "";
if (cnt1 > 0) {
// sort surveys on date
var surveyssorted = OrderBy(surveys, 'OBS_Date DES');
// take most recent survey
var survey = First(surveyssorted);
// get repeats for this survey
var repeats = FeatureSetByRelationshipName(survey, "repeat_SpeciesCounts");
cnt2 = Count(repeats);
if (cnt2 > 0) {
// we have repeats, now aggregate the repeat per species: SPECIES_CODE
var stats = GroupBy(repeats, ['SPECIES_CODE'], [
{name: 'sumMOUTH', expression: 'MOUTH', statistic: 'SUM'},
{name: 'sumTIDAL', expression: 'TIDAL', statistic: 'SUM'},
{name: 'sumLIVE', expression: 'LIVE', statistic: 'SUM'},
{name: 'sumDEAD', expression: 'DEAD', statistic: 'SUM'}]);
// read the aggregation and create the text
for (var stat in stats) {
result += TextFormatting.NewLine + TextFormatting.NewLine + "Specie code: " + stat["SPECIES_CODE"];
result += TextFormatting.NewLine + " - " + "Mouth: " + stat.sumMOUTH;
result += TextFormatting.NewLine + " - " + "Tidal: " + stat.sumTIDAL;
result += TextFormatting.NewLine + " - " + "Live: " + stat.sumLIVE;
result += TextFormatting.NewLine + " - " + "Dead: " + stat.sumDEAD;
}
} else {
result = "No counts available";
}
} else {
result = "No counts available";
}
// return the result
return result;The result will display as this:
In your email you mentioned that you want to access the previous survey counts, so not the latest record. In that case you would still have to sort the surveys on date but now loop through the surveys and take the second. This is a bit longer than using the First function, but it can be done.
Hello @XanderBakker ,
This post helped me a lot as I am beginner to the world of Arcade so thanks so much.
I have one question, is there a way to make some loop that can go thro multiple related record and show them as one attribute (or more) in pop up.
This is how it looks now, only showing one related record:
And code used was yours with some simple modifications:
var relatedrecords = OrderBy(FeatureSetByRelationshipName($feature,"ActivitesMaster"), "Activity DESC");
var cnt = Count(relatedrecords);
var relatedinfo = "";
if (cnt > 0) {
var info = First(relatedrecords);
relatedinfo = info.Activity
}
return relatedinfo;Is something similar to For or While Loop available in Arcade? Main problem is that some features have 1 related attribute, and up to 3 or 4 in some different cases. This would help me and the several apps that I am developing.
Thanks in advance,
Ivan
Hi Xander-
Any idea why the Related Records would not be available under Globals > $feature ? I would like to use the FeatureSetByRelationshipName but I cannot without that. What is the typical cause of this? Thank you in advance!
