Arcade Join Tables

[RobertAnderson3]

I have a dashboard set up to show my Survey123 data and am using an Arcade expression to join tables from repeats to the base layer to attach the date information to filter all the tables in my dashboard.

I had this expression working properly but it was very poorly optimized and I was getting consistent complaints about that fact, I moved to update the code from another post I found here on the Esri Community and the speed has dramatically improved. However I am finding occasionally rows are missing from the table showing the repeat information and I am not sure why.

My code is here:

function Memorize(fs) {
    var temp_dict = {
        fields: Schema(fs)['fields'],
        geometryType: '',
        features: []
    }

    for (var f in fs) {
        var attrs = {}

        for (var attr in f) {
            attrs[attr] = Iif(TypeOf(f[attr]) == 'Date', Number(f[attr]), f[attr])
        }

        Push(
            temp_dict['features'],
            {attributes: attrs}
        )
    }

    return FeatureSet(Text(temp_dict))
}

var portal = Portal("https://www.arcgis.com/");
var polyfs = Memorize(FeatureSetByPortalItem(
    portal,
    "(valid itemID is pasted here)",
    0,
    ["*"],
    false
  )
);

var tablefs = Memorize(FeatureSetByPortalItem(
    portal,
    "(valid itemID is pasted here)",
    2,
    ["*"],
    false
  )
);

var joinedDict = {
    fields: [
        { name: "FeatureID", type: "esriFieldTypeString" },
        { name: "Date", type: "esriFieldTypeDate" },	
        { name: "Location", type: "esriFieldTypeString" },
        { name: "Vehicle", type: "esriFieldTypeString" },	
        { name: "VehHours", type: "esriFieldTypeDouble" },	        
        { name: "VehAct", type: "esriFieldTypeString" },	
    ],
    'geometryType': '',
    features:[]
};

// Populate Feature Array
for (var v in tablefs) {
    var tableID = v["parentglobalid"]
    for (var p in Filter(polyfs, "globalid =@tableID")){
        Push(
          joinedDict['features'], 
          {
            attributes: {
                FeatureID: tableID,
                Date: Number(p["WO_StartDate"]),
                Location: p["Location"],
                Vehicle: v["Vehicle"],
                VehHours: v["veh_time"],
                VehAct: v["veh_act"],
            }
        }
      )
    }
}

// Return dictionary cast as a feature set 
return FeatureSet(Text(joinedDict));

[RPGIS]

Here is what I use which might be of some help.

This function gets field names in a feature that are not in the list of omitted field names. If there isn't a list of field names to omit, include all field names.

function GetFieldNames( InputFeature , OmitFields ){
    var FieldNames = [ ]
    var FeatureInfo = Schema(InputFeature).fields
    for ( var i in FeatureInfo ){
        // Get the field name of the input feature
        var fieldname = FeatureInfo[ i ].name
        if ( Boolean( OmitFields ) ) {
            if ( Includes( OmitFields , fieldname ) == False ) { Push( FieldNames , fieldname ) }
            }
        else { Push( FieldNames , fieldname ) }
    return FieldNames
    }

This function finds all matching field names between two features.

// Get matching field names found in both lists
function GetMatchingFields( FieldListA, FieldListB ){
    var MatchingFields = [ ]
    // Loop through the list of field names in list A
    for ( var i in FieldListA ){
        var fieldname = FieldListA[i]
        /* If list of field names in list B includes the field name in list A
           then append the field name to the matching list of field names */
        if ( Includes( FieldListB , fieldname ) ){ Push( MatchingFields, fieldname ) }
        }
    return MatchingFields
    }

This one gets all attributes from a feature based on input field names.

// Get feature attributes based on field list
function GetFeatureAttributes( FieldList ){
    Expects( $feature , '*' )
    var Attributes = Dictionary( $feature ).attributes
    var SpecificAttributes = { }
    if ( IsEmpty( FieldList ) == False ){
        for ( var f in FieldList ){
            SpecificAttributes[ FieldList[ f ] ] = Attributes[ FieldList[ f ] ]
            }
        }
    else { SpecificAttributes = Attributes }
    return SpecificAttributes
    }

These may be of some use to you but I have used these to automate a lot of attribute rules. It also takes the guess work out of having to do things manually.

You can also use the get matching field names function with the get field names function(s) as inputs.

MatchingFieldNames = GetMatchingFields( GetFieldNames( InputFeature , OmitFields ), GetFieldNames( InputFeature , OmitFields ))

[jcarlson]

I've noticed sometimes my Memorize function doesn't play nice with Domains.

/*
Memorize a featureset.
Params:
    `fs`: A FeatureSet
    `retainDomains`: Boolean; whether or not to retain object domains. Memorizing a Featureset will drop out-of-domain records!
*/
function Memorize(fs, retainDomains) {
    var temp_dict = {
        fields: Schema(fs)['fields'],
        geometryType: '',
        features: []
    }

    if (!retainDomains) {
        for (var fld in temp_dict['fields']) {
            if (HasKey(temp_dict['fields'][fld], 'domain')) {
                temp_dict['fields'][fld]['domain'] = null
            }
        }
    }

    for (var f in fs) {
        var attrs = {}

        for (var attr in f) {
            attrs[attr] = Iif(TypeOf(f[attr]) == 'Date', Number(f[attr]), f[attr])
        }

        Push(
            temp_dict['features'],
            {attributes: attrs}
        )
    }

    return FeatureSet(Text(temp_dict))
}

 Try that version of the function and put false for the retainDomains parameter, see if that helps.

[RobertAnderson3]

Thank you so much, seeing the addition of the retainDomains parameter is a big help as I remember this was an issue I ran into when I first implemented this, clearly I didn't completely fix the issue though.

It seems that's likely what the issue was, I ended up switching from the ["*"] to return all fields on my polyfs layer and it then started working properly.

I'll have to give it a shot with the retainDomains parameter being added, I feel like that will be a good thing to include in my code going forward as Survey123 will often have domains.

[RobertAnderson3]

Hi Josh,

If I add the code for the retainDomains and set it to false, I get Execution error - Object is immutable. However if I set it to true it will run, but not provide the right results. No matter how I get the if statement to evaluate to false it gives me the error.

    if (!retainDomains) {
        for (var fld in temp_dict['fields']) {
            if (HasKey(temp_dict['fields'][fld], 'domain')) {
                temp_dict['fields'][fld]['domain'] = null
            }
        }
    }

..........

var polyfs = Memorize(FeatureSetByPortalItem(
    portal,
    "validItemID",
    0,
    ["*"],
    false
  ),false
);

[jcarlson]

Strange...

Any chance the layer is public? I'd love to test stuff against the actual data.