topic Re: Arcade Expression throws Data Error / null in ArcGIS Online Questions

Arcade Expression throws Data Error / null

MattCreaney — Sat, 01 Aug 2020 02:54:11 GMT

I'm trying to cross reference work done on a site by the amount of time a vehicle was there. I have this Arcade script, which tests ok in the script editor but on the map data table shows Data Error and in the popup is null. Here's the script, can anyone please help to debug?

var Asset;
var DateOfVisit;

//Get Date of Commission, add to result
var DateOfCommission = Date(year($feature.CreationDate),month($feature.CreationDate),day($feature.CreationDate));
var result = "Date Of Commission = " + DateOfCommission;
Console($feature.CreationDate);
Console(DateOfCommission);

var VehicleIntersecting;
var FirstAsset;
var AssetGeometry;
var AssetArea;
var Buffer_Distance = 100;
var AssetID;
var BayID = $feature["bay_guid"];
Console(BayID);
var QueryBay = "GlobalID = '" + BayID + "'";
var QueryAsset;
Console(QueryBay);

// Get Associated Bay Record
var Bay = Filter(FeatureSetById($map, /* Production_database - Bay */ "Production_database_3775"), QueryBay);
Console(Bay)

// Find GUID of Associated Asset Record
for (var FilteredBay in Bay) {
var AssetID = FilteredBay.asset_guid
Console(AssetID);
var QueryAsset = "GlobalID = '" + AssetID + "'";
Console(QueryAsset);

// Get Associated Asset Record
var Asset = Filter(FeatureSetById($map, /* Production_database */ "Production_database_8049"), QueryAsset);
Console(Asset);
for (var FilteredAsset in Asset) {
var FirstAsset = First(Asset)
var AssetGeometry = Geometry(FirstAsset);
Console(AssetGeometry);

//Buffer 50m around Asset
var Asset_Area = Buffer(FirstAsset, Buffer_Distance, 'meters');
Console(Asset_Area);

//Find Vehicles that intersect Asset Bugger
var VehicleIntersecting = Intersects(FeatureSetById($map, /* Vehicle Locations */ "Vehicle_Locations_8121"), Asset_Area);
for (var VehicleStop in VehicleIntersecting) {

//Get Date of Visit
var DateOfVisit = Date(year(VehicleStop.date_visited), month(VehicleStop.date_visited), day(VehicleStop.date_visited));
Console (DateOfVisit);
Console (DateOfCommission);
// Add Date of Visit to Result
var result =+ " //// Date Of Visit = " + DateOfVisit;
Console(result);

//Add Visit Duration if Dates of Visit and Commission Match
if (DateOfVisit == DateOfCommission) {
var result =+ " //// Visit Duration = " + VehicleStop.visit_duration;
}
}

}
}
return result;

Re: Arcade Expression throws Data Error / null

MattCreaney — Sat, 11 Dec 2021 18:08:46 GMT

Updated code:

var Asset;
var DateOfVisit;
var IntersectCount;
var result;
var VehicleIntersecting;
var FirstAsset;
var AssetGeometry;
var AssetArea;
var Buffer_Distance = 100;
var AssetID;

//Get Date of Commission
var DateOfCommission = Text(Date(year($feature.CreationDate),month($feature.CreationDate),day($feature.CreationDate)));

Console($feature.CreationDate);
Console(DateOfCommission);

//Get Bay ID and set Query
var BayID = $feature["bay_guid"];
Console(BayID);
var QueryBay = "GlobalID = '" + BayID + "'";
var QueryAsset;
Console(QueryBay);

// Get Associated Bay Record
var Bay = Filter(FeatureSetById($map, /* Production_database - Bay */ "Production_database_3775"), QueryBay);
Console(Bay)

// Find GUID of Associated Asset Record and Set Query
for (var FilteredBay in Bay) {
    var AssetID = FilteredBay.asset_guid
    Console(AssetID);
    var QueryAsset = "GlobalID = '" + AssetID + "'";
    Console(QueryAsset);
    
    // Get Associated Asset Record
    var Asset = Filter(FeatureSetById($map, /* Production_database */ "Production_database_8049"), QueryAsset);
    Console(Asset);
    
    // Use first record (never more than one) and get geometry
    for (var FilteredAsset in Asset) {
        var FirstAsset = First(Asset)
        var AssetGeometry = Geometry(FirstAsset);
        Console(AssetGeometry);
        
        // Buffer 50m around Asset
        var Asset_Area = Buffer(FirstAsset, Buffer_Distance, 'meters');
        Console(Asset_Area);
        
        // Find Vehicles that intersect Asset Buffer
        var VehicleIntersecting = Intersects(FeatureSetById($map, /* Vehicle Locations */ "Vehicle_Locations_8121"), Asset_Area);
        var IntersectCount = count(VehicleIntersecting);
        Console(IntersectCount);
        
        // If an intersecting vehicle record is found
        if (IntersectCount > 0) {
            for (var VehicleStop in VehicleIntersecting) {
                
                //Get Date of Visit
                var DateOfVisit = Text(Date(year(VehicleStop.date_visited), month(VehicleStop.date_visited), day(VehicleStop.date_visited)));
                Console(DateOfVisit);
                Console(DateOfCommission);
                Console(result);
                
                // Set result as Visit Duration if Dates of Visit and Commission Match
                if (DateOfVisit == DateOfCommission) {
                    var result = "Visit Duration = " + VehicleStop.visit_duration;
                } else {
                    var result = "No Vehicle Data Found"
                }
            }
        } else {
            var result = "No Intersecting Vehicle Record Found"
        }        
    }
}
return result;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Result in Script Editor (correct):

Result in Data Table:

Popup is empty. Please help!!

Re: Arcade Expression throws Data Error / null

XanderBakker — Sat, 11 Dec 2021 18:08:48 GMT

Hi Matt Creaney ,

I would be nice to see what is written to the console. Could you post that too?

A couple of observations to start with:

On line 13 you extract year, month and day from a date to create a date and convert it to a text. You can apply the Text function directly on the data and supply a format if you only want to have the data and not the time
On line 21 you create a SQL expression using a GlobalID. This query will not return any results. You have to change the query to get results:

// either use:
var QueryBay = "GlobalID = @BayID";
// or complicate things and use:
var QueryBay = "GlobalID = '{" + Upper(BayID) + "}'";
‍‍‍‍

Do this also for line 33
On line 41, don't use a loop if you only want to use the first asset.
On line 60 apply Text directly on the data field and don't extract the year, month day to recreate the date

When testing the arcade expression in the editor it may provide you a result (No Intersecting Vehicle Record Found), but this is probably since the validation is less strict. The query on the GlobalID is not valid and that is probably causing to not have any results.

Re: Arcade Expression throws Data Error / null

MattCreaney — Sat, 11 Dec 2021 18:08:51 GMT

Xander Bakker‌

Thank you so much for your help. I'm very keen to resolve this issue. Apologies that my code is a little rough.

So now I have:

var Asset;
var DateOfVisit;
var IntersectCount;
var result;
var VehicleIntersecting;
var FirstAsset;
var AssetGeometry;
var AssetArea;
var Buffer_Distance = 100;
var AssetID;

//Get Date of Commission
var DateOfCommission = Text($feature.CreationDate);

Console(DateOfCommission);

//Get Bay ID and set Query
var BayID = $feature["bay_guid"];
Console(BayID);
var QueryBay = "GlobalID = @BayID";
var QueryAsset;
Console(QueryBay);

// Get Associated Bay Record
var Bay = Filter(FeatureSetById($map, /* Production_database - Bay */ "Production_database_3775"), QueryBay);
Console(Bay)

// Find GUID of Associated Asset Record and Set Query
for (var FilteredBay in Bay) {
    var AssetID = FilteredBay.asset_guid
    Console(AssetID);
    var QueryAsset = "GlobalID = @AssetID";
    Console(QueryAsset);
    
    // Get Associated Asset Record
    var Asset = Filter(FeatureSetById($map, /* Production_database */ "Production_database_8049"), QueryAsset);
    Console(Asset);
    
    // Use first record (never more than one) and get geometry
    var FirstAsset = First(Asset)
    var AssetGeometry = Geometry(FirstAsset);
    Console(AssetGeometry);
    
    // Buffer 50m around Asset
    var Asset_Area = Buffer(FirstAsset, Buffer_Distance, 'meters');
    Console(Asset_Area);
    
    // Find Vehicles that intersect Asset Buffer
    var VehicleIntersecting = Intersects(FeatureSetById($map, /* Vehicle Locations */ "Vehicle_Locations_8121"), Asset_Area);
    var IntersectCount = count(VehicleIntersecting);
    Console(IntersectCount);
    
    // If an intersecting vehicle record is found
    if (IntersectCount > 0) {
        for (var VehicleStop in VehicleIntersecting) {
            
            //Get Date of Visit
            var DateOfVisit = Text(VehicleStop.date_visited);
            Console(DateOfVisit);
            Console(DateOfCommission);
            Console(result);
                
            // Set result as Visit Duration if Dates of Visit and Commission Match
            if (DateOfVisit == DateOfCommission) {
                var result = "Visit Duration = " + VehicleStop.visit_duration;
            } else {
                var result = "No Vehicle Data Found"
            }
        }
    } else {
        var result = "No Intersecting Vehicle Record Found"
    }
}
return result;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Tests ok, same as previously. The Console output is now:

2020-07-21T10:36:28+10:00 5c8fad47-691a-467c-a542-dc8014eadd1e GlobalID = @BayID object, FeatureSet dba7142e-50bd-43d8-9a3a-c7859a419fd1 GlobalID = @AssetID object, FeatureSet {"spatialReference":{"latestWkid":3857,"wkid":102100},"x":16259374.139800005,"y":-4055441.0997} {"spatialReference":{"latestWkid":3857,"wkid":102100},"rings":[[[16259474.139800005,-4055441.0997],[16259473.92569233,-4055447.640012923],[16259473.284286141,-4055454.152319222],[16259472.218328046,-4055460.6087322016],[16259470.732382633,-4055466.9816045105],[16259468.832812954,-4055473.2436465304],[16259466.527753256,-4055479.3680432364],[16259463.827074157,-4055485.328569022],[16259460.742340382,-4055491.0997],[16259457.286761235,-4055496.6567233023],[16259453.475134034,-4055501.975842901],[16259449.323780753,-4055507.03428151],[16259444.850478124,-4055511.810378119],[16259440.074381515,-4055516.283680748],[16259435.015942905,-4055520.435034029],[16259429.696823306,-4055524.2466612305],[16259424.139800005,-4055527.7022403786],[16259418.368669027,-4055530.7869741535],[16259412.408143241,-4055533.4876532513],[16259406.283746535,-4055535.7927129497],[16259400.021704515,-4055537.692282629],[16259393.648832206,-4055539.1782280407],[16259387.192419227,-4055540.2441861373],[16259380.680112928,-4055540.885592324],[16259374.139800005,-4055541.0997],[16259367.599487081,-4055540.885592324],[16259361.087180782,-4055540.2441861373],[16259354.630767804,-4055539.1782280407],[16259348.257895494,-4055537.692282629],[16259341.995853474,-4055535.7927129497],[16259335.871456768,-4055533.4876532513],[16259329.910930982,-4055530.7869741535],[16259324.139800005,-4055527.7022403786],[16259318.582776703,-4055524.2466612305],[16259313.263657104,-4055520.435034029],[16259308.205218494,-4055516.283680748],[16259303.429121885,-4055511.810378119],[16259298.955819257,-4055507.03428151],[16259294.804465976,-4055501.975842901],[16259290.992838774,-4055496.6567233023],[16259287.537259627,-4055491.0997],[16259284.452525852,-4055485.328569022],[16259281.751846753,-4055479.3680432364],[16259279.446787056,-4055473.2436465304],[16259277.547217377,-4055466.9816045105],[16259276.061271964,-4055460.6087322016],[16259274.995313868,-4055454.152319222],[16259274.35390768,-4055447.640012923],[16259274.139800005,-4055441.0997],[16259274.35390768,-4055434.559387077],[16259274.995313868,-4055428.047080778],[16259276.061271964,-4055421.5906677986],[16259277.547217377,-4055415.21779549],[16259279.446787056,-4055408.95575347],[16259281.751846753,-4055402.831356764],[16259284.452525852,-4055396.8708309783],[16259287.537259627,-4055391.0997],[16259290.992838774,-4055385.542676698],[16259294.804465976,-4055380.2235570992],[16259298.955819257,-4055375.1651184903],[16259303.429121885,-4055370.3890218814],[16259308.205218494,-4055365.915719252],[16259313.263657104,-4055361.764365971],[16259318.582776703,-4055357.95273877],[16259324.139800005,-4055354.4971596217],[16259329.910930982,-4055351.412425847],[16259335.871456768,-4055348.711746749],[16259341.995853474,-4055346.4066870506],[16259348.257895494,-4055344.507117371],[16259354.630767804,-4055343.0211719596],[16259361.087180782,-4055341.955213863],[16259367.599487081,-4055341.313807676],[16259374.139800005,-4055341.0997],[16259380.680112928,-4055341.313807676],[16259387.192419227,-4055341.955213863],[16259393.648832206,-4055343.0211719596],[16259400.021704515,-4055344.507117371],[16259406.283746535,-4055346.4066870506],[16259412.408143241,-4055348.711746749],[16259418.368669027,-4055351.412425847],[16259424.139800005,-4055354.4971596217],[16259429.696823306,-4055357.95273877],[16259435.015942905,-4055361.764365971],[16259440.074381515,-4055365.915719252],[16259444.850478124,-4055370.3890218814],[16259449.323780753,-4055375.1651184903],[16259453.475134034,-4055380.2235570992],[16259457.286761235,-4055385.542676698],[16259460.742340382,-4055391.0997],[16259463.827074157,-4055396.8708309783],[16259466.527753256,-4055402.831356764],[16259468.832812954,-4055408.95575347],[16259470.732382633,-4055415.21779549],[16259472.218328046,-4055421.5906677986],[16259473.284286141,-4055428.047080778],[16259473.92569233,-4055434.559387077],[16259474.139800005,-4055441.0997]]]} 0

Results still the same in the data table and the popup

Re: Arcade Expression throws Data Error / null

XanderBakker — Mon, 03 Aug 2020 23:17:38 GMT

Hi Matt Creaney ,

I see that the IntersectCount is returning 0, so according to the expression there are no vehicles found within the buffer.

To dig a little deeper it would help a lot to have access to (a part of) the data. If possible please create a group, share your data (or part of the data) to that group and invite me to that group using my user name "xbakker.spx".

Re: Arcade Expression throws Data Error / null

MattCreaney — Mon, 03 Aug 2020 23:29:13 GMT

Xander Bakker‌ - ok, thanks. Done.

Re: Arcade Expression throws Data Error / null

MattCreaney — Mon, 03 Aug 2020 23:30:08 GMT

Xander Bakker‌ - note, for the record that the script editor tests there is not a corresponding vehicle record.

Re: Arcade Expression throws Data Error / null

XanderBakker — Sat, 11 Dec 2021 18:08:54 GMT

Hi Matt Creaney ,

Sorry for the delay. I just had a look at the webmap, but I have a couple of questions:

None of the feature layers has any arcade expression defined.
Where should the sequence begin? When you click on a feature of the "Production database" layer?
I did not see a layer that has a field called "bay_guid", so it fails at line 18: $feature["bay_guid"]

I was assuming that you would start with the Production database point features and wanted to extract information from the vehicles, but I am getting the idea that maybe you are starting from a table, but it is not clear how you want to do that.

Below an example of a simple test to get a taste of the data:

Note that I use the FeatureSetByRelationshipName to get to the related data:

var Asset;
var DateOfVisit;
var IntersectCount;
var result;
var VehicleIntersecting;
var FirstAsset;
var AssetGeometry;
var AssetArea;
var Buffer_Distance = 100;
var AssetID;
var dateFormat = "MM/DD/Y";

//Get Date of Commission
Console($feature.CreationDate);
var DateOfCommission = Text($feature.CreationDate, dateFormat);
Console(DateOfCommission);

var bays = FeatureSetByRelationshipName($feature, "Bay");
Console("bays:" + count(bays));

var Asset_Area = Buffer($feature, Buffer_Distance, 'meters');
Console("Asset_Area:" + Area(Asset_Area, 'square-meters'));

var VehicleIntersecting = Intersects(FeatureSetById($map, /* Vehicle Locations */ "Vehicle_Locations_8121"), Asset_Area);
var IntersectCount = count(VehicleIntersecting);
Console("IntersectCount:" + IntersectCount);

result = IntersectCount + " vehicules found (" + DateOfCommission + ")";
if (IntersectCount > 0) {
    for (var VehicleStop in VehicleIntersecting) {
        
        //Get Date of Visit
        var DateOfVisit = Text(VehicleStop.date_visited, dateFormat);
        Console(DateOfVisit);
        Console(DateOfCommission);
        Console(result);
        
        // Set result as Visit Duration if Dates of Visit and Commission Match
        if (DateOfVisit == DateOfCommission) {
            result += TextFormatting.NewLine + " - Visit Duration = " + VehicleStop.visit_duration;
        } else {
            result += TextFormatting.NewLine + " - Ignore Visit Duration (" + DateOfVisit + ") = " + VehicleStop.visit_duration;
        }
    }
} else {
    result += TextFormatting.NewLine + " - No Intersecting Vehicle Record Found";
}        


return result;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Arcade Expression throws Data Error / null

MattCreaney — Wed, 05 Aug 2020 07:40:17 GMT

Hi Xander Bakker‌

Thanks for taking a look. In response to your questions:

None of the feature layers has any arcade expression defined.
- The expression was on the product commission ITP layer.
Where should the sequence begin? When you click on a feature of the "Production database" layer?
- When a specific product commission ITP record is clicked
I did not see a layer that has a field called "bay_guid", so it fails at line 18: $feature["bay_guid"]
- The field exists in the product commission ITP layer

I might try to make some changes to the script you wrote and see how I go. If you have any advice that would be greatly appreciated.

Re: Arcade Expression throws Data Error / null

MattCreaney — Sat, 11 Dec 2021 18:08:56 GMT

Hi Xander Bakker‌

I have updated my script with changes to the way I find related features and how date to text is formatted, but I still get the same result. As above, my script is located on the product commission ITP layer because this is where the specific record of the activity related to the vehicle stop exists. Apologies, my code is untidy.

var Asset;
var DateOfVisit;
var IntersectCount;
var result;
var VehicleIntersecting;
var FirstAsset;
var AssetGeometry;
var AssetArea;
var Buffer_Distance = 100;
var AssetID;

//Get Date of Commission
var DateOfCommission = Left(Text($feature.CreationDate),10);

Console(DateOfCommission);

//Get Bay ID and set Query
//var BayID = $feature["bay_guid"];
//Console("BayID = " + BayID);
//var QueryBay = "GlobalID = '" + BayID + "'";
//var QueryAsset;
//Console("QueryBay = " + QueryBay);

// Get Associated Bay Record
var Bay = FeatureSetByRelationshipName($feature, 'Bay');
Console("Bay record = " + Bay)
Console("Bay record count = " + Count(Bay))

// Find GUID of Associated Asset Record and Set Query
//for (var FilteredBay in Bay) {
//    var AssetID = FilteredBay.asset_guid
//    Console("AssetID = " + AssetID);
//    var QueryAsset = "GlobalID = '" + AssetID +"'";
//    Console("QueryAsset = " + QueryAsset);
    
    // Get Associated Asset Record
    var Asset = FeatureSetByRelationshipName(First(Bay), 'Assets');
    Console("Asset record = " + Asset);
    
    // Use first record (never more than one) and get geometry
//    var FirstAsset = First(Asset)
    var AssetGeometry = Geometry(First(Asset));
    Console("Asset Geometry is " + AssetGeometry);
    
    // Buffer 50m around Asset
    var Asset_Area = Buffer(First(Asset), Buffer_Distance, 'meters');
    Console("Asset Area is " + Asset_Area);
    
    // Find Vehicles that intersect Asset Buffer
    var VehicleIntersecting = Intersects(FeatureSetById($map, /* Vehicle Locations */ "Vehicle_Locations_8121"), Asset_Area);
    var IntersectCount = count(VehicleIntersecting);
    Console("Intersect Count = " + IntersectCount);
    
    // If an intersecting vehicle record is found
    if (IntersectCount > 0) {
        for (var VehicleStop in VehicleIntersecting) {
            
            //Get Date of Visit
            var DateOfVisit = Left(Text(VehicleStop.date_visited),10);
            Console("Date of Visit = " + DateOfVisit);
            Console("Date of Commission = " + DateOfCommission);
                
            // Set result as Visit Duration if Dates of Visit and Commission Match
            if (DateOfVisit == DateOfCommission) {
                var result = "Visit Duration = " + VehicleStop.visit_duration;
            }
        }
    } else {
        var result = "No Intersecting Vehicle Record Found"
    }
//}
return result;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Arcade Expression throws Data Error / null

XanderBakker — Sat, 08 Aug 2020 00:34:51 GMT

Hi mcreaney ,

I still haven't had much time to look at it, but I do see a couple of things. Most importantly, I don't really understand to what layer the "Assets" refer. Can you tell me what it is called in the web map? Are you sure they have geometry? When I look at what the geometry returns, I don't have any result.

Re: Arcade Expression throws Data Error / null

MattCreaney — Sat, 08 Aug 2020 00:42:04 GMT

Hi Xander Bakker‌

Assets are the production database layer which has geography.

Re: Arcade Expression throws Data Error / null

XanderBakker — Sat, 11 Dec 2021 18:08:59 GMT

Hi Matt Creaney ,

In that case, it does not make sense to start with the assets and find the bays and use the first bay to find the assets. Or does it?

Have a look at the code below and an example result to see if it comes anywhere near what you are looking for.

Using the following expression:

var Buffer_Distance = 100;
var dateFormat = "MM/DD/Y";
var result = "";

//Get Date of Commission
var DateOfCommission = Text($feature.CreationDate, dateFormat);
Console(DateOfCommission);

var Asset_buffer = Buffer($feature, Buffer_Distance, 'meters');
Console("Asset Area is " + Area(Asset_buffer, "square-meters"));

// Find Vehicles that intersect Asset Buffer
var VehicleIntersecting = Intersects(FeatureSetById($map, /* Vehicle Locations */ "Vehicle_Locations_8121", ["date_visited", "visit_duration"], false), Asset_buffer);
var IntersectCount = Count(VehicleIntersecting);
Console("Intersect Count = " + IntersectCount);
        
// If an intersecting vehicle record is found
var cnt = 0;
result = "Date of Commission: " + DateOfCommission + TextFormatting.NewLine;
if (IntersectCount > 0) {
	for (var VehicleStop in VehicleIntersecting) {
		
		//Get Date of Visit
		var DateOfVisit = Text(VehicleStop["date_visited"], dateFormat);
		Console("Date of Visit = " + DateOfVisit);
		Console("Date of Commission = " + DateOfCommission);
			
		// Set result as Visit Duration if Dates of Visit and Commission Match
		if (DateOfVisit == DateOfCommission) {
			result += " - Duration: " + VehicleStop["visit_duration"] + "h" + TextFormatting.NewLine;
		} else {
		    // switch this off, since you are not interesting in this
		    cnt += 1;
			//result += " - Duration: (" + DateOfVisit + ") = " + VehicleStop["visit_duration"] + "h" + TextFormatting.NewLine;
			Console("other date: " + VehicleStop["visit_duration"]);
		}
	}
    if (cnt > 0) {
        result += " - " + cnt + " vehicle stops found at different dates..." ;        
    }	
} else {
	// handle: no interecting vehicles found
	result = "No Intersecting Vehicle Records Found with the same date";
}

return result;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Arcade Expression throws Data Error / null

MattCreaney — Sun, 09 Aug 2020 23:56:04 GMT

Hi Xander Bakker‌ - thanks, thats exactly what I'm looking for, except I want the expression on the Product Commission ITP layer..

Re: Arcade Expression throws Data Error / null

XanderBakker — Mon, 10 Aug 2020 13:52:08 GMT

Hi Matt Creaney ,

Since the Product Commission ITP "layer" is a table, it does not have a pop-up. So it can't start there. I assume you start at the Production Database point feature layer. From your last code I deduce the following:

You read the creation date of the Production Database point feature the user clicks on
You get the related bay from the bay table
Based on the first related bay, you access the Production Database point feature layer again and get the first "asset"
You buffer the asset geometry and look for vehicle stops in the buffer
You return information of the vehicle stop when the date of visit is equal to the data of commission.

The part that I can't wrap my head around is why you would want to start with an asset (Production Database point feature) and use the bays to get back to a Production Database point feature (asset)?

Re: Arcade Expression throws Data Error / null

MattCreaney — Mon, 10 Aug 2020 21:04:07 GMT

Hi Xander Bakker‌

The expression I wrote is in the Product Commission ITP layer - I am testing through a web mapping application I've just added to the group. In other maps I have using the same database I have many many scripts that successfully execute although none of these access related tables. The popups are accessed through the relate table links.

In this application the expression is supposed to appear under the asset name..

Re: Arcade Expression throws Data Error / null

XanderBakker — Mon, 10 Aug 2020 22:57:41 GMT

Hi Matt Creaney ,

As far as I have seen you only shared a map with me and the layers. The map has two point feature layers:

Product database
Vehicle locations

And the map also has a list of tables. When I click on a feature of the product database not all tables are presented and related tables. Especially the table Product Commission ITP does not appear as related table in the map (no sure of this is because the list is too long).

In order to see how you workflow works, I would need access to the App that you showed. I still believe that it might be more effective to use the Arcade expression to extract all the necessary information and present it in the pop-up, although this depend how the application is configured.

Re: Arcade Expression throws Data Error / null

MattCreaney — Tue, 11 Aug 2020 21:13:19 GMT

Xander Bakker‌ An Asset (production database feature) has 1 or more Bays and a Bay has 1 or more Product Commission ITPs, so you have to get to the ITP via the Bay record.

I've shared the app to the group, not sure why you can't see it. Here's the URL ArcGIS Web Application

Re: Arcade Expression throws Data Error / null

XanderBakker — Sat, 11 Dec 2021 18:09:02 GMT

Hi Matt Creaney ,

I have no access to the WebApp.

Based on your last comment I did a little test, taking the production database feature (the asset) and get the related bays and for each bay get the related Product Commission ITP and for each Product Commission ITP get back to the assets (in this case I used a query based on the asset name) and did a buffer around each asset to get the vehicles within the buffer:

// An Asset (production database feature) has 1 or more Bays 
// and a Bay has 1 or more Product Commission ITPs, 
// so you have to get to the ITP via the Bay record.

var result = "";
var Buffer_Distance = 100;
var dateFormat = "MM/DD/Y";
var DateOfCommission = Text($feature.CreationDate, dateFormat);

result = "Asset OID:" + $feature.OBJECTID;
result += TextFormatting.NewLine + "Asset name:" + $feature["asset_name"];
result += TextFormatting.NewLine + "DateOfCommission:" + DateOfCommission;

var bays = FeatureSetByRelationshipName($feature, "Bay");
result += TextFormatting.NewLine + " # bays count:" + Count(bays);
for (var bay in bays) {
    result += TextFormatting.NewLine + " # bay.bay_number:" + bay.bay_number;
    var itps = FeatureSetByRelationshipName(bay, "Product_Commission_ITP");
    result += TextFormatting.NewLine + " ## itps count:" + Count(itps);
    for (var itp in itps) {
        var assetname = itp.asset_name;
        result += TextFormatting.NewLine + " ## itp asset_name:" + assetname;
        var sql = "asset_name = @assetname";
        var assets = Filter($layer, sql);
        result += TextFormatting.NewLine + " ## assets count:" + Count(assets);
        for (var asset in assets) {
            result += TextFormatting.NewLine + " ### asset OID:" + asset.OBJECTID;
            var assetbuffer = Buffer(asset, Buffer_Distance, 'meters');
            var vehicles = Intersects(FeatureSetById($map, "Vehicle_Locations_8121"), assetbuffer);
            result += TextFormatting.NewLine + " #### vehicles count:" + Count(vehicles);
            for (var vehicle in vehicles) {
                result += TextFormatting.NewLine + " ##### vehicle:" + vehicle.vehicle;
                var visited = Text(vehicle["date_visited"], dateFormat);
                var duration = vehicle["visit_duration"];
                result += TextFormatting.NewLine + " ##### visited:" + visited;
                result += TextFormatting.NewLine + " ##### duration:" + duration;
            }
        }
    }    
}

return result;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

However, no matter where I look it will only find a single asset:

Asset OID:2450
Asset name:RO5310
DateOfCommission:07/20/2020
# bays count:1
# bay.bay_number:1
## itps count:1
## itp asset_name:RO5310
## assets count:1
### asset OID:2450
#### vehicles count:4
##### vehicle:RV20 - Matt B
##### visited:07/20/2020
##### duration:2:08:35
##### vehicle:RV1 - Locky
##### visited:07/23/2020
##### duration:0:26:01
##### vehicle:RV10 - Nibs
##### visited:07/27/2020
##### duration:0:12:44
##### vehicle:RV9 - Macca
##### visited:07/30/2020
##### duration:3:54:38 ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Can you point out a location where you have either multiple bays or multiple product commission ITP's?

Re: Arcade Expression throws Data Error / null

MattCreaney — Wed, 12 Aug 2020 00:16:36 GMT

Sorry Xander Bakker‌ - I should say that an asset has a 1:M relationship to bays and a bay has a 1:M relationship to product commission ITP but this can be zero (commission not yet completed).

Asset RG-2-622 has multiple bays and these do have ITPs although I'm not sure if the dates will line up as these records were uploaded rather than created on site. Not sure if this affects your test.