Select order number from text field

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
I have a text field includes the subject for order confirmations. Some emails have the subject:

Order Confirmation (1234567)
Order Confirmation - 1234567

Is there a way to grab only the sales order number?
Photo of Meagan McLeod

Meagan McLeod

  • 130 Points 100 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Matt NZ

Matt NZ

  • 296 Points 250 badge 2x thumb

Are there other possible email subject formats?

Are your sales orders always 7 digits?

You could use something like this:

var text string = [Subject];

//If the end of the string is a bracket, take the number from within the brackets

//Otherwise, take whatever is right of the last space
Right($String," ")

I haven't tested it, but something like that should work.

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,348 Points 50k badge 2x thumb
I tested this and it works :)

var number A = ToNumber(Part([Subject],1," -(),"));
var number B = ToNumber(Part([Subject],2," -(,)"));
var number C = ToNumber(Part([Subject],3," -(,)"));
var number D = ToNumber(Part([Subject],4," -(,)"));
var number E = ToNumber(Part([Subject],5," -(,)"));
var number F = ToNumber(Part([Subject],6," -(,)"));

Max($A, $B, $C, $D, $E, $F)

I suggest continuing the pattern up to as many words you think are possible before the numbers.  So just go to say ABCDEFGHIJKLM

The characters in these quotes here " -()," need to be all the ones which might precede or directly follow the numbers.

Photo of Meagan McLeod

Meagan McLeod

  • 130 Points 100 badge 2x thumb
Thank you! That worked great.