Jump to content

  •  

Photo

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

calculations functions value lists contains

  • Please log in to reply
4 replies to this topic

#1 Raschelle  enthusiast

Raschelle
  • Members
  • 41 posts
  • FM Application:12 Advance
  • Platform:Mac OS X Mavericks
  • Skill Level:Intermediate
  • Time Online: 38m 28s

Posted 19 January 2012 - 04:31 PM

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

Raschelle :runningcat:

"There is a world of endless resources.
There is a mind full of outrageous dreams.
There is a place where the two meet.
Anything is possible..." ~Debbie Gibson


#2 bcooney  consultant

bcooney
  • Moderators
  • 5,768 posts
  • LocationLong Island, NY
  • FM Application:13 Advance
  • FMGo:iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:9, 10, 11, 12
  • Membership:TechNet
  • Time Online: 23d 13h 14m 5s

Posted 19 January 2012 - 05:17 PM

patterncount ( )

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

#3 Raybaudi  ChindoguMaker

Raybaudi
  • Members
  • 3,234 posts
  • LocationRome, Italy
  • FM Application:12 Advance
  • Platform:Windows XP
  • Skill Level:Intermediate
  • Time Online: 48d 17h 16m 18s

Posted 20 January 2012 - 10:39 AM

What function could I add for "contains"?



FilterValues ( )

Try:

If ( ValueCount ( FilterValues ( FEDHAZ ; "d. Acute Health" ) ) ; "T" )
  • 1
Daniele
--------------------------------
I'm sorry for my English

#4 Raschelle  enthusiast

Raschelle
  • Members
  • 41 posts
  • FM Application:12 Advance
  • Platform:Mac OS X Mavericks
  • Skill Level:Intermediate
  • Time Online: 38m 28s

Posted 08 February 2012 - 01:37 PM

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

Raschelle :runningcat:

"There is a world of endless resources.
There is a mind full of outrageous dreams.
There is a place where the two meet.
Anything is possible..." ~Debbie Gibson


#5 bcooney  consultant

bcooney
  • Moderators
  • 5,768 posts
  • LocationLong Island, NY
  • FM Application:13 Advance
  • FMGo:iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:9, 10, 11, 12
  • Membership:TechNet
  • Time Online: 23d 13h 14m 5s

Posted 09 February 2012 - 05:19 AM

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?
  • 0





FMForum Advertisers