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

[KellyDoss1]

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....

[Anonymous User]

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!  

[JuliaHillin]

Hi @XanderBakker ! I'm late to the party, but I was hoping you still might be able to help me. I've tried implementing the code you provided earlier in this thread and have been unsuccessful. I don't receive any errors, but its also not providing the date of any recent inspections and only printing my else statement. We don't have many records in our related table, so I'm not sure if that might be the issue? I've been struggling with this for several hours and was hoping you might have a solution! Here is a copy of my code:

var relatedrecords = OrderBy(FeatureSetByRelationshipName($feature,"Battery_Inspections"), "inspectiondate DESC");
var cnt = Count(relatedrecords);

var relatedinfo = "";
if (cnt > 0) {
    var info = First(relatedrecords);
    relatedinfo = Text(ToLocal(info.inspectiondate), "M/D/Y, hh:mm");
} else {
    relatedinfo = "No inspections available."
}

return relatedinfo

[AndrewSmith21]

Does this code work in 10.6.1 Enterprise?

[ClintonKeating]

This was very useful. I am trying to use this on an attribute rule for assigning the latest inspection date from related inspection table to the equipment parent. It is working well but the triggers are only on the equipment record so when a new inspection is added the parent equipment record doesn't recognize an insert or update on the parent equipment record. This is on a FGDB to be Published to an Enterprise feature service on portal.  

Any thoughts / ideas on this? 

[XanderBakker]

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]

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.

[XanderBakker]

Hi Timothy.Flynn_ocfl , 

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

[kmsmikrud]

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

[XanderBakker]

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").

[XanderBakker]

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.