4 replies to this topic

[Raschelle]

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"?

[bcooney]

patterncount ( )

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

[Raybaudi]

What function could I add for "contains"?

FilterValues ( )

Try:

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

[Raschelle]

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).

[bcooney]

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?