How to get attribute with last date modified from related table using Arcade

2606
32
11-06-2019 02:38 PM
KellyDoss1
New Contributor II

Hello all,

 

I'm pretty new to Arcade and I'm having problems.  I believe I need to be using FeatureSets in Arcade.

 

I have a hosted feature layer with several related tables, some of which are 1:M.  In the CIP_Test pop-up, for the 1:M related tables, I only need to show the data from the rows with the latest "Date Modified".

 

I've attached a screenshot of the related table.  Where there are duplicate AcctNo, I only want to show the ProjectPhase (and maybe other fields) for the record that was last modified.

 

Any suggestions on how to do this are much appreciated!  I have gotten nothing but errors in everything I've tried from any forum posts I've found....

Reply
0 Kudos
32 Replies
KatieMBaltzley
New Contributor III

I will try the code above to see if it works!  Although I did find out that there is a bug associated with the "FeatureSetByName" function and esri plans on fixing it in the next field maps update!  

Reply
0 Kudos
XanderBakker
Esri Esteemed Contributor

Hi Tim Flynn ,

Remember to return the relatedinfo and not the relatedrecords, but the key to get the most recent date. This is done with the OrderBy, but on the first line the way to order the data was missing. Have a look at the end of the first line of the expression below:

var relatedrecords = OrderBy(FeatureSetByRelationshipName($feature,"AST_Inspection"), "insp_date DESC");
var cnt = Count(relatedrecords);

var relatedinfo = "";
if (cnt > 0) {
var info = First(relatedrecords);
relatedinfo = Text(ToLocal(info.insp_date), "MM/DD/Y");
}

return relatedinfo;
TimFlynn
New Contributor III

So is there a difference between adding in DES vs DESC?  Both seemed to work, at least while I have very few records in the data set.

Reply
0 Kudos
XanderBakker
Esri Esteemed Contributor

Hi Timothy.Flynn_ocfl , 

As far as I know they are both valid options... 

kmsmikrud
Occasional Contributor III

Hi Xander Bakker and Kelly Doss,

I really appreciate the code for displaying related records in the pop-up using the FeatureSetByRelationshipName. I have a question, I have a point layer with a related table and then a second related table to the first related table. This is from a Survey123 app in which points have related surveys and within the Survey123 there is a few repeat questions hence the second related table. Is it possible to access the second related table thru the pop-up with arcade and what function would you recommend? I would like to summarize counts in the second table displayed by most recent survey date.

thanks in advance!

Kathy

Reply
0 Kudos
XanderBakker
Esri Esteemed Contributor

Hi Kathy Smikrud ,

I suppose that it should be possible feeding a row from the 1st related table when fetching the 2nd related table using the same FeatureSetByRelationshipName. If you can share the data I can look at it with more detail (AGOL user "xbakker@esri.co").

Reply
0 Kudos
XanderBakker
Esri Esteemed Contributor

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.

kmsmikrud
Occasional Contributor III

Hi Xander Bakker‌,

THANK-YOU SO MUCH! The latter code is exactly what I was hoping to do but unable to put the code together in how to access the counts in the second related table. In regards to accessing all previous survey counts at this time the goal was just to access the last survey count which is what you provided. I can't tell you how much I appreciate your help on this and its very exciting to see the results show up.

I am curious if the arcade will work offline in the Collector for ArcGIS app with iOS device which is really the goal for surveyors to be able to view previous survey counts when they are out. If this functionality isn't available now hopefully it is on the road map.

Again thank-you so much for your time in looking at the data and sharing the code!

Kathy

XanderBakker
Esri Esteemed Contributor

Hi kmsmikrud ,

I am glad that the expression is doing what you wanted to achieve. At this point I am not sure if the expression will work in Collector. Support for Arcade is incrementing with each release, but the best you can do is try it out and when you do, please post back your findings.

Reply
0 Kudos
IvanKuzmic
New Contributor

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:

IvanKuzmic_0-1608296203894.png

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 

Reply
0 Kudos