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

davidh2892249

23-11-2018

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

Accepted Solutions (1)

Accepted Solutions (1)

menno_74

30-11-2018

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