Forum Discussion

PankajSoni's avatar
PankajSoni
Contributing Partner
2 years ago

How to add supplier bank account no in same format based on the supplier master--> Payment instruction

Hi,

 

I’ve added supplier bank details in the supplier master GI, but bank account number is not displaying in the same format as per the supplier master payment instruction. so how we can solve this.

I would really appreciate, if someone can help me on this. 

 

Attached vendor master GI for reference.

 

 

 

Thanks,

Pankaj

  • Will_H's avatar
    Will_H
    MYOB Moderator

    Hi PankajSoni ,

     

    I don't know how to do this properly, but the below formula achieves it:

    =IIF(LEN([Bankaccountnumber.DetailValue])=16,Concat(LEFT([Bankaccountnumber.DetailValue],2),'-',SUBSTRING([Bankaccountnumber.DetailValue],3,4),'-',SUBSTRING([Bankaccountnumber.DetailValue],7,7),'-',RIGHT([Bankaccountnumber.DetailValue],3)),[Bankaccountnumber.DetailValue])

     

    Notes on the above:

    IIF checks the Length of the bank account number, if length is exactly 16 characters (NZ Bank account number true format) then format it.

    CONCATENATE (combine strings)

    LEFT (first 4 characters), SUBSTRING to grab each of the other 2 segments(4, then 7), RIGHT (last 3 characters)

    Finally if the length _was not_ exactly 16 characters, display whatever was in the field.

     

    Edited:
    Edited to correct error with substring start positions, thanks to PankajSoni  for providing the correction.

    • PankajSoni's avatar
      PankajSoni
      Contributing Partner

      Hi Will_H ,

       

      Thanks a lot.

      I really appreciate your help. now it's been solved and working pefectly. 

       

      Have a nice day :-)

      Pankaj Soni 

      • Leneth_A's avatar
        Leneth_A
        Former Staff

        Hi PankajSoni 

         

        We're glad to hear it's been resolved. We're always happy to help you again for future posts,

         

         

        Cheers,
        Leneth

  • Hi Pankaj,

     

    Thank you for your post. As you are listed as an MYOB Advanced Partner, please post this on the Partner Community Forum.

     

    I will send you a private message with an email address if you have not already been registered into this private forum.