Arcade syntax

Question

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!

JohannesLindner · Answer

&& 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))

KurtRadamaker1 · Answer

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!

kenbuja · Answer

Try this

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