Integer Index expected or Invalid where clause

[anilkumar]

Hello all,

I have a map with two-point layers and a relational table. I am writing calculation rule to generate a value 'rate' based on relationship and other values, also retrieving

var fQ = $feature.LQ;
var fW = $feature.fW;
var wM = $feature.wM;
var ci = $feature.ci;
var eff = $feature.ec;
var FT = null;
var MatType = null;

// Treat empty values as null
if (IsEmpty(ci)) {
    ci = null;
}
if (IsEmpty(eff)) {
    eff = null;
}

// Fetch related poles based on GLOBALID
var gId = $feature.pGLOBALID;
var rP = Filter(FeatureSetByName($datastore, 'SJ', ['GLOBALID', 'FT', 'Mat'], false), 'GLOBALID = @GID');

// Check if rP is not empty and assign values accordingly
if (Count(rP) > 0) {
    var pole = First(rP);
    MatType = pole.Mat;
    FT = pole.FT;
}

// Check if either MatType or FT is null or empty, then return default value
if (IsEmpty(MatType) || IsEmpty(FT)) {
    return '0';  // Default rate
}

// Construct the SQL WHERE clause incrementally, handling null values and data types appropriately
var a = [];
if (!IsEmpty(fQ)) {
    a.push('fQ = @fQ');
}
if (!IsEmpty(fW)) {
    a.push('fW = @FW');
}
if (!IsEmpty(wM)) {
    a.push('wM = @wM');
}
if (!IsEmpty(ci)) {
    a.push('ci = @CI');
}
if (!IsEmpty(eff)) {
    a.push('ec = @eff');
}
if (!IsEmpty(FT)) {
    a.push('FT = @ft');
}
if (!IsEmpty(MatType)) {
    a.push('MatTYPE = @MatType');
}

var whereClause = Join(a, ' AND ');

// Filter related units based on the constructed SQL WHERE clause
var relatedUnits = Filter(FeatureSetByName($datastore, 'SLC', ['*'], false), whereClause);

// Check if relatedUnits is not null and not empty, then return the rate
if (relatedUnits != null && Count(relatedUnits) > 0) {
    var unit = First(relatedUnits);
    return unit.Code;
} else {
    return '0';  // Default rate
}

status based on availability of other field values. I am either getting Invalid where clause or integer index expected with unidentifiable code line for errors. I am using different combination of data types as short (ci,eff) and Text (code and FT). I'm trying to calculate relationship value and calculate rate value in the same execution of Arcade expression. Above is the calculation rule attached:

[MikeMillerGIS · Accepted solution]

Don't call Count, this trigger an additional db query, change to this

// Check if rP is not empty and assign values accordingly
var pole = First(rP);
if (!IsEmpty(pole){
    MatType = pole.Mat;
    FT = pole.FT;
}

This is not JS, you do not call array.push, change to Push(array,value)

Push(a, 'fQ = @fQ');

Again, not JS, there is no Join, you need to use concat

var whereClause = Concatenate(a, ' AND ');

Don't call count

var unit = First(relatedUnits);
if (!IsEmpty(unit){
    return unit.Code;
} else {
    return '0';  // Default rate
}

list of arcade functions - https://developers.arcgis.com/arcade/function-reference/text_functions/ 

[anilkumar]

Thank you @MikeMillerGIS for the correction related to Arcade Expressions for count and where clause. I am trying to retrieve the rate based on where conditions and relate. still not able to generate valid rate other than zero.

var fQ = $feature.LQ;
var fW = $feature.fW;
var wM = $feature.wM;
var ci = $feature.ci;
var eff = $feature.ec;
var FT = null;
var MatType = null;


// Check for null values and set defaults
if (IsEmpty(fQ)) {
    fQ = null;
    Console(""fQ is null"");
}
if (IsEmpty(fW)) {
    fW = null;
    Console(""fW is null"");
}
if (IsEmpty(wM)) {
    wM = null;
    Console(""wM is null"");
}
if (IsEmpty(ci)) {
    ci = 'NA';
    Console(""ci is set to 'NA'"");
}
if (IsEmpty(eff)) {
    eff = null;
    Console("eff is null");
}

// Fetch related poles based on POLE_GLOBALID
var gId = $feature.pGLOBALID;
if (IsEmpty(gId)) {
    gId = null;
    Console(""gId is null"");
} else {
    var relatedPoles = Filter(FeatureSetByName($datastore, 'SJ', ['GLOBALID', 'FT', 'MatType'], false), 'GLOBALID = @gId');
    // Check if relatedPoles is not empty and assign values accordingly
    if (!IsEmpty(relatedPoles)) {
        var pole = First(relatedPoles);
        if (!IsEmpty(pole)) {
            MatType = pole.Mat;
            fType = pole.FT;
        }
    }
}

// Check if either MatType or fType is null or empty, then return default value
if (IsEmpty(MatType) || IsEmpty(fType)) {
    return '0';  // Default rate code
}

// Construct the SQL WHERE clause incrementally, handling null values and data types appropriately
var conditions = [];
if (!IsEmpty(fQ)) {
    Push(conditions, 'fQ = @fQ');
}
if (!IsEmpty(fW)) {
    Push(conditions, 'fw = @FW');
}
if (!IsEmpty(wM)) {
    Push(conditions, 'wM = @wM');
}
if (!IsEmpty(ci)) {
    Push(conditions, 'ci = @CI');
}
if (!IsEmpty(eff)) {
    Push(conditions, 'ec = @eff');
}
if (!IsEmpty(fT)) {
    Push(conditions, 'FT = @ft');
}
if (!IsEmpty(matType)) {
    Push(conditions, 'MatTYPE = @matType');
}

var whereClause = Concatenate(conditions, ' AND ');
Console(""whereClause: "" + whereClause);

// Filter related units based on the constructed SQL WHERE clause
var relatedUnits = Filter(FeatureSetByName($datastore, 'SLC', ['*'], false), whereClause);

// Check if relatedUnits is not empty, then return the rate code
if (!IsEmpty(relatedUnits)) {
    var unit = First(relatedUnits);
    if (!IsEmpty(unit)) {
        return unit.CODE;
    } else {
        Console(""No units found matching the whereClause"");
        return '0';  // Default code
    }
} else {
    Console(""relatedUnits is null or empty"");
    return '0';  // Default rate
}

[MikeMillerGIS]

I am not sure, can you share your database?