Crystal Formula for creating fixed-length variable

I attempted this question earlier but the answer was not what I was looking for.  
My experience is somewhat limited In Crystal Developer.
My specs are that I need to create a fixed-length flat file, 500 bytes.
I am to use Crystal to select the databse fields, place them on a report and then export that report to this flat file.
I have no trouble with any of that EXCEPT the fixed length feauture.
If I wish for the flat file to look like
"field1    field2    field3                                                                      "
(spaces between fields and padded at end)
how do I accomplish this in Crystal (using a formula)?????
The output record layout is attached.  The fixed-file layout is a requirement of the State, to whom this file is being sent.
Thank you VERy much
Mark
Sample-of-Record-layout.doc
mbroad02Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbroad02Author Commented:
Remember, my Crystal experience is limited, so the simpler to follow, the better.
Thank you
0
peter57rCommented:
You need to create a formula field which builds the entire record in one string.

For example:

"A" & Left({Employee.First Name} & space(9),9) & Left({Employee.Last Name} & space(9),9) & Left({Employee.Position} & space(30),30) & space(50)

Each field is concatenated with the same number of spaces as the required fields size and then the leftmost N characters of that concateneted value  are used.
Add the field to the detail line as far left as you can go.
Bear in mind that unless you choose a fixed pitch font for your report, the columns will not visibly align even though the fields are consistent sizes.




0
mbroad02Author Commented:
Is this syntax (except for field names) as I would enter it?
How do I insure that each record is the same length?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

peter57rCommented:
The syntax is OK but I have not put all your fields in - it's just an example of what to do.
The records are the same length because the formula field is a fixed length.  In my example it is
1 + 9 + 9 + 30 + 50
0
mbroad02Author Commented:
Not having much experience in using stringvars, is this what I am looking at doing? (Named "OUTPUT")

stringVar OUTPUT := & Left({Defendant1} &Left( Defendant2) & Left({Def name} & Left (Def Address) & Left({Text Summary} & (150)

And then I would place the fomula on the report

Please advise....Thanks
0
mlmccCommented:
I won't swear to this but I believe Crystal will trim the trailing blanks when you export.

Try this
stringVar OUTPUT := Left({Field1} & {Field2} & ... & '500 spaces', 500)

mlmcc
0
peter57rCommented:
I can't be entirely sure of this either but running a test export and opening the .txt file in Word or XL seems to show that the trailing spaces are retained.  I suspect that importing into some database apps would strip the spaces though.
0
mbroad02Author Commented:
Thank you.  I will try these out and let you know.
0
mbroad02Author Commented:
I am sorry, but I was out of the office for a week and did not get a chance to try it out.  I will very soon and let you know.  thanks!!!
0
mbroad02Author Commented:
I am testing the feature right now.
0
mbroad02Author Commented:
Ok, I keyed in a very simple version to see if it would work.  I typed in the following formula and placed  the formula on the report line:
stringVar OUTPUT := Left('D' & {IDNT.SSN} & ' ',500);
From what I can tell, this will place a 'D' in the first position, IDNT.SSN in the next positions and will space fill up to 500 bytes.  
Unfortunately it did not work.  Am I attempting this correctly?

Thanks,
Mark
0
mlmccCommented:
No.  That will give you D then the SSN field.  It will select the first 500 characters but it will not padd if there are not enought characters.

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mbroad02Author Commented:
Ok, I think I am asking Crystal to do somthing it is not meant to do.  I guess I will have to find another avenue to get what I need.
Thank you anyway.
0
mbroad02Author Commented:
I am asking Crystal to do something it really is not meant to do.  I will look for another solution using another app.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB Reporting Tools

From novice to tech pro — start learning today.