Forum Discussion

HarrisonRay's avatar
HarrisonRay
Qrew Member
2 years ago

Report Link - Matching Text to Multi-Select Text

Hello!

I'm having some trouble with a report link that's matching a text field from "this table" to a multi-select text field in another table.


The problem is that if I select the box to "only include values if they match exactly in both fields," the report link won't catch records from the other table that have multiple selections. But, because some of the values in "this table" have common words/phrases, if I deselect the box, then the report link will grab records from the other table that include multi-select options that simply contain the value from "this table."

eg -- the values in "this table" are "test" and "testing". If I deselect the box to match exactly, the "testing" records will get grabbed by the "test" report link. 

Of course, I'd rather do this with record ID #s, but this is a fairly old app with hundreds of thousands of records. The relationship wasn't created correctly and the number of records is usually too big for queries..

Thanks in advance!
-Harrison



------------------------------
Harrison Ray
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    2 years ago

    not tested but maybe this will work.  On the table with the target multi select field, make a new field called [Multi Select (text format)].  The formula will be 
    ToText([My Multi Select Field]) & " ;"

    note that I added a space semi at the end.

    Then create a field on the "Parent" record "This record" with the formula 

    [my text field looking for a match] & " ;"

    see if we get proper non exact matches using those two new fields.

    The point is that you are no longer matching on "test" but are now matching on "test ;" which is different from "testing ;"



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------

    • HarrisonRay's avatar
      HarrisonRay
      Qrew Member
      2 years ago

      Genius! 

      Thanks, as always, Mark!



      ------------------------------
      Harrison Ray
      ------------------------------
      • HarrisonRay's avatar
        HarrisonRay
        Qrew Member
        2 years ago

        One more update in case anyone needs to do this in the future --

        use Mark's formula but include a special character at the beginning of your strings as well, in case you have values like "testing", "test", and "ting"



        ------------------------------
        Harrison Ray
        ------------------------------
    • MikeTamoush's avatar
      MikeTamoush
      Qrew Commander
      2 years ago

      I like Marks solution, but I think maybe you will need to use Search and Replace. Mark, you can let me know if I am wrong but if he simply adds a ; to the end of his multi select field, it could be like 'choice a; testing; test; choice d;  ;'

      ie just one appended semicolon. Im thinking:
      SearchandReplace(ToText[Original Multi Select], ";", "dummytext; ")

      Then in the original table, the field would be: [My text field looking for a match] & "dummytext".

      So now on page one it matches, Testdummytext, with

      ChoiceAdummytext; testdummytext; testingdummytext; choiceDdummytext



      ------------------------------
      Mike Tamoush
      ------------------------------