Quick Base Discussions

Expand all | Collapse all

Select order number from text field

  • 1.  Select order number from text field

    Posted 04-19-2018 01:08
    I have a text field includes the subject for order confirmations. Some emails have the subject:

    Order Confirmation (1234567)
    or
    Order Confirmation - 1234567

    Is there a way to grab only the sales order number?


  • 2.  RE: Select order number from text field

     
    Posted 04-19-2018 05:18

    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
    If(Right($String,1)=")",NotRight(NotLeft($String,"("),")")

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

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



  • 3.  RE: Select order number from text field

    Posted 04-19-2018 12:17
    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.


    Mark


  • 4.  RE: Select order number from text field

    Posted 04-19-2018 15:43
    Thank you! That worked great.