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

Avatar

Avatar
Validate 10
Level 3
davidh2892249
Level 3

Likes

16 likes

Total Posts

131 posts

Correct reply

2 solutions
Top badges earned
Validate 10
Validate 1
Ignite 5
Ignite 3
Ignite 1
View profile

Avatar
Validate 10
Level 3
davidh2892249
Level 3

Likes

16 likes

Total Posts

131 posts

Correct reply

2 solutions
Top badges earned
Validate 10
Validate 1
Ignite 5
Ignite 3
Ignite 1
View profile
davidh2892249
Level 3

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)

Avatar

Avatar
Validate 1
Level 2
menno_74
Level 2

Likes

7 likes

Total Posts

18 posts

Correct reply

5 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 1
Affirm 5
View profile

Avatar
Validate 1
Level 2
menno_74
Level 2

Likes

7 likes

Total Posts

18 posts

Correct reply

5 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 1
Affirm 5
View profile
menno_74
Level 2

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

Answers (3)

Answers (3)

Avatar

Avatar
Validate 10
Level 3
davidh2892249
Level 3

Likes

16 likes

Total Posts

131 posts

Correct reply

2 solutions
Top badges earned
Validate 10
Validate 1
Ignite 5
Ignite 3
Ignite 1
View profile

Avatar
Validate 10
Level 3
davidh2892249
Level 3

Likes

16 likes

Total Posts

131 posts

Correct reply

2 solutions
Top badges earned
Validate 10
Validate 1
Ignite 5
Ignite 3
Ignite 1
View profile
davidh2892249
Level 3

29-11-2018

Where does the variable get declared?

What's the syntax.

Could you provide example code please.

Thanks

Dave

Avatar

Avatar
Validate 250
Level 10
pablo_rosero1
Level 10

Likes

81 likes

Total Posts

348 posts

Correct reply

77 solutions
Top badges earned
Validate 250
Validate 100
Validate 50
Validate 25
Validate 10
View profile

Avatar
Validate 250
Level 10
pablo_rosero1
Level 10

Likes

81 likes

Total Posts

348 posts

Correct reply

77 solutions
Top badges earned
Validate 250
Validate 100
Validate 50
Validate 25
Validate 10
View profile
pablo_rosero1
Level 10

28-11-2018

Hi David,

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

I hope that helps!

All the best,

Pablo

Avatar

Avatar
Validate 10
Level 3
davidh2892249
Level 3

Likes

16 likes

Total Posts

131 posts

Correct reply

2 solutions
Top badges earned
Validate 10
Validate 1
Ignite 5
Ignite 3
Ignite 1
View profile

Avatar
Validate 10
Level 3
davidh2892249
Level 3

Likes

16 likes

Total Posts

131 posts

Correct reply

2 solutions
Top badges earned
Validate 10
Validate 1
Ignite 5
Ignite 3
Ignite 1
View profile
davidh2892249
Level 3

23-11-2018

Adobe Campaign Classic by the way