Arcade Error: Test execution error: Execution error - Cannot access property of null object. Verify test data.

[ZachBodenner]

Hello,

I have a fairly long Arcade expression that is designed to use in the Arcade popup element (for HTML access). In a web service/web map, I have a parcel layer and three tables (Residential, Multi Family, and Commercial). The parcel layer is related to all three tables through the field "PID". In theory, there should be no overlap - if a parcel has a related record in Residential, it should not have one in Commercial. I think there might actually be some overlap, but I think the expression should account for that. What I want to have happen is to return a table constructed of HTML if a filter match is found.

var resProperties = FeatureSetByName($map, "Residential",['PID','Actual_Age','Total_Baths','Total_Bedrm','GB_Area','Style','Assessment_Year']);
var multiFamilyProperties = FeatureSetByName($map, "Multi-Family",['PID','Actual_Age','Bldg','Total_Floor_Area','Assessment_Year','Nbr_Story']);
var commericalProperties = FeatureSetByName($map, "Commerical",['PID','Actual_Age','Bldg','Total_Floor_Area','Assessment_Year','Nbr_Story']);
//var salesHistory = FeatureSetByName($map, "Sales History",['PID','Sale_Date','Sale_Price']);

var parcelPID = $feature.PID;

//var resProperties = FeatureSetByName($map, "Residential",['PID','Actual_Age','Total_Baths','Total_Bedrm','GB_Area','Style','Assessment_Year'])
//var parcelPID = $feature.PID;
//var PIDfilter = First(Filter(resProperties, "PID = @parcelPID"));
//var test = PIDfilter['Assessment_Year']

var PIDres = First(Filter(resProperties, "PID = @parcelPID"));
var PIDmf = First(Filter(multiFamilyProperties, "PID = @parcelPID"));
var PIDcomm = First(Filter(commericalProperties, "PID = @parcelPID"));
//var PIDsales = First(Filter(salesHistory, "PID = @parcelPID"));

var resAge = PIDres['Actual_Age'];
var resBaths = PIDres['Total_Baths'];
var resBedrm = PIDres['Total_Bedrm'];
var resGBArea = PIDres['GB_Area'];
var resStyle = PIDres['Style'];
var resAssessYr = PIDres['Assessment_Year'];

var mfAge = PIDmf['Actual_Age'];
var mfBldg = PIDmf['Bldg'];
var mfTotalFloor = PIDmf['Total_Floor_Area'];
var mfAssessYr = PIDmf['Assessment_Year'];
var mfNbrStory = PIDmf['Nbr_Story'];

var commAge = PIDcomm['Actual_Age'];
var commBldg = PIDcomm['Bldg'];
var commTotalFloor = PIDcomm['Total_Floor_Area'];
var commAssessYr = PIDcomm['Assessment_Year'];
var commNbrStory = PIDcomm['Nbr_Story'];


var resHTML = `<div style="background-color:#3f540f;padding:5px;">
    <span style="color:#ffffff;font-size:18px;"><strong>Building Information</strong></span>
</div>
<figure style="width:100%;">
    <figure>
        <figure class="table">
            <table>
                <tbody>
                    <tr>
                        <td>
                            <span style="font-size:16px;"><strong>Building Type</strong></span>
                        </td>
                        <td style="padding-right:175px;">
                            <span style="font-size:16px;">Single Family Residential</span>
                        </td>
                    </tr>
                    <tr>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;"><strong>Building Style</strong></span>
                        </td>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;">${resStyle}</span>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            <span style="font-size:16px;"><strong>Year Built</strong></span>
                        </td>
                        <td>
                            <span style="font-size:16px;">${resAge}</span>
                        </td>
                    </tr>
                    <tr>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;"><strong>Total Building Area</strong></span>
                        </td>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;">${resGBArea}</span>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            <span style="font-size:16px;"><strong>Total Bedrooms</strong></span>
                        </td>
                        <td>
                            <span style="font-size:16px;">${resBedrm}</span>
                        </td>
                    </tr>
                    <tr>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;"><strong>Total Bathrooms</strong></span>
                        </td>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;">${resBaths}</span>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            <span style="font-size:16px;"><strong>Assessment Year</strong></span>
                        </td>
                        <td>
                            <span style="font-size:16px;">${resAssessYr}</span>
                        </td>
                    </tr>
                   </tbody>
            </table>
        </figure>
    </figure>
</figure>
`

var mfHTML = `
<div style="background-color:#3f540f;padding:5px;">
    <span style="color:#ffffff;font-size:18px;"><strong>Building Information</strong></span>
</div>
<figure style="width:100%;">
    <figure>
        <figure class="table">
            <table>
                <tbody>
                    <tr>
                        <td>
                            <span style="font-size:16px;"><strong>Building Type</strong></span>
                        </td>
                        <td style="padding-right:175px;">
                            <span style="font-size:16px;">Multi-Family Residential</span>
                        </td>
                    </tr>
                    <tr>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;"><strong>Building Name</strong></span>
                        </td>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;">${mfBldg}</span>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            <span style="font-size:16px;"><strong>Year Built</strong></span>
                        </td>
                        <td>
                            <span style="font-size:16px;">${mfAge}</span>
                        </td>
                    </tr>
                    <tr>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;"><strong>Total Floor Area</strong></span>
                        </td>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;">${mfTotalFloor}</span>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            <span style="font-size:16px;"><strong>Number of Stories</strong></span>
                        </td>
                        <td>
                            <span style="font-size:16px;">${mfNbrStory}</span>
                        </td>
                    </tr>
                    <tr>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;"><strong>Assessment Year</strong></span>
                        </td>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;">${mfAssessYr}</span>
                        </td>
                    </tr>
                   </tbody>
            </table>
        </figure>
    </figure>
</figure>
`

var commHtml = `<div style="background-color:#3f540f;padding:5px;">
    <span style="color:#ffffff;font-size:18px;"><strong>Building Information</strong></span>
</div>
<figure style="width:100%;">
    <figure>
        <figure class="table">
            <table>
                <tbody>
                    <tr>
                        <td>
                            <span style="font-size:16px;"><strong>Building Type</strong></span>
                        </td>
                        <td style="padding-right:175px;">
                            <span style="font-size:16px;">Commercial</span>
                        </td>
                    </tr>
                    <tr>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;"><strong>Building Name</strong></span>
                        </td>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;">${commBldg}</span>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            <span style="font-size:16px;"><strong>Year Built</strong></span>
                        </td>
                        <td>
                            <span style="font-size:16px;">${commAge}</span>
                        </td>
                    </tr>
                    <tr>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;"><strong>Total Floor Area</strong></span>
                        </td>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;">${commTotalFloor}</span>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            <span style="font-size:16px;"><strong>Number of Stories</strong></span>
                        </td>
                        <td>
                            <span style="font-size:16px;">${commNbrStory}</span>
                        </td>
                    </tr>
                    <tr>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;"><strong>Assessment Year</strong></span>
                        </td>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;">${commAssessYr}</span>
                        </td>
                    </tr>
                   </tbody>
            </table>
        </figure>
    </figure>
</figure>`

var decideHTML

if (PIDres['PID'] != '' ) {
   decideHTML += resHTML 
}
if (PIDmf['PID'] != ''){
    decideHTML += mfHTML
}

if (PIDcomm['PID'] != ''){
    decideHTML += commHtml
}


return { 
	type : 'text', 
	text : decideHTML
}

I feel like this should be working but I get "Test execution error: Execution error - Cannot access property of null object. Verify test data." when trying to run the expression. I think the issue is related to the filter Interestingly, if I reconstruct the bare bones of this in a regular Arcade expression, the filter seems to work fine and I can get individual attributes from the related table. An example of something that works in the Arcade Attribute Expression: 

var table = FeatureSetByName($map, "Residential",['Assessment_Year','Style'])
var thisPID = $feature.PID;
var PIDfilter = First(Filter(table, "PID = @thisPID"));
var test = PIDfilter['Assessment_Year']
return test

So I'm not 100% sure why the error is occurring. 

[dgiersz_cuyahoga]

I think it is because, like you said, any given PID is only in one table. Which means when you check all 3, then 2 of them will return null (lines 13-15).  Then you try to set variables using a property of those nulls.

[ZachBodenner]

That sounds like it could be correct, but that's what I'm trying t check against  with the if statements at the end. Did I just structure them poorly?

[dgiersz_cuyahoga · Accepted solution]

Yeah, I would check them right after setting the variable.

var PIDres = First(Filter(resProperties, "PID = @parcelPID"));
var PIDmf = First(Filter(multiFamilyProperties, "PID = @parcelPID"));
var PIDcomm = First(Filter(commericalProperties, "PID = @parcelPID"));

if (IsEmpty(PIDres) == false){
     //set your variables
     }
elif (IsEmpty(PIDmf) == false){
     //set your variables
     }
elif (IsEmpty(PIDcomm) == false){
     //set your variables
     }
else{
     return "No PID found"
}
//build your table with the variables

This would also allow you to just use one set of variables, not a unique set for each PID type, and one set of table HTML with those new variables.

[ZachBodenner]

Thanks for the help! If you're curious about my final expression that I got to work, here it is:

// get tables
var resProperties = FeatureSetByName($map, "Residential",['PID','Actual_Age','Total_Baths','Total_Bedrm','GB_Area','Style','Assessment_Year']);
var multiFamilyProperties = FeatureSetByName($map, "Multi-Family",['PID','Actual_Age','Building_Name','Total_Floor_Area','Assessment_Year','Nbr_Story']);
var commericalProperties = FeatureSetByName($map, "Commerical",['PID','Actual_Age','Building_Name','Total_Floor_Area','Assessment_Year','Nbr_Story']);
// Identify PID and create final HTML variable
var parcelPID = $feature.PID;
var decideHTML
// Filter statements
var PIDres = First(Filter(resProperties, "PID = @parcelPID"));
var PIDmf = First(Filter(multiFamilyProperties, "PID = @parcelPID"));
var PIDcomm = First(Filter(commericalProperties, "PID = @parcelPID"));

// Check if there's a matching PID in the Residential table
if (IsEmpty(PIDres)==false){
  //if yes, establish variables
var resAge = PIDres['Actual_Age'];
var resBaths = PIDres['Total_Baths'];
var resBedrm = PIDres['Total_Bedrm'];
var resGBArea = Text(PIDres['GB_Area'],'#,###'+" Sq.Ft.");
var resStyle = PIDres['Style'];
var resAssessYr = PIDres['Assessment_Year'];
  // if yes, final HTML
var resHTML = `<div style="background-color:#3f540f;padding:5px;">
    <span style="color:#ffffff;font-size:18px;"><strong>Building Information</strong></span>
</div>
<figure style="width:100%;">
    <figure>
        <figure class="table">
            <table>
                <tbody>
                    <tr>
                        <td>
                            <span style="font-size:16px;"><strong>Building Type</strong></span>
                        </td>
                        <td style="padding-right:175px;">
                            <span style="font-size:16px;">Single Family Residential</span>
                        </td>
                    </tr>
                    <tr>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;"><strong>Building Style</strong></span>
                        </td>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;">${resStyle}</span>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            <span style="font-size:16px;"><strong>Year Built</strong></span>
                        </td>
                        <td>
                            <span style="font-size:16px;">${resAge}</span>
                        </td>
                    </tr>
                    <tr>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;"><strong>Total Building Area</strong></span>
                        </td>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;">${resGBArea}</span>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            <span style="font-size:16px;"><strong>Total Bedrooms</strong></span>
                        </td>
                        <td>
                            <span style="font-size:16px;">${resBedrm}</span>
                        </td>
                    </tr>
                    <tr>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;"><strong>Total Bathrooms</strong></span>
                        </td>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;">${resBaths}</span>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            <span style="font-size:16px;"><strong>Assessment Year</strong></span>
                        </td>
                        <td>
                            <span style="font-size:16px;">${resAssessYr}</span>
                        </td>
                    </tr>
                   </tbody>
            </table>
        </figure>
    </figure>
</figure>
`
// make decideHTML equal to final Res HTML
  decideHTML = resHTML
}

// Check if there's a matching PID in the Multifamily table
else if (IsEmpty(PIDmf)==false){
  //if yes, establish variables
var mfAge = PIDmf['Actual_Age'];
var mfBldg = PIDmf['Building_Name'];
var mfTotalFloor = Text(PIDmf['Total_Floor_Area'],'#,###'+" Sq.Ft.");
var mfAssessYr = PIDmf['Assessment_Year'];
var mfNbrStory = PIDmf['Nbr_Story'];
  // if yes, final HTML
var mfHTML = `<div style="background-color:#3f540f;padding:5px;">
    <span style="color:#ffffff;font-size:18px;"><strong>Building Information</strong></span>
</div>
<figure style="width:100%;">
    <figure>
        <figure class="table">
            <table>
                <tbody>
                    <tr>
                        <td>
                            <span style="font-size:16px;"><strong>Building Type</strong></span>
                        </td>
                        <td style="padding-right:175px;">
                            <span style="font-size:16px;">Multi-Family Residential</span>
                        </td>
                    </tr>
                    <tr>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;"><strong>Building Name</strong></span>
                        </td>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;">${mfBldg}</span>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            <span style="font-size:16px;"><strong>Year Built</strong></span>
                        </td>
                        <td>
                            <span style="font-size:16px;">${mfAge}</span>
                        </td>
                    </tr>
                    <tr>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;"><strong>Total Floor Area</strong></span>
                        </td>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;">${mfTotalFloor}</span>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            <span style="font-size:16px;"><strong>Number of Stories</strong></span>
                        </td>
                        <td>
                            <span style="font-size:16px;">${mfNbrStory}</span>
                        </td>
                    </tr>
                    <tr>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;"><strong>Assessment Year</strong></span>
                        </td>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;">${mfAssessYr}</span>
                        </td>
                    </tr>
                   </tbody>
            </table>
        </figure>
    </figure>
</figure>
`
// make decideHTML equal to final Res HTML
  decideHTML = mfHTML
}

// Check if there's a matching PID in the Commercial table
else if (IsEmpty(PIDcomm)==false){
  //if yes, establish variables
var commAge = PIDcomm['Actual_Age'];
var commBldg = PIDcomm['Building_Name'];
var commTotalFloor = Text(PIDcomm['Total_Floor_Area'],'#,###'+" Sq.Ft.");
var commAssessYr = PIDcomm['Assessment_Year'];
var commNbrStory = PIDcomm['Nbr_Story'];
  //if yes, final HTML
var commHTML = `<div style="background-color:#3f540f;padding:5px;">
    <span style="color:#ffffff;font-size:18px;"><strong>Building Information</strong></span>
</div>
<figure style="width:100%;">
    <figure>
        <figure class="table">
            <table>
                <tbody>
                    <tr>
                        <td>
                            <span style="font-size:16px;"><strong>Building Type</strong></span>
                        </td>
                        <td style="padding-right:175px;">
                            <span style="font-size:16px;">Commercial</span>
                        </td>
                    </tr>
                    <tr>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;"><strong>Building Name</strong></span>
                        </td>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;">${commBldg}</span>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            <span style="font-size:16px;"><strong>Year Built</strong></span>
                        </td>
                        <td>
                            <span style="font-size:16px;">${commAge}</span>
                        </td>
                    </tr>
                    <tr>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;"><strong>Total Floor Area</strong></span>
                        </td>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;">${commTotalFloor}</span>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            <span style="font-size:16px;"><strong>Number of Stories</strong></span>
                        </td>
                        <td>
                            <span style="font-size:16px;">${commNbrStory}</span>
                        </td>
                    </tr>
                    <tr>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;"><strong>Assessment Year</strong></span>
                        </td>
                        <td style="background-color:#e1e1e1;">
                            <span style="font-size:16px;">${commAssessYr}</span>
                        </td>
                    </tr>
                   </tbody>
            </table>
        </figure>
    </figure>
</figure>`
// make decideHTML equal to final Res HTML
  decideHTML = commHTML
}

return { 
	type : 'text', 
	text : decideHTML
}