Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn more

View all

Sign in to view all badges

SOLVED

"Contains" syntax for calculated field on schema (expr=)

davidh2892249
Level 5
Level 5

Hi There,

I'm looking to create a SQL calculated field on a custom schema, based on another field on the same schema.

The expression I'm looking to use is if field x contains a string, return field y (the calculated field) as true.

Example code from schema XML:

<attribute label="Field A" length="50" name="fieldA" type="string"/>

<attribute label="Field B" name="fieldB" expr="IF @fieldA contains 'Example' return 1, else 0" type="boolean"/>

Can anyone help with the Red bit?

I'm sure it needs an Iif function, I simply don't know how to write the condition if @fieldA contains 'example'

Thanks

David

1 Accepted Solution
menno_74
Correct answer by
Level 2
Level 2

Hi David,

Usually I try this in edit mode first, by adding an expression in the data panel of the schema (right click and choose 'configure list...', then add an expression; use: 'Edit the forumla by using an expression' ) once you set up a working expression there, simply copy it into the definition of the attribute (edite panel).

As an 'contains'  or ' like' parameter is not present, In your case you can try to find a (partial) text is in your search string, and if so it will return the location of the found (partial) text (Charindex). In your example this would probably work:

Iif( Charindex( Lower( @fieldA ), 'example' ) > 0, 1 , 0 )

Kind Regards,

Menno

View solution in original post

4 Replies
pablo_rosero1
Level 10
Level 10

Hi David,

Do the red part separately and then assign a variable instead.

I hope that helps!

All the best,

Pablo

davidh2892249
Level 5
Level 5

Where does the variable get declared?

What's the syntax.

Could you provide example code please.

Thanks

Dave

menno_74
Correct answer by
Level 2
Level 2

Hi David,

Usually I try this in edit mode first, by adding an expression in the data panel of the schema (right click and choose 'configure list...', then add an expression; use: 'Edit the forumla by using an expression' ) once you set up a working expression there, simply copy it into the definition of the attribute (edite panel).

As an 'contains'  or ' like' parameter is not present, In your case you can try to find a (partial) text is in your search string, and if so it will return the location of the found (partial) text (Charindex). In your example this would probably work:

Iif( Charindex( Lower( @fieldA ), 'example' ) > 0, 1 , 0 )

Kind Regards,

Menno

View solution in original post