×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Sorting UserDefined Fields in Crystal Report (coming from Single Record)

Sorting UserDefined Fields in Crystal Report (coming from Single Record)

(OP)
I have in ONE record with several UserDefined fields - UDF (5+5 in total) like so.
UDF1 = 40 (UDF1a = $1.9) UDF2 = 32 (UDF2a =$2.1)
UDF3 = 90 (UDF3a = $1.6) UDF4 = 54 (UDF4a =$1.8)
UDF5 = 87 (UDF5a = \$1.3)

I would like to sort them in ascending order keeping the fact they come from ONE record only. I am not too sure about the best practice or the formulas i need to use.
UDF2 =32 USD2a
UDF1 =40 USD1a
UDF4 =54 USD4a
UDF5 =87 USD5a
UDF3 =90 USD3a

I am not going to give suggestions of what I have tried because ironically I am too numb from trying.
It is amazing... One day I am on top of the world with CR and one day i feel i know NOTHING...
Thank you for our help.

RE: Sorting UserDefined Fields in Crystal Report (coming from Single Record)

Please provide actual sample data from the User Defined Field, showing the separator character, and the location of each piece of data that you need to display and sort on.

Regards
Pete

RE: Sorting UserDefined Fields in Crystal Report (coming from Single Record)

Use a Command instead of the table

select RecordID, UDF1 as Qty, UDF1a As UnitCost
From Table
Union all
select RecordID, UDF2 as Qty, UDF2a As UnitCost
From Table
Union all
select RecordID, UDF3 as Qty, UDF3a As UnitCost
From Table
Union all
select RecordID, UDF4 as Qty, UDF4a As UnitCost
From Table
Union all
select RecordID, UDF4 as Qty, UDF4a As UnitCost
From Table

Ian

RE: Sorting UserDefined Fields in Crystal Report (coming from Single Record)

(OP)
I already have a massive sql generating all other data on the report. I was hoping to use the fields and sort them out in CR formula, then take the sorted fields (or parameters) and put them all in one detailed section and suppress which ever is empty.

kind of...
Define parameters... (and I am not too sure if I need them but I assume then I can use them to sort them in order)
Parameter X(1) = smallest
Parameter X(2) = second smallest, etc, etc, etc.

then do CR formula to do the following....
Y=0
X(y+1)
UDF list = remaining UDFs I want to compare.
compare the remaining UDFs
Find the smallest and set parameter X(y) = the smallest UDF found from selection
Eliminate the smallest from the UDFs remaining
loop

I am not quite there yet on the CR formulas. there could be a better way I am not to sure. sql is already generating the table for me.

RE: Sorting UserDefined Fields in Crystal Report (coming from Single Record)

You can not create data in Crystal. So even if you do split data I can't see how you will sort in order you want.
So I don't think you an achieve what you want.

Still think it will be easier to replace your table with the union query, or if you are creating the table in SQL just unpivot it.

Ian

RE: Sorting UserDefined Fields in Crystal Report (coming from Single Record)

Take a look at thread767-1553861: Sorting Summary labels in an array. I think you could use a formula to make an array of the user fields. Something like:

Stringvar array x := makearray([field1, field2,field3,etc.]);
""

Then use the method in the thread to sort the array and then reference array elements in separate detail sections--detail a to f.

-LB

RE: Sorting UserDefined Fields in Crystal Report (coming from Single Record)

(OP)
my Major problem was the fields where string and not integer and had many corrupt data in them... so First I followed Ibass recommendation and was able to create an array of numbers however they where in Alphabetical order and not sorted by value.
This lead me to do a sql sub-report following Ian Watterman's recommendation... and this worked out great but not before I cleaned the userdefined fields.

select RecordID, UDF1 as Qty, UDF1a As UnitCost
From Table
Union all
select RecordID, UDF2 as Qty, UDF2a As UnitCost
From Table
Union all
etc etc, etc
thanks all for our help.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!