topic Arcade syntax in Developers Questions

Arcade syntax

KurtRadamaker1 — Thu, 21 Sep 2023 14:15:00 GMT

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!

Re: Arcade syntax

KenBuja — Thu, 21 Sep 2023 16:20:23 GMT

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

Re: Arcade syntax

KurtRadamaker1 — Thu, 21 Sep 2023 16:02:04 GMT

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.

Re: Arcade syntax

KenBuja — Thu, 21 Sep 2023 16:51:59 GMT

Try this

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

Re: Arcade syntax

KurtRadamaker1 — Thu, 21 Sep 2023 18:25:41 GMT

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!

Re: Arcade syntax

JohannesLindner — Thu, 21 Sep 2023 18:36:14 GMT

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