Sign in to follow this  
Followers 0

Is there a calculation for "contains" a string of text?


5 posts in this topic

Posted

Hi,

I have a field in a db that has a value list attached to it and checkboxes. I need to make calculation fields that will look at the field and if it contains certain text, will put "T" in the calculation field.

Field Name: FEDHAZ

Value List Name: Hazard Category

Values:

a. Fire

b. Reactive

c. Pressure Release

d. Acute Health

e. Chronic Health

Since a chemical can have more than one of these values, and be checked in any order, there's quite a bit of variation as to what text the field can contain.

I need to make a field, Acute, that if the field contains the "d. Acute Health" text, it will fill the Acute field with the letter "T". I could say:

If (FEDHAZ = "d. Acute Health"; "T"; ""

However, as you know, that will only work if the only item checked is that one. How can I get the same result no matter which other ones have been checked? Something similar to:

IF (FEDHAZ contains "d. Acute Health"; "T" ""

What function could I add for "contains"?

Share this post


Link to post
Share on other sites

Posted

patterncount ( )

but consider adopting a child table of Hazards for each Chemical record, rather than a checkbox set.

1 person likes this

Share this post


Link to post
Share on other sites

Posted

What function could I add for "contains"?

FilterValues ( )

Try:

If ( ValueCount ( FilterValues ( FEDHAZ ; "d. Acute Health" ) ) ; "T" )

1 person likes this

Share this post


Link to post
Share on other sites

Posted

Thanks! That seemed to do the trick.

The child table is probably the better way to go (design-wise), but I need to be able to export all the data out in a single Excel file to upload to a state agency online system. I don't want to have to compile multiple exports into a single file. (If there's a way to do that, it's beyond my self/forum-taught FM knowledge).

Share this post


Link to post
Share on other sites

Posted

You would export from the context of the child table. However, I'd need to know more about the destination and what format it expects. Are you going to be OK with a return-sep list in one cell in Excel?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0