Check the first 6 characters or the first 3 characters of an ID 1

[blueboyz]

I am using Sage 50 (Peachmtree) Quantum 2013 with pervasive sql as the database engine with Crystal 2008.

I need help with creating a formula to group item id's.
If the item id contains a dash "-" then the formula should extract the first 6 characters and create a group when the first 6 characters are the same.
Otherwise the formula should extract the first 3 characters and create a group when the first three characters are the same.

The item id table is {LineItem.ItemID}.

Here are some of the item id's:
M27055
M27505
M27901
M28505
M28055
M289128
M29055
M29505
M29901
M299128
M30-10055
M30-10505
M30-10901
M30-30055
M30-30505
M30-30901
M30-309128
M30-98055
M30-98505
M30-98901
M30-989128
M30-99055
M30-99505
M30-99901
M30-999128

So the group assignment would be:

M27055 = 1
M27505 = 1
M27901 = 1

M28505 = 2
M28055 = 2
M289128 = 2

M29055 = 3
M29505 = 3
M29901 = 3
M299128 = 3

M30-10055 = 4
M30-10505 = 4
M30-10901 = 4

M30-30055 = 5
M30-30505 = 5
M30-309015 = 5
M30-309128 = 5

M30-98055 = 6
M30-98505 = 6
M30-98901 = 6
M30-989128 = 6

M30-99055 = 7
M30-99505 = 7
M30-99901 = 7
M30-999128 = 7

When the groups are assigned a number, I can sort the groups by number as well as display a group name.
I can create a formula to capture the item id by 3 or 6 characters and then assign a group number, but I don't know how to create a formula to check for both cases.

If Left({LineItem.ItemId},3) = "M27" then 1
else If Left({LineItem.ItemId},3) = "M28" then 2
else If Left({LineItem.ItemId},3) = "M29" then 3, etc.

But how do I check if the item id has a dash and if it does, then use the first 6 characters of id to create the group number?
If the item id does not have a dash then the formula should use the first 3 characters of the id to create the group number.

 

[AndyMc]

formula:
//@GroupName
stringvar gn := {LineItem.ItemID};
if instr(gn,"-") > 0 then left(gn,6) else left(gn,3)

 

[AndyMc]

You would group on the formula above

 

[blueboyz]

Amazing! Your formula worked like a charm!
Thank you so much.

-GR

 

[blueboyz]

If the dash has to be in the 4 position, would the formula be:

//@GroupName
stringvar gn := {LineItem.ItemID};
if Left(gn,4) = "-" then left(gn,6) else left(gn,3)

There are some item id's like:

M75505
M75920
M75920-CAD
M75920-EU

and they create one group (which is correct) using the formula in this reply.

However, the item id's like:
M80-91911
M80-85932
M80-86912
M80-87912
M80916
form another group.

The correct grouping should be:
M80-91911
M80-85932
M80-86912
M80-87912

and another group for:
M80916

 

[AndyMc]

No, if you wanted to look specifically at position 4 then use MID function (position 4, length 1):
//@GroupName
stringvar gn := {LineItem.ItemID};
if mid(gn,4,1) = "-" then left(gn,6) else left(gn,3)

 

[AndyMc]

OR
//@GroupName
stringvar gn := {LineItem.ItemID};
if instr(gn,"-") = 4 then left(gn,6) else left(gn,3)

 

[blueboyz]

Andymc:

Thank you again for your help. I tried the 2nd formula you provided in your last post:

//@GroupName
stringvar gn := {LineItem.ItemID};
if instr(gn,"-") = 4 then left(gn,6) else left(gn,3)

and it worked perfectly!

I am grateful for your help.