[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 607
  • Last Modified:

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
0
mbroad02
Asked:
mbroad02
  • 9
  • 3
  • 2
1 Solution
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 9
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now