2 Replies Latest reply on Feb 19, 2012 9:34 PM by maximums

    extracting variable text from text field

    maximums

      Hi all

       

      I need to turn the following 2 fields into result below:

       

      Field 1: Microsoft Word 2010 Indroduction

      Field 2: Microsoft Word Introduction

      Desired Result: to extract 2010

       

      The syntax in Field 1 is always constant, ie. product & version & level or product & version but the number of words or characters is variable.

      The version can be text and digits and there are infinity possibilities

       

      I've been messing around with various calculations but all I have achieved is a headache!

       

      I hope somebody can clear the confused state I've put myself in

       

      cheers

      Linda

        • 1. Re: extracting variable text from text field
          DavidJondreau

          Bruce Robertson has a helpful custom function AntiFilterValues().

           

          This calc might get you part of the way (there will be issues if field 2 has words that are also in the version, or if there's pilcrows in either field).

           

          Let([

          f1 = table::field1 ;

          f2 = table::field2 ;

          f1a = Substitute ( f1 ; " " ; ¶ ) ;

          f2a = Substitute ( f2 ; " " ; ¶ ) ;

           

          vals = AntiFilterValues ( f1a ; f2a ) ;

          result = Substitute ( vals ; " " ; ¶ ) ;

           

          ];

          result

          )

          • 2. Re: extracting variable text from text field
            maximums

            That's perfect thanks David (and thanks of course to Bruce for his cusom function)

             

            I only needed to tweak the source fields using validation to ensure syntax control and convention and it should all be transparent now. The problem originated because their FileMaker database stored a course that can run multiple versions and it is upon enrolment of a participant that the version is selected whereas the web developer has broken a course that runs multiple versions into separate courses.

             

            I'm a happy little vegemite as is my client.

            Thanks again

            Linda