Arcade syntax

[KurtRadamaker1]

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!

[KenBuja]

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`)
);

[KurtRadamaker1]

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.

[KenBuja]

Try this

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

[KurtRadamaker1]

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!

[JohannesLindner]

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