x Want to participate? Join the group to interact
Options
  • State Not Answered
  • Replies 3 replies
  • Subscribers 107 subscribers
  • Views 1749 views
  • Users 0 members are here
Browse Forums
Announcements
293
topics
370
replies
Business Object Interface
1k
topics
7k
replies
Core Financial Modules
2k
topics
8k
replies
Customization and Productivity
3k
topics
12k
replies
Distribution, Manufacturing, and Applications
2k
topics
7k
replies
General Discussion
1k
topics
3k
replies
Partner Cloud
45
topics
91
replies
Payroll, Time Tracking, and Job Cost
777
topics
1k
replies
Reporting and Analytics
962
topics
3k
replies
Sage CRM for Sage 100
164
topics
357
replies
Technical and Installation
2k
topics
9k
replies
Year-end Forum
89
topics
237
replies
stircrazy08

UDF field Missing Leading zeroes in Crystal

Posted By

we are on MAS100 5.1  using crystal reports 2011 for Sage.

I have created a UDF field in Sales Order Detail called UDF_Customer_Item_No , i made this 15 char long as a String, control type multi line.

we are using visual integrator to import orders into MAS100, it populates the field as 0009528541330 as it should with leading zeroes, 

i used excel(ODBC) to view the table data and i see that the field looks correct.

i have created a crystal report that i am using the sales order header and detail, but when i put the field UDF_Customer_Item_no which shows it is formated as a string(15) it displays the value as   9528541330   

any ideas on how i can get the actual value of 0009528541330 onto my report?   i cannot say we will always get 3 leading zero's or such as the customer item number.

Peter

  • 0
    Is it possible that Crystal is viewing it as a number somehow? I would try a formula like:

    totext({SO_SalesOrderDetail.UDF_Customer_Item_no})
  • 0 in reply to Jon_K
    thanks Jon, tried that didnt work.
    but i believe the problem is Excel, where it is converting the value to number and removing leading zeroes when you open the file.
    my work around is to export the CSV file as .txt file, then import it into excel, and making the column TEXT for each of the fields that have leading zero's, then the leading zero's stick to the number and I am able to use.

    adding a step but it works. thanks for the reply.
  • 0 in reply to stircrazy08
    I have run into that exact same problem with CSV files, it is an excel issue. If you could generate your import files as an XLS or XLSX spreadsheet instead of CSV you could switch your VI job file type from Delimited (CSV) to Excel XLS (Version 4) which uses .XLS spreadsheets or Excel XLSX (Version 12) which would use .XLSX.

*Community Hub is the new name for Sage City