Sign in to follow this  
Followers 0

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

5 posts in this topic


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


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

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

What function could I add for "contains"?

FilterValues ( )


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

1 person likes this

Share this post

Link to post
Share on other sites

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

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

  • Similar Content

    • By naio
      I need to update a solution designed for FM9 to FM15, the whole solution has 68 value lists (VL) all of them based on tables but only a few of these are conditional VLs. All of the tables have a primary key and a value field. The solution is designed according to the separation model.
      In the user file layout, each of VL data fields is duplicated so one field stores the value id (as dropdown list) and the other -just on top- shows the related value. This design requires a lot of table occurrences just to show related values which makes the graph and layout design more complex and tedious.
      As most of the lists are static (their component values will never be changed) I wonder if I should transform these lists into the 'Custom value' kind. Given that the design is quite robust like this, it seems unnecessary to change it but the gains on design simplification and clarity are very tempting.
      I would like to know the best practice using VLs. I would also appreciate your criteria about the use of dropdown vs the use of popup menu in the layouts.
    • By Nestor
      Hi,  I'm importing records and have a date field that i need to use to set my Fiscal year, month, week based on the fiscal year starting February 1st and ending January 31st.
      Any ideas or sample i might be able to take a look.

      Thanks in advance.
    • By fm8443
      I have a Layout in Table Mode as (partially) shown in the attachment.
      (Note:  First row will have a Country Code in it - not shown in attachment)
      I want to "fill-down" the last country code in all rows (in the Country Field) UNTIL the country code changes.  Then I want to similarly do it for that one.  Until I reach the end of rows.
      My database will grow from 500 to 10,000 rows of text/number data.  I am uploading in batches from Excel.
      Is this possible?  I've tried various calculations and scripting without much success.  Thanks!
      I'm trying to get something like this:

    • By beckham
      Hi can anyone point me in the right direction, I have a database that as a Field populated by a value list, i.e.,: Christmas,Easter,Mothers Day , etc. I want to chart how many records exist of each chosen category. I thought it would be from a summary field using Total of and count but that option isn't available, How do I get filemaker to count the number of uses of the word Christmas from that field , can anyone help please?
      Thanks in advance
    • By HOnza
      24U released 24U Toolbox Plug-In 2.0
      The 24U's ''swiss army knife'' for FileMaker developers is now rock stable and fully compatible with FileMaker Pro 14
      Prague, CZ -- November 4, 2015
      24U Software announces the version 2.0 of 24U Toolbox Plug-In, toolbox of the most useful calculation functions for daily use. The new version have been rewritten to be fully compatible with FileMaker Pro 14 and stable on FileMaker Server even when being used from several simultaneous threads.

      Version 2.0 brings:
       - Full 64-bit support for FileMaker Pro
       - Improved stability on server
       - Better registration interface

      24U Toolbox Plug-In 2.0's 64-bit support was focused and tested not only on FileMaker Server, but also on FileMaker Pro 14 and FileMaker Pro Advanced 14. Now is fully compatible in both 32-bit and 64-bit modes.
      The latest version of the plug-in works reliably with FileMaker Server when the same plug-in is accessed from multiple threads and sessions simultaneously and provides more ways to register the plug-in, and to find out whether the plug-in is registered even without user's interaction.

      About 24U Toolbox Plug-In
      24U Toolbox Plug-In is a robust FileMaker Pro plug-in which allows FileMaker Pro databases to:
      - Execute FileMaker or System shell scripts to automate tasks inside/outside of FileMaker Pro
      - Execute SQL commands to develop faster and keep your solution clean
      - Match & replace regular expressions to find & replace text fast using patterns
      - Merge data values into text while maintaining styling to quickly and easily apply templates
      - Lookup DNS name for IP & vice versa to find out who is connecting from where
      - Get current public IP to know more about clients and discover potential security attacks
      - Define global keyboard shortcuts to avoid unnecessarily excessive use of script triggers
      - Obtain precise microsecond timestamps to measure nearly unmeasurable chunks of time
      - Get the type of a FileMaker variable to let your scripts make the right decisions
      - Share variables between databases to avoid creating too many external data sources

      Important update for 24U FM Bench users
      24U Toolbox Plug-In is an essential element of the 24U FM Bench optimization tool. Upgrading to version 2.0, thanks to its improved stability, can be beneficial to all FM Bench users who use the tool to benchmark, troubleshoot, and optimize server-side scripts and web-enabled solutions.

      Use 24U Toolbox Plug-In to add a completely new dimension to your server-side data processing

      Server-side scripts can be efficiently used with FileMaker Server for nightly batch data imports and processing. The powerful features of 24U Toolbox Plug-In let you make this processing much faster and more powerful by being able to use system-level scripts, access your data with SQL queries, merging fields and variables into text, etc.
      HOnza Koudelka, Software Division Manager of 24U, says: ''24U Toolbox Plug-In is designed to help FileMaker developers in their daily work by providing a solid toolbox of the most commonly needed simple but powerful functions. This Plug-In makes a lot of functions easier and it is also a key component of 24U FM Bench, our popular tool for optimizing FileMaker solutions.''

      System Requirements and Compatibility
      The plug-in is fully compatible with FileMaker Pro 11, 12, 13 and 14, and with FileMaker Server 12, 13 and 14.
      Recommended configuration:
      - Mac OS X: Any Intel Mac, 4 GB RAM, Mac OS X 10.10 Yosemite, FileMaker Pro 14 Advanced and FileMaker Server 13 or newer
      - Windows: 1.6 GHz processor, 4 GB RAM, Windows 8.1 or newer, FileMaker Pro 14 Advanced and FileMaker Server 13 or newer
      Minimum requirements:
      - Mac OS X: Macintosh, 2 GB RAM, Mac OS X 10.7 Lion, FileMaker Pro 12 and FileMaker Server 12 or newer
      - Windows: 1 GHz processor, 1 GB RAM, Windows Vista, FileMaker Pro 12 and FileMaker Server 12 or newer
      Availability and Pricing
      24U Toolbox Plug-In is available as a shareware. User can install 24U Toolbox Plug-In, and try it out for the period of 14 days free of charge. After this period, 24U Toolbox Plug-In can stop working and require a registration code. User will get this code after he pays for a license.
      Licenses for 24U Toolbox Plug-In start at US$49 per user and US$499 per server.
      Existing customers can upgrade to the new version at 50 % off the regular prices if placing order within 14 days upon release, and 30 % off if buying later.
      Customers who have bought 24U Toolbox Plug-In within the last 30 days before the release of the new version are eligible for free upgrade.
      Optional Premium Support and Implementation services are available with the purchase of 24U Toolbox Plug-In 2.0.

      More info:

      About 24U Software
      24U is a key vendor of plug-ins for FileMaker Pro and a producer of FM Bench, the tool for auditing and optimizing FileMaker solutions. We unveiled the Marvelous Optimization Formula and teach how to efficiently optimize performance of your solution and processes by focusing on real bottlenecks. Optimizing your performance... Inventing unique iOS applications... Helping organizations to get more out of their technologies... Dedicated to creating the easiest to use FileMaker plug-ins... We connect the dare of education with the experience of industry to achieve the impossible.

      Customer contact:
      HOnza Koudelka
      Software Division Manager, 24U s.r.o.
      Press Contact:
      (c)2015 24U s.r.o. All rights reserved. 24U, 24U Software, and FM Bench are trademarks of 24U s.r.o. FileMaker is a trademark of FileMaker, Inc., registered in the U.S. and other countries. All other trademarks are the property of their respective owners. Pricing and availability are subject to change without notice.