This is a toughy and I have already spend a full day on this. I am not sure what I can do. It works as far as I know but there is a little thing that I am not seeing. I hope you can help.
Here is the specs. The report has 5 groups. All but one is grouped by a recordfield. The only one is a user selection to group the report on that will "sort" the records. It works in every case except in one.
Group1 Part Type
Group2 Store Number
Group3 User selection
Group4 Inventory Number
Group5 Category
The user selection group has 8 selections to sort the record by. All but two are numeric fields. The two is converted by using ASC function to make it numeric as the formula deals with one data type for output.
Here is the formula.
IF {?SortField}=1 THEN
{@TotalLookups}
ELSE IF {?SortField}=2 THEN
{@TotalSales}
ELSE IF {?SortField}=3 THEN
{InvReprice.OutOfStockLookup}
ELSE IF {?SortField}=4 THEN
{InvReprice.InStockLookup}
ELSE IF {?SortField}=5 THEN
{InvReprice.Returns}
ELSE IF {?SortField}=6 THEN
{InvReprice.InvQty}
ELSE IF {?SortField}=7 THEN
(Asc(Mid({InvReprice.LocationCode},1,1))*128*128*128*128*128*128*128*128
+Asc(Mid({InvReprice.LocationCode},2,1))*128*128*128*128*128*128*128
+Asc(Mid({InvReprice.LocationCode},3,1))*128*128*128*128*128*128
+Asc(Mid({InvReprice.LocationCode},4,1))*128*128*128*128*128
+Asc(Mid({InvReprice.LocationCode},5,1))*128*128*128*128
+Asc(Mid({InvReprice.LocationCode},6,1))*128*128*128
+Asc(Mid({InvReprice.LocationCode},7,1))*128*128
+Asc(Mid({InvReprice.LocationCode},8,1))*128
+Asc(Mid({InvReprice.LocationCode},9,1)))*-1
ELSE IF {?SortField}=8 THEN
(Asc(Mid({InvReprice.InventoryNumber},1,1))*128*128*128*128*128*128*128*128*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},2,1))*128*128*128*128*128*128*128*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},3,1))*128*128*128*128*128*128*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},4,1))*128*128*128*128*128*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},5,1))*128*128*128*128*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},6,1))*128*128*128*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},7,1))*128*128*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},8,1))*128*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},9,1))*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},10,1))*128*128
+Asc(Mid({InvReprice.InventoryNumber},11,1))*128
+Asc(Mid({InvReprice.InventoryNumber},12,1)))*-1
InventoryNumbers and Location is a DB string field. They are always alphanumeric. Examples of inventory number 100-123456AB or 220-654321BA. Examples of Location MM123, BACK01, CA-4AA-04. Both may not always be a max length of 12 for inventory or 9 for locations but always works when less. I even checked pages on Inventory number sort. Even it comes out beautiful regardless of having 12 characters. Location is only 9 and has a problem.
The formula was written by a consultant back when we were using CR7. Now we are on CR10 with VB6 and SQL Server 2000. I don't know CR10 so well.
I do know that you can trick the sort by using two Sort formulas for having two data types. I have SortString and SortNumber that I just add to Record Sort Expert. But this time it is a group and you can only have one group formula as you could only select one thing to group on. So that is why the formula above is written that way.
The client is complaining that the Location is not sorting correctly. It is showing (notice first two)
A-46-C-08
A-46-C-07
A-46-C-08
A-46-C-09
This is just one example. There are many like that where the last digit is throw off of the sort order. How do I correct that or try some solutions?
Thank you.
Here is the specs. The report has 5 groups. All but one is grouped by a recordfield. The only one is a user selection to group the report on that will "sort" the records. It works in every case except in one.
Group1 Part Type
Group2 Store Number
Group3 User selection
Group4 Inventory Number
Group5 Category
The user selection group has 8 selections to sort the record by. All but two are numeric fields. The two is converted by using ASC function to make it numeric as the formula deals with one data type for output.
Here is the formula.
IF {?SortField}=1 THEN
{@TotalLookups}
ELSE IF {?SortField}=2 THEN
{@TotalSales}
ELSE IF {?SortField}=3 THEN
{InvReprice.OutOfStockLookup}
ELSE IF {?SortField}=4 THEN
{InvReprice.InStockLookup}
ELSE IF {?SortField}=5 THEN
{InvReprice.Returns}
ELSE IF {?SortField}=6 THEN
{InvReprice.InvQty}
ELSE IF {?SortField}=7 THEN
(Asc(Mid({InvReprice.LocationCode},1,1))*128*128*128*128*128*128*128*128
+Asc(Mid({InvReprice.LocationCode},2,1))*128*128*128*128*128*128*128
+Asc(Mid({InvReprice.LocationCode},3,1))*128*128*128*128*128*128
+Asc(Mid({InvReprice.LocationCode},4,1))*128*128*128*128*128
+Asc(Mid({InvReprice.LocationCode},5,1))*128*128*128*128
+Asc(Mid({InvReprice.LocationCode},6,1))*128*128*128
+Asc(Mid({InvReprice.LocationCode},7,1))*128*128
+Asc(Mid({InvReprice.LocationCode},8,1))*128
+Asc(Mid({InvReprice.LocationCode},9,1)))*-1
ELSE IF {?SortField}=8 THEN
(Asc(Mid({InvReprice.InventoryNumber},1,1))*128*128*128*128*128*128*128*128*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},2,1))*128*128*128*128*128*128*128*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},3,1))*128*128*128*128*128*128*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},4,1))*128*128*128*128*128*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},5,1))*128*128*128*128*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},6,1))*128*128*128*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},7,1))*128*128*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},8,1))*128*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},9,1))*128*128*128
+Asc(Mid({InvReprice.InventoryNumber},10,1))*128*128
+Asc(Mid({InvReprice.InventoryNumber},11,1))*128
+Asc(Mid({InvReprice.InventoryNumber},12,1)))*-1
InventoryNumbers and Location is a DB string field. They are always alphanumeric. Examples of inventory number 100-123456AB or 220-654321BA. Examples of Location MM123, BACK01, CA-4AA-04. Both may not always be a max length of 12 for inventory or 9 for locations but always works when less. I even checked pages on Inventory number sort. Even it comes out beautiful regardless of having 12 characters. Location is only 9 and has a problem.
The formula was written by a consultant back when we were using CR7. Now we are on CR10 with VB6 and SQL Server 2000. I don't know CR10 so well.
I do know that you can trick the sort by using two Sort formulas for having two data types. I have SortString and SortNumber that I just add to Record Sort Expert. But this time it is a group and you can only have one group formula as you could only select one thing to group on. So that is why the formula above is written that way.
The client is complaining that the Location is not sorting correctly. It is showing (notice first two)
A-46-C-08
A-46-C-07
A-46-C-08
A-46-C-09
This is just one example. There are many like that where the last digit is throw off of the sort order. How do I correct that or try some solutions?
Thank you.