"Contains" syntax for calculated field on schema (expr=) | Community
Skip to main content
davidh2892249
davidh2892249
Level 5
November 23, 2018
Solved

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

  • November 23, 2018
  • 4 replies
  • 7706 views

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

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by menno_74

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

4 replies

davidh2892249
davidh2892249Author
Level 5
November 23, 2018

Adobe Campaign Classic by the way

pablo_rosero1
pablo_rosero1
Level 9
November 29, 2018

Hi David,

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

I hope that helps!

All the best,

Pablo

davidh2892249
davidh2892249Author
Level 5
November 29, 2018

Where does the variable get declared?

What's the syntax.

Could you provide example code please.

Thanks

Dave

menno_74
menno_74Accepted solution
Level 2
November 30, 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

    Terms & ConditionsAccessibility statement

    Loading footer...