topic Re: How to get attribute with last date modified from related table using Arcade in ArcGIS Online Questions

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

KellyDoss1 — Wed, 06 Nov 2019 22:38:02 GMT

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

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

KellyDoss1 — Sat, 11 Dec 2021 14:06:18 GMT

Xander Bakker‌

I had posted this same question at the bottom of the "Overlapping Features in Pop-Ups.....FeatureSets with Arcade" blog post and you responded with:

You could probably try something like shown below. Keep in mind that you need to change some field names to match your data. The idea is to get the ID from the current feature and create a filter to get the related project phases. This is sorted descending on the date and you get the first element from the result, which should be the most recent change in project phase.

var yourid = $feature.YourIdField; // change the name of the field
var tblphases = FeatureSetByName($map, "CIP Test - Project Phase");
var sql = "YourIdField = '" + yourid + "'"; // if field is text
var relphases = Filter(tblphases, sql);
var cnt = Count(relphases);
Console(cnt);

var result = "";
if (cnt > 0) {
    result = "Project phase:" + TextFormatting.NewLine;
    var currentphase = First(OrderBy(relphases, 'DateModified DESC'));
    result += "Date : " + currentphase.DateModified + TextFormatting.NewLine;
    result += "Phase: " + currentphase.ProjectPhase;
    return result;
} else {
    result = "There are no project phases defined";
}

return result;

If the data is related using a relationshipclass, it can be done in a more easy way."

My question is for this last sentence. I've setup my data now in ArcMap with relationship classes and published it as a feature layer. Can you explain the easy way, please?

Thanks,

Kelly

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

XanderBakker — Thu, 12 Dec 2019 15:39:39 GMT

Hi Kelly.Doss_DurhamNC ,

Let me create a dummy dataset and test some things out and I will get back to you with an example.

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

XanderBakker — Sat, 11 Dec 2021 14:06:22 GMT

Hi Kelly Doss ,

Here is an example of what I think you are trying to obtain:

The underlying Arcade expression is:

var relatedrecords = OrderBy(FeatureSetByRelationshipName($feature, "RelData"), "DateModified DES");
var cnt = Count(relatedrecords);

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

return relatedinfo;‍‍‍‍‍‍‍‍‍‍

So, first the related records are obtained using the relationshipclass (ordering the result on DateModified). Then we use the count to know have many related records were found. When there are any, the first record (most recent update) is retrieved and some fields are formatted for presentation.

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

KellyDoss1 — Thu, 12 Dec 2019 16:25:25 GMT

Thank you so much for taking the time to help me! I’ll let you know if I have success.

Kelly

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

KellyDoss1 — Fri, 13 Dec 2019 17:14:41 GMT

Hello,

I have shared an AGO group with you that has my feature layer in it. As I’ve been researching through Esri documentation and blog posts on how to create the desired dashboard, I’m wondering now if I’ll be able to even get there using the current data schema. So before continuing with doing research, working out attribute expressions, and testing for my popups, I’d like to give you some history and what I’d like to accomplish. I’d appreciate if you can let me know if it’s feasible and what would be needed to make it happen.

Our current Capital Projects viewer that was created many years ago in SQL is here:

http://cip.durhamnc.gov/

In its current state, it hasn’t been possible to have a mapping component because the only geographic information included is a parcel PIN, and that won’t work and is blank for many projects. City management has requested that we create a more updated viewer that includes a map, and would like a dashboard that has charts and graphs as well. Phase 1 of the overall project is creating the viewer using existing data. For the mapping component, I created polygon and point (with many points for multipart polygons) feature classes with the Account Number, then joined and related tables that were exported from the existing SQL database; I tried adding a Query Layer, but it wouldn’t publish.

The feature layer in the group that I shared with you is these polygon and point layers and the related tables. Ideally, I was hoping to make a Capital Projects Operations Dashboard<https://solutions.arcgis.com/local-government/help/capital-project-dashboard/> or use Web AppBuilder with the Dashboard theme. It would include filters for Service Area, Fiscal Year, Completed Projects, etc., the popup panel would include Phase data, and there would be charts for the Expenditure and Financials tables.

Is this even possible with how the data is currently setup? If not, do you have any suggestions on how to properly setup my data schema so that all needed data is in one table? I’ve viewed the feature layer, map, and dashboard included with the Capital Projects Dashboard Solution to see its schema and settings, and, along with your popup arcade expression help, it is greatly helpful for the popups, but not for the expenditure and financial data.

Phase 2 of the overall project is converting to a new system that can be used for all city projects, not just capital projects. My department was going to test drive Aurigo, PPM software and an Esri partner, but that has hit a snag.

Thank you so much for your time. From reading through the blog posts and help requests, you are an incredible asset to the community! I’ve been at my job for a little over a year now, and my whole team is learning all of the new AGO and Portal capabilities on the fly as needed.

Kelly Doss, GISP

GIS Analyst

Technology Solutions Dept.

City of Durham

101 City Hall Plaza

Durham, NC 27701

P 919-560-4122 x33218

F 919-560-4808

Kelly.Doss@durhamnc.gov

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

XanderBakker — Mon, 16 Dec 2019 13:51:17 GMT

Hi Kelly Doss ,

I tried to open the CIP viewer, but for some reason it won't open. I did not see an invitation in my messages when I entered ArcGIS Online. Can you share a link to your portal/group to see if I have access? I assume that I will be able to see the data there to be able to provide some feedback to the questions you asked.

As far as I understand your data in AGOL is static, since publishing query layers requires ArcGIS Enterprise with access to the SQL database. Also publishing data with joins can generate problems.

Operations Dashboard can provide the tools to visualize the data you have. Using solutions and the proposed data schema for local government can provide a lot of benefits. In case the Capital Project Dashboard does not contain all the features you want, you could consider providing an additional Dashboard to visualize the missing data.

Once I have access to the data, I can have a look at your data and see what is possible, but it might be a good thing to reach out to your local Esri account manager and sit down with him/her and a solution engineer from the local government team to have a better look at what you envision and learn about the options ArcGIS provides. This might be a good time to do that.

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

TimFlynn — Sat, 11 Dec 2021 14:06:24 GMT

Hello Xander,

I'm working on a similar project and trying to pull an inspection date out of a related table. My code is as follows:


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

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

return relatedrecords‍‍‍‍‍‍‍‍‍‍‍

When I test it returns results:

But in the pop up I've inserted the expression, it breaks the entire pop up and says No Information Available.

Where have I gone wrong?

Thanks,

Tim

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

XanderBakker — Thu, 20 Feb 2020 14:05:05 GMT

Hi Tim Flynn ,

Small change required in the expression. You are returning "relatedrecords" which is a featureset you fetched on line 1 of your expression and you want to return "relatedinfo".

The featureset is shown in the test result, but a featureset cannot be shown in the pop-up. That is the reason.

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

TimFlynn — Thu, 20 Feb 2020 14:36:53 GMT

Xander, thank you so much! I knew it was something simple.

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

TimFlynn — Sat, 11 Dec 2021 14:06:27 GMT

Xander,

This works but now I've realized that for multiple inspections it will still only return the first inspection date. What do I need to add to return the most recent inspection date?

My code is as follows:


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

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

return relatedrecords‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I tried adding "Top(OrderBy(...),1)" but that doesn't seem to make a difference.

Thanks,

Tim

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

TimFlynn — Thu, 20 Feb 2020 15:21:08 GMT

Never mind Xander Bakker I figured it out! I simply added "insp_date DES" and that did the trick! Also added a nifty else statement for empty lists.

Thanks for all the help in your many responses on these forums.

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

XanderBakker — Sat, 11 Dec 2021 14:06:30 GMT

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;‍‍‍‍‍‍‍‍‍‍

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

XanderBakker — Thu, 20 Feb 2020 15:29:04 GMT

I didn't see your responde before, but I'm glad you resolved it. It was the same thing I was proposing.

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

TimFlynn — Thu, 20 Feb 2020 15:49:17 GMT

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.

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

XanderBakker — Thu, 20 Feb 2020 18:47:14 GMT

Hi Timothy.Flynn_ocfl ,

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

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

kmsmikrud — Fri, 29 May 2020 23:55:53 GMT

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

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

XanderBakker — Mon, 01 Jun 2020 22:20:45 GMT

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

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

XanderBakker — Sat, 11 Dec 2021 14:06:33 GMT

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.

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

kmsmikrud — Thu, 04 Jun 2020 14:56:36 GMT

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