Jump to content
Sign in to follow this  

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

Recommended Posts

Raschelle    0


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
bcooney    101

patterncount ( )

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

  • Like 1

Share this post

Link to post
Share on other sites
Raybaudi    58

What function could I add for "contains"?

FilterValues ( )


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

  • Like 1

Share this post

Link to post
Share on other sites
Raschelle    0

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
bcooney    101

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  

  • Similar Content

    • By crazybake
      There are three tables:
      Services:materialName::ServiceTransactionsLineItems:Material_FK and Services:MaterialYear::ServiceTransactionLineitems:TransactionLineItemYear
      (Services may change from year to year, and within one transaction you might line items for both current and future years.)

      :Services:MaterialBudgetAllocation is a user entered number.
      What I am hoping to do is: Get the yearly total amounts of each material (Material_FK) and subtract that from Services:MaterialBudgetAllocation to get a remaining amount.
    • By charmcello
      I am tasked with troubleshooting a web registration form that queries a Filemaker Pro v12 database via a Filemaker PHP API.
      The web form had been running well for a couple of months after major revision, when all of a sudden:
      All dropdowns are empty All radio buttons are missing These symptoms appear to be browser-independent. The appearance of these symptoms is mysterious:
      Nobody has modified the PHP code for the web form - I've done a compare with a backup done at the time when the web form was not broken and it is identical to what is currently on the server.  The Filemaker Pro layout that the web form is querying is just fine - all the value lists for the dropdowns and radio buttons are present. I've created a stripped-down version of the web form: https://campmeetings.cccsda.org/test_form.php and I've also attached the php source code file.
      The date and record fields are populating correctly. Notice the empty dropdown list after "Church I attend."
      Could this be caused by an incompatibility between the FMP PHP API and PHP libraries on the server? Perhaps due to a PHP update on the server? Any ideas how to troubleshoot this? 
    • By the Otter
      As most people here probably know, the Let ( ) function can be used to define a Local variable. As such, it is possible to build a custom function that defines such a variable, and it is further possible to set said variable to a value including itself. An example would be the following custom function, ErrorList, consisting of the following calculation:
      Let ( $ErrorList = List ( $ErrorList ; Get ( LastError ) ) ; "" )
      If a Set Variable script step sets the same variable as a custom function like the one above, e.g.
      Set Variable [ $ErrorList ; Value: ErrorList ]
      …the script step will run appropriately, so long as the contradictory variable—in this case, $ErrorList—is not yet defined. However, once this variable has been defined, executing the preceding script step will cause FileMaker 14 (and perhaps other versions) to suffer an Error #1213 and crash the application. The workaround for this behavior is to have the Set Variable script step set a dummy variable, e.g.
      Set Variable [ $x ; Value: ErrorList ]
      Even if $x is not referenced anywhere, having a script call the ErrorList function passes the variable $ErrorList to the script’s own context, thus allowing its value to be accessed by later steps in the same script (including subsequent calls to the ErrorList function itself).
      In FileMaker 15, this behavior has been changed: local variables defined within a custom function are now valid only within the scope of the function itself, including any recursions. While this alleviates the problem of application crashes, it also results in unexpected behavior when scripts written in earlier versions of FileMaker rely on custom functions to set local variables. When migrating to FileMaker 15, each affected script step must be updated to set the target variable explicitly instead of relying on the custom function to do the work. In other words, the code:
      Set Variable [ $ErrorList ; Value: ErrorList ]
      …which proved fatal in FileMaker 14, is now required grammar for FileMaker 15: FileMaker 15 believes that what happens in the function stays in the function, instead returning the result of the calculation to the variable defined in the Set Variable script step. The FileMaker 14 grammar,
      Set Variable [ $x ; Value: ErrorList ]
      …thus sets $x to the intended value of $ErrorList while leaving the value of $ErrorList as null.
      Unfortunately, this cannot work effectively in a mixed-installation environment: the FileMaker 14 grammar leaves FileMaker 15 clients with unintended null values; the FileMaker 15 grammar causes FileMaker 14 to crash. When upgrading all users to FileMaker 15 is not feasible, the best workaround is to use the FileMaker 14 grammar, then once all relevant script steps are complete, check the value of the intended variable (e.g. $ErrorList) and, if empty, set it to the value of the dummy variable (e.g. $x).
    • By Marinos Kangas
      I am new to FileMaker and still trying to find my way around the scripting language.  Even though the scripting language is quite rich, I don't feel as comfortable and flexible as I do in lets say VB.
      To my query now;  I have designed a solution for my company which processes invoices.  These invoice forms input numerical amounts tied on budget codes for each department and in turn are stored in a different table.  As you can see on the image attached, I cannot find a calculation function that will sum all the amounts for each BudgetCode into AmountAllocated.  AmountAssigned is the yearly department budget predefined.
      I will be grateful for any suggestions or ideas.

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

Important Information

By using this site, you agree to our Terms of Use.