calcualted field help

[grumbachr]

I am using a "Case" calculation function to populate a calculated field.

I have one field that is populated with data that is imported from an external file. Lets call it Type and it can have similar values like Item 1 (v1.0), Item 1 (v1.1), Item 2 (v2.0) Item 2 (v2.2), Item 2 (v2.5) etc...

I want to simplify my Case statement so I don't have to express each possible value. Right now I would have a statement like; Case ( Type= "Item 1 (v1.0)" ; "Item 1"; Type= Item 1 (v1.1) ; "Item 1"; Type= "Item 2 (v2.0)" ; Item 2 ; Type= Item 2 (v2.2) ; Type = Item 2 (v2.5); Item 2 ; " ")

Simplified I want it to look more like this.

Case ( Type= "Item 1 *" ; "Item 1"; Type= "Item 2 * " ; Item 2 ; " ")

I know the asterisk wont work here but what other option do I have?

I am not good with If statements but is that what I would need to build? If so could someone help me with a simple example please?

grumbachr

[-Queue-]

"Item " & Int(GetAsNumber(Type))

[grumbachr]

Will this work even if there is no consistency on what

[-Queue-]

Ah, I had thought you meant that only the data in parentheses was in the Type field. Don't ask me why.

So, is the item information that you want to retrieve simply the data not in parentheses? Like PowerPC G4, PowerMac G3, PowerPC 970, etc.?

If so,

Trim(Let ( P = Position( Type; "("; 0; 1 );

Left( Type; Case( P; P - 1; Length(Type) ) )

))

should work.

[grumbachr]

trim would work if it wasn't for how Apple changed the format after the G4. I really just want to identify them as a G3, G4, G5. So I need PowerPC 970 (2.2) or what ever to equal G5. The G3 is easy with the trim command but when they start adding a version number and changing the name that

[-Queue-]

Trim was just to clean up any leftover spaces after removing the parenthesized portion. You could always test the rightmost word after removing parentheses to see if it contains "G", otherwise hardcode the type.

Let([

P = Position( Type; "("; 0; 1 );

T = Left( Type; Case( P; P - 1; Length(Type) ) );

R = RightWords( T; 1 )];

Case( PatternCount( R; "g" ); R; R = "970"; "G5"; "Unknown" )

)

I used "Unknown" to make it easy to find records that don't fit the other criteria. Once you find any, you can add the additional criteria test and desired result before "; 'Unknown'" and remove it altogether once you're satisfied with all results.