Forum Discussion

CharlotteM's avatar
CharlotteM
Qrew Assistant Captain
7 years ago

How can I derive a name in the middle of text?

We have a block of text coming in from Quickbooks. (Description on invoice line item)

There is always a line in the description that says 

Consultant: First Last

Where First and Last are their first and last names.

There may be other things above it, and below it.

Is there any way to parse this block of text and derive "First Last" as the result?

I tried using both of these but the first one fails if there is more than one colon, and the second one fails if this line is not the third line.

Left(Trim(Right([Description],":")),"\n")

Trim(Right(Part([Description], 3, "\n"),":"))

Can this be done in native QuickBooks? 
Ask about Quickbase functionality
Formulas and functions
  • MattStephens's avatar
    MattStephens
    Qrew Cadet
    7 years ago
    How many colons could you possibly get in the invoice line description?
    If it's only a handful, you could check the text to the left of each colon to see if it is "Consultant", and if it is, use the text to the right. Something like this:

    //Get all the text parts between colons(:)
    var text texta = Part([Descsription],1,":");
    var text textb = Part([Descsription],2,":");
    var text textc = Part([Descsription],3,":");
    var text textd = Part([Descsription],3,":");

    //Find which part contains "Consultant", and then use the next part.
    var text NamePart = 
    Case("Consultant",
    Right($texta,10),$textb,
    Right($textb,10),$textc,
    Right($textc,10),$textd);

    //Get the first and last name
    var text FirstName = Part($NamePart,2," \n");
    var text LastName =  Part($NamePart,3," \n");

    //Create the display anme
    $FirstName & " " & $LastName
  • CharlotteM's avatar
    CharlotteM
    Qrew Assistant Captain
    7 years ago
    There could be a lot of colons in some cases.

    I'm wondering if there is any way to do this with a code fragment / code page.
    • RobIV's avatar
      RobIV
      Qrew Cadet
      7 years ago
      Absolutely,

      If this parsing is at all complex, and you have the stomach for javascript, I would create a code page that utilizes handy and powerful JS libraries like Lodash to parse the content and return what you need.

      Do you have any specific examples of the data you are looking at?  It would be helpful if you could share some sanitized examples.

      Thanks,
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
     Building on Matt's approach, you can use the new line character which is \n as a delimiter.

    //Get all the text parts between colons(:)
    var text texta = Trim(Part([Descsription],1,"\n"));
    var text textb = Trim(Part([Descsription],2,"\n"));
    var text textc = Trim(Part([Descsription],3,"\n"));
    var text textd = Trim(Part([Descsription],3,"n"));


    var text ConsultantLine = 
    IF(
    Begins($texta, "Consultant"), $texta,
    Begins($textb, "Consultant"), $textb,
    Begins($textc, "Consultant"), $textc,
    Begins($textd, "Consultant"), $texta)

    Trim(Right($ConsultantLine,":"))
  • CharlotteM's avatar
    CharlotteM
    Qrew Assistant Captain
    7 years ago
    Thanks everyone.  I was actually able to get the sending application configured to make the text much easier  for native QuickBase to parse.  I didn't think that was an option.