Jump to content
Sign in to follow this  

Combine Fields with separator ignore empty

Recommended Posts

I tried few calculation in Filemaker like substitute trim etc but did not get same result.

Google sheets has nice function JOINTEXT any help to replicate/convert the function in Filemaker calculation.



Share this post

Link to post
Share on other sites

You can achieve a similar result by using the List() function and substituting the default ¶ delimiter with your own:

Substitute ( List ( Field_A ; Field_B ; Field_C ) ; ¶ ; ";" )

This assumes the fields do not contain any carriage returns - otherwise there will be more work to do.

Note that empty fields will be ignored. If that's not what you want, then just do a straightforward concatenation:

Field_A & ";" & Field_B & ";" & Field_C


  • Thanks 1

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Similar Content

    • By Jim Gill
      This is probably one of those questions which separates me from people that make money as developers and those that don't (that would be me).  I have a simple script that compares two variables. The variables are populated with  text (Peoples Names).  Basically, if the the two variables are TRUE (the variables are equal) I want to stop the script.  Despite the two variables being equal - the IF continues.  I have noted when I use the same numbers to populate the variables - the script works as expected.  Please tell me Filemaker is smart enough to evaluate text in variables.   Below is the my script and attached is a screenshot of my debugging efforts.   
      As always - thank you to the people here which share your wisdom - a big reason I love Filemaker so much.  
      IWP: IWP: Bunk Choice Select
      #This script allows WebD users to select bunkmates for the coming summer.
      #List All Chosen Names
      Set Variable [ $BR_List; Value:List ( BRE__BUNKSREQUEST_tog::Bunk Request Choice 1;BRE__BUNKSREQUEST_tog::Bunk Request
      Choice 2;BRE__BUNKSREQUEST_tog::Bunk Request Choice 3;BRE__BUNKSREQUEST_tog::Bunk Request Choice 4 ) ]
      Set Variable [ $BunkRequest; Value:Agegroup_Names_List::Name_Full ]
      Set Variable [ $BR_Check; Value:FilterValues ( $BR_List ; $BunkRequest ) ]
      #IF check to see if the names has already been selected
      If [ $BunkRequest = $BR_Check ]
      Show Custom Dialog [ Title: "No Duplicates"; Message: "You have already chosen this person. Please give us FOUR differnt names!
      Thanks!"; Default Button: “OK”, Commit: “Yes” ]
      Exit Script [ ]
      End If

      If [ BRE__BUNKSREQUEST_tog::Bunk Request Choice 1 = "" ]
      Set Field [ BRE__BUNKSREQUEST_tog::Bunk Request Choice 1; $BunkRequest ]
      Else If [ BRE__BUNKSREQUEST_tog::Bunk Request Choice 2 = "" ]
      Set Field [ BRE__BUNKSREQUEST_tog::Bunk Request Choice 2; $BunkRequest ]
      Else If [ BRE__BUNKSREQUEST_tog::Bunk Request Choice 3 = "" ]
      Set Field [ BRE__BUNKSREQUEST_tog::Bunk Request Choice 3; $BunkRequest ]
      Else If [ BRE__BUNKSREQUEST_tog::Bunk Request Choice 4 = "" ]
      Set Field [ BRE__BUNKSREQUEST_tog::Bunk Request Choice 4; $BunkRequest ]
      End If
      Commit Records/Requests
      [ Skip data entry validation; No dialog ]

    • By troi
      Text manipulation plug-in adds new script steps and online help for FileMaker Pro 16.

      Alphen aan den Rijn, the Netherlands, November 13, 2017–Troi today announced the immediate availability of Troi Text Plug-in 4.5 for FileMaker Pro 16.

      What is Troi Text Plug-in?

      Troi Text Plug-in is a very powerful tool for dealing efficiently with text in your FileMaker Pro database. All from within FileMaker you can:

      - Customize spell checking*
      - Parse XML text into fields
      - Get (unique) lines and sort words and lines
      - Combine sets from 2 text fields:
          - get all lines that are the same
          - get all lines that differ
          - and other combinations
      * currently not available on FileMaker Cloud

      What is new in version 4.5?

      We rewrote Troi Text Plug-in for FileMaker 16 and it now has the following new functionality:
      - Tested and made compatible with FileMaker Pro 16.
      - Tested and improved compatibility with Windows 10 (Creators Update) and macOS 10.13 (High Sierra).
      - Tested and made compatible to run under FileMaker Cloud 16.
      - Rewrote and modernized the code in several places.
      - Made various minor changes to the example files to better work with FileMaker Pro 16.
      - Updated the User Guide and the Reference.fmp12 file.

      Extra features when running FileMaker Pro 16

      Version 4.5 adds the following new features when running in FileMaker 16:
      - The Text Plug-in adds script steps when running FileMaker Pro 16. You can select these script steps in the script steps pane in Script Workspace, or you can begin typing the script step name, then choose the script step from the list that appears. The script steps expand the possible actions you can perform with FileMaker Pro. The following script steps are added:
          - Learn Spelling
          - Unlearn Spelling
          - Set Spelling Ignore List
      - Added new scripts to the Spelling.fmp12 example file, to demonstrate how to use the new external script steps of FileMaker Pro 16. The examples check if FileMaker Pro 16 is running and will then select these new scripts.
      - Added support for online help for the functions and script steps of the plug-in. You can now easily get online help from the Troi website, by clicking the Help button (the small question mark button) next to the function description in the functions pane or the script step description in the script steps pane.

      More detailed information on all changes and improvements implemented in Troi Text Plug-in 4.5 can be found at https://www.troi.com/products/textplugin/ 
      More information on Troi plug-ins for FileMaker Cloud can be found at: https://www.troi.com/compatibility/filemaker_cloud/

      Pricing & availability

      A fully functional demo version of Troi Text Plug-in 4.5 is available for downloading at:


      Licenses cost US$ 49 per user. Details on developer licenses and multi-user discounts can be found on our web site. You can order licenses from our web site.

      Upgrade information

      Version 4.5 needs a new registration. The upgrade is free for users who bought a license for Troi Text Plug-in on or after October 27, 2016. Eligible users will be sent a new registration. Contact us if you have not received yours after November 17, 2017.
      Upgrades from licenses bought before October 27, 2016 are available from US$ 29 per user. Upgrade prices for other licenses can be found on our web site. You can order upgrades from our web site.
    • By Hoytopher
      Hi all,
      I'm an FM novice who has a functioning database, and I'm trying to add layers of functionality to it. One feature I'm trying to design is a script to search through a Gmail outbox with certain keywords.
      In URLs, "%" translates to "%25". Filemaker appears to do this automatically - In Gmail syntax, you need to write "in%3Asent" to have the Gmail search bar contain "in:sent" which searches my outbox. Here's my current code for Go to URL:
      This "%" bit translates to "%25". I've tried "\%", I've tried creating a string, x, then having it open x, but no matter what I cannot get "%" to show up as-is, it always translates to "%25". Is there any way around this? I've tried subtracting text, but I just can't seem to get it to work. Any bright ideas? I can't find an answer to this specific question of disabling URL string substitution.
    • By Asu
      Hello FM Mavens, 
      I would like to be able to change an attribute (happens to be the size) of a selection in a text window with a script. (Basically, as if moving the size selector from the Formatting Bar into a button on a layout).
      I created a script that cuts the selection, puts it in another text field, makes the change, cuts it out and goes back to the original field but by then the insertion point is gone. I can manually paste it back of course.  I wonder if there is a way to remember the insertion point somehow and make it a so the paste step uses it. 
    • By Mike_k
      OK I admit it ... I'm stumped. I'm not a FileMaker professional, merely an enthusiastic tinkerer.
      I'm trying to create a feedback system where a series of standard questions are checked. Based on this, those questions answered 'yes' are combined together and those answered 'no 'are ignored. I have created 2 fields per criteria, 'Preset text' and 'preset question'. 
      So ...Preset message = 
      (if preset question 1 = yes, include ‘preset text 1’ else, blank){cr}
      (if preset question 2 = yes, include ‘preset text 2’  else, blank){cr}
      (if preset question 10 = yes, include ‘preset text 10’ else, blank){cr}
      I've managed to get the display of answers based on yes/no, but I can't get rid of the blank lines.
      I'm am probably going about this the wrong way, so any suggestions?
  • Create New...

Important Information

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