Reproducing Labelling SQL Query as Arcade Label expression

MattSmithCarto

Trying to simplify the number of label classes . So I'm trying to set the expressions used in the When logical function by replicating the SQL Quaries previously used to distinguish between label classes.

 

But I'm struggling to replicate this one

FEATURETYPE  =   'Railway' And STATUS = 'Operational' And OWNER NOT IN ('Private', 'Aurizon') AND ROUTENAME NOT LIKE '%System' AND ROUTENAME NOT LIKE '%Yard' AND ROUTENAME NOT LIKE '%Siding'

I know wildcards are a bit tricky in Arcade which is where I'm getting stuck.

Any help would be appreciated.

jcarlson

Ah, I see. You want the label expression to dynamically pipe in different values.

IN basically equates to the Includes function. Checking for an "ends with" is a bit trickier without wildcards, but we can do it by grabbing the Right portion of a string equal to the length of the searched text, and see if it's equal to the searched text. To put that in code:

 

// a test string
var str = 'Here\'s a sample'

// this returns true
Right(str, 5) == 'ample'

// this returns false
Right(str, 5) == 'fishy'

 

We can actually build our own custom function for this. Let's call it EndsWith.

Okay, maybe it would look like this:

 

var bad_names = [
    'Private',
    'Aurizon'
]

// Custom function returns boolean if searchText is at the end of text
function EndsWith(searchText, text){
    
    // Get length of search text
    var strlen = Count(searchText)
    
    // Look at end of text for that string
    return(Right(text, strlen) == searchText)
}

var f = $feature['FEATURETYPE']
var s = $feature['STATUS']
var o = $feature['OWNER']
var r = $feature['ROUTENAME']

if( f == 'Railway' &&
    s == 'Operational' &&
    Includes(bad_names, o) &&
    !EndsWith('Yard', r) &&
    !EndsWith('Siding', r) &&
    !EndsWith('System', r)
    ) {
        return 'Some Category'
    }

 

 Now, I notice that all your conditions must be true, since these are all "AND" statements in the original SQL.

Depending on where you're working, and what version Arcade you have access to, you might try using the All function. Try this instead of lines 21 - 29 of the expression above.

 

var conditions = [
    f == 'Railway',
    s == 'Operational',
    Includes(bad_names, o),
    !EndsWith('Yard', r),
    !EndsWith('Siding', r),
    !EndsWith('System', r)
]
    
if( All(conditions, Boolean)){
    return 'Some Category'
}

 

That would allow you to establish all your conditions prior to the if/else section, which can help make your expression more readable.