2 Replies Latest reply on May 6, 2012 7:02 PM by melbury

    I am trying to use a calculation to find a record that contains defined text

    melbury

      Hi, I'm sure this should be easy but I just can't work how to do it and I'm at the end of my tether!

       

      I have 2 related tables

       

      the first contains a text field called candidate ID and has contents such as "WKT-021-008" .I have another field in this table I want to populate with a result if I can find (lookup) the candidate ID from this record in another table

       

      My other table has many fields including one 'LocationName' that 'may' contain the text I'm looking for; and this field/records need to be searched - Note location of my 'search text' within the text string is not consistent. e.g may be found in "WAIRAKEI STORAGE (WKT-021-008-A)" for example or variants of this.

       

      My preference would be to return the 'location ID' field from my second table where a location name that contains my Candidate ID and enter this in my first table to show that this location already exists; if not the field will show text "not found"

       

      Can anyone point me in the right direction? Hope this all makes sense.

       

      Thanks in advance

        • 1. Re: I am trying to use a calculation to find a record that contains defined text
          cortical

          <<

          where a location name that contains my Candidate ID

          >>

           

          A relationship left to right matches against literal string values

          so A to A, B to B, AB to AB

           

          A to AB is not a match

           

          you essentially have multiple values within the right side field

          try substituting the spaces with pilcrows, to make the right side field a multiline ; the brackets also need to be removed

          eg Substitute( location_id ;  [" " ; "<pilcrow character>" ] ;  ["(" ; "" ]  ;  [")" ; "" ]  ;  )

           

          // pilcrow is a carriage return

           

          this will return WAIRAKEI STORAGE (WKT-021-008-A) as:

          WAIRAKEI STORAGE

          WKT-021-008-A

           

          which can then be used as a right side multi-line key

          the right side field always needs to be indexed  (except for cartesians)

          1 of 1 people found this helpful
          • 2. Re: I am trying to use a calculation to find a record that contains defined text
            melbury

            Thanks for this.

             

            I tried to use the substitute, however had too many variables and unknowns still.

             

            I have crudely solved my problem with a script that takes each location then performs a find and if there is a reuslt it copies the location ID back to my original table.

             

            It's a bit rough however the power of 'find' has solved the tricky part for me - I think....

             

            Thanks for your reply - I'm sure I'll use substitute somewhere, now I know how it works; it will be useful in tidying some data.

             

            Mike