Arcade Expression throws Data Error / null

3249
21
07-31-2020 07:54 PM
MattCreaney
Occasional Contributor

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;

Tags (1)
0 Kudos
21 Replies
MattCreaney
Occasional Contributor

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

0 Kudos
XanderBakker
Esri Esteemed Contributor

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.

MattCreaney
Occasional Contributor

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

0 Kudos
XanderBakker
Esri Esteemed Contributor

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

0 Kudos
MattCreaney
Occasional Contributor

Xander Bakker‌ - ok, thanks.  Done.

0 Kudos
MattCreaney
Occasional Contributor

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

0 Kudos
XanderBakker
Esri Esteemed Contributor

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;
MattCreaney
Occasional Contributor

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.

0 Kudos
MattCreaney
Occasional Contributor

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