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 = <a href="https://community.esri.com/t5/user/viewprofilepage/user-id/731209">@GID</a>');

// 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 = <a href="https://community.esri.com/t5/user/viewprofilepage/user-id/186967">@FW</a>');
}
if (!IsEmpty(wM)) {
    a.push('wM = @wM');
}
if (!IsEmpty(ci)) {
    a.push('ci = <a href="https://community.esri.com/t5/user/viewprofilepage/user-id/465295">@CI</a>');
}
if (!IsEmpty(eff)) {
    a.push('ec = @eff');
}
if (!IsEmpty(FT)) {
    a.push('FT = <a href="https://community.esri.com/t5/user/viewprofilepage/user-id/73075">@ft</a>');
}
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

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/