Select to view content in your preferred language

Arcade syntax

678
5
09-21-2023 07:15 AM
KurtRadamaker1
Occasional Contributor

In an arcade attribute expression I'm counting all of the rows in a layer using the filter. The below code is working.

This Arcade code correctly returns a count of all species in the layer matching the value of the species point clicked for the popup.

//accepted records - working correctly
Count(
Filter($layer, "Species = '" + replace($feature.species, "'","''") + "'")
);

I would also like to filter if row batch number is not null. I can't seem to get the syntax right. I tried

//accepted records - failing
Count(
Filter($layer, "Species = '" + replace($feature.species, "'","''") + "'") && (batch_number != Null)
);

 

I have tried lots of different versions using tick marks and $feature, but no luck.

please help!

 

 

Tags (1)
0 Kudos
5 Replies
KenBuja
MVP Esteemed Contributor

I think this is what it should be (based on my shaky SQL92 knowledge)

 

Count(
  Filter($layer, "Species = '" + replace($feature.species, "'","''") + "' AND $feature.batch_number IS NOT Null")
);

 

 

You could use a template literal to embed expressions without excessive quotes to make it easier to understand.

 

Count(
  Filter($layer, `Species = '${replace($feature.species, "'","''")}' AND ${$feature.batch_number} IS NOT Null`)
);

 

 

0 Kudos
KurtRadamaker1
Occasional Contributor

I tried the above examples and I get syntax errors

Expected "!", "'", "(", "+", "-", "@", "CASE", "CAST", "DATE", "EXTRACT", "FALSE", "INTERVAL", "N'", "NOT", "NULL", "POSITION", "SUBSTRING", "TIMESTAMP", "TRIM", "TRUE", "`", [ \t\n\r], [0-9], or [A-Za-z_\x80-￿] but "$" found.

0 Kudos
KenBuja
MVP Esteemed Contributor

Try this

Count(
  Filter($layer, `Species = '${replace($feature.species, "'","''")}' AND batch_number IS NOT Null`)
);

 

 

 

0 Kudos
KurtRadamaker1
Occasional Contributor

How I solved it

//accepted records

var sp = "Species = '" + replace($feature.species, "'","''") + "'"
var bn = "batch_number is NOT Null"
Count(
Filter($layer, sp + ' AND ' + bn)
);

 

No clue why the other syntax doen't work!

0 Kudos
JohannesLindner
MVP Frequent Contributor

&& and != are logical operators used in many programming languages. SQL uses more natural operators, in this case AND and IS NOT NULL, as Ken pointed out.

Instead of writing the query yourself, which quickly gets unwieldy and hard to read with all those plusses and apostrophes, you can use the @ notation of the Filter function:

var species = Replace($feature.species, "'", "''")
var sql = "Species = @species AND batch_number IS NOT NULL"
return Count(Filter($layer, sql))

 


Have a great day!
Johannes
0 Kudos