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

[RamziK]

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.

 

[pmax9999]

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

 

[RamziK]

I uploaded the records as an excel sheet for demonstration.
thank you

 

[IanWaterman]

Use a Command instead of the table
Base your SQl on this

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

 

[RamziK]

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

 

[IanWaterman]

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

 

[lbass]

Take a look at thread767-1553861. 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

 

[RamziK]

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.