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

  • 0
  • 1
  • Question
  • Updated 7 months ago
  • Answered
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? 
Photo of Charlotte

Charlotte

  • 450 Points 250 badge 2x thumb

Posted 7 months ago

  • 0
  • 1
Photo of Matt NZ

Matt NZ

  • 296 Points 250 badge 2x thumb
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
Photo of Charlotte

Charlotte

  • 450 Points 250 badge 2x thumb
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.
(Edited)
Photo of Rob White IV

Rob White IV

  • 948 Points 500 badge 2x thumb
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,
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
 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,":"))
Photo of Charlotte

Charlotte

  • 450 Points 250 badge 2x thumb
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.