Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Crystal Reports - trimming Strings - Need to produce a fixed length file

Posted on 2011-03-17
4
531 Views
Last Modified: 2012-05-11
Hi,

I have a fairly simple job but not sure of the syntax
I am building up a report that will produce a fixed length csv style file
so I have 17 numbered formula fields and then I put them all into an 18th formula
which goes onto the report

so the final string looks like

{@01 - User}+""+{@02 - Site}+"  "+{@03 - PONumber}+"   "+{@04 - FromLocation}+"                "+{@05 - PalletNumber}+""+{@06 - PartNumber}+""+{@07 - EANCode}+""+{@08 - Quantity}+""+{@09 - ToLocation}+""+{@10 - AOD}+"   "+{@11 - Site2}+""+{@12 - Job}+""+{@13 - InOutIndicator}+""+{@14 - Whole}+""+{@15 - Date1}+""+{@16 - Time1}+" "+{@15 - Date1}+""+{@16 - Time1}+""+{@17 - MrIddd}+"                              "


Some of the fields have a static value, some are dynamic from the database

I need to do 2 things.

1. for 08-Quantity field I need to pad the number with zeroes - ie if the qty is 25 I want it to show 00025 or if it 175 it should say 00175
2. All the fields have a minimum/maximum length so 03-PO Number could come from the database as a six digit number or a 10 character description, i need it to be always 5 characters wide

If someone could point me towards the Stringy functions I need to use

Thanks

Seb
0
Comment
Question by:sebadlington
  • 2
  • 2
4 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 35155671
Totext is the main function needed for formatting numbers.

Totext({@08 - Quantity}, "00000")

I don't understand what you want to do with your other field.

I don't see how it can be either a number or a text field. It sounds like it must be text field.
But how do you decide which bit to include and which bit to leave out?
0
 

Author Comment

by:sebadlington
ID: 35156302
Thanks peter that has sorted the qty out

For the other field I basically need to trim it - it doesn't matter if i chop the end off as it's always the first 5 digits of the field that I need but it is possible that I will sometimes only get a 3 digit number through in which case I'll need to padd it out with space
so if I get  123456789 I want it to read "12345"
if I get 123  i want it to read "123  "  so 3 numbers with 2 added whitespace characters?

Basically I'm squirting this back to a customer who has a COBOL system and they cant' accept a virable length file.
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 35157309
left({@03 - PONumber}+ "     ", 5)  

there are5 spaces between the  "     "
0
 

Author Closing Comment

by:sebadlington
ID: 35164128
You're a legend! Thanks Peter - got the files all looking hunkdory now

Cheers

Seb
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I previously wrote an article addressing the use of UBCD4WIN and SARDU. All are great, but I have always been an advocate of SARDU. Recently it was suggested that I go back and take a look at Easy2Boot in comparison.
In this article, you will read about the trends across the human resources departments for the upcoming year. Some of them include improving employee experience, adopting new technologies, using HR software to its full extent, and integrating artifi…
This video shows how use content aware, what it’s used for, and when to use it over other tools.
This video demonstrates how to use each tool, their shortcuts, where and when to use them, and how to use the keyboard to improve workflow.

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question