Description

We needed the ability to group line item data in the proper sequence in an Estimate or Order with up to 4 levels of child items.

Basic Discussion of Approach

Cyrious stores Line Item data in Outline format in the TransDetail object name {TransDetail.LineItemNumber} – E.g. 1.A.1.a. We were working with Orders with 2,3, and 4 levels of child items and wanted to convert the line itme number to numeric format so that we could group line items sequentially which does not happen with alphanumeric text. We were aware that Cyrious includes an index number of line items and can arrive at the same result with some iterative looping but we wanted something simpler. Consequently, we decided to create an algorithm based on “10's” which would convert to 4 sub-levels. In this manner the first number would be in the 100,000 range, the second in the 10,000 range, the third in the 1000 range and the fourth in the 10 range.

Using Crystal's split function, we are able to create an array of the Line Item Number with each element a separate row, using the “.” (period) delimiter with which they are stored. Crystal's ubound() function returns the number of rows in the array which tells us the depth.

We use the Crystal Case function to convert the alphanumeric letters to numerals

After that it is simple concatenation.

The formula shown works for Orders with 4 levels of line items which is the most we encounter but could be expanded for additional depth by increasing the number ranges. The number of child items under a parent line item is limited to 10 with this approach; but we don't experience Orders with more than 10 child items in a single category. The code below only converts 5 letters to numbers but this is easily expanded with additional case entries.

Steps

1. The following code accomplishes our objective and line items sort properly.

//Create array from Line Item number field
local stringvar array LiNumbers := Split ({Command.LineItemNumber}, ".");
//Convert alpa letters in position 2 to numbers
Local numbervar AlphaConvPos2 :=
IF ubound(LiNumbers) in [2,3,4] THEN
Select LiNumbers[2]
Case "A":1
CASE "B":2
CASE "C":3
CASE "D":4
CASE "E":5
ELSE 0;
//convert alpha letters in position 4, if extant, to numbers
Local numbervar AlphaConvPos4 :=
IF ubound(LiNumbers) >=4 THEN
Select LiNumbers[4]
Case "A":1
CASE "B":2
CASE "C":3
CASE "D":4
CASE "E":5
ELSE 0;
//Convert the alpha LI in row 1 "x" to a number
TONUMBER(LiNumbers[1]);
//  (LiNumbers[1]*100000)+(LiNumbers[2]*10000)+(LiNumbers[3]*100)+AlphaConvPos4 *10)
Select ubound(LINumbers)
Case 1:TONUMBER(LiNumbers[1])*100000
CASE 2:(TONUMBER(LiNumbers[1])*100000)+(AlphaConvPos2*10000)
CASE 3:(TONUMBER(LiNumbers[1])*100000)+(AlphaConvPos2*10000)+(TONumber(LiNumbers[3])*100)
CASE 4:(TONUMBER(LiNumbers[1])*100000)+(AlphaConvPos2*10000)+(TONumber(LiNumbers[3])*100)+(AlphaConvPos4 *10)

Contributor:Steve Gillispie

Date: 9/12/2017

Version: Control 6,1/Crystal XI