Jumat, 12 Desember 2008

SAP Business One substring formula for leading zero number print out

this is a right$ (in vb) compare to sap business one report/print out template formula.
000012390 where 12390 is the numeric data from database table field.

to output the field in print out/report.
  1. put the number field to format from database into report and set the field invisible.
    for example the field name: field_99.
  2. put a new text field with the leading zero desired by concatenating the field_99.
    the formula:
    concat("000000000", field_99)
    set the field into invisible (let say this field named: field_100).
  3. put a new text field capture the number field length using this formula:
    set the field invisible, for example the field name: field_101.
  4. put a new text field to output the number field with leading zero with this formula:
    substring(field_100, field_101 - 9, field_101)

the number field should be concatenating with "0000000" then add a new field to capture the concatenated field length and substring the concatenating the field with desired leading zero
in the example will return 9 characters.

=========================================================================== This email is confidential.  If  you are not the  addressee tell the sender immediately  and destroy this  email without using,  sending or storing it. Emails are not secure  and may suffer  errors, viruses, delay, interception and amendment.  The Trakindo Group of Companies do not accept liability for damage   caused   by   this   email   and   may   monitor   email  traffic. Unless expressly stated, any opinions are the sender's and are not approved by  the  Trakindo  Group  of  Companies  and  this  email  is not an offer, solicitation,     recommendation     or    agreement    of    any     kind. ===========================================================================