special group formula

[Former Member]

I've got the need to create a group based on certain criteria. I have a series of varying item lists in my QuickBooks item table. They all have different names and have a certain structure to them. There are about 50 that have the following type name: F6112-PT-6001 where all the letters and number stay the same except for the last 4 which go up in increments of 1. Ex. F6112-PT-6002.

What I need is the ability to 1) search in the item name for the value F6112-PT and 2) if it exists strip the last 4 digits off the right, 6001 and use that as the group name.

I know how to do this type of stuff in Excel and SQL but not Crystal Reports. Any ideas?

Regards,

Derek Caldwell

[Former Member · Accepted solution]

The functions provided worked for stripping off the numbers but I still don't understand how to search through a value to determine if there is a certain sql text string present. Need to search for values "F6112-PT" and if they are present then use the Right or LEFT function.

[former_member260594 · Accepted solution]

Derek,

You can use the In String function to search for the text, for example;

If InStr( "F6112-PT", {database field} ) > 0 then LEFT( {database field}, 😎 else {database field}

[Former Member]

The solution worked except the formula was slightly wrong. Here is what did the text string search and then displayed the group by the last 4 digits;

If InStr( {database field} ,"F6112-PT" ) > 0 then LEFT( {database field}, 😎 else {database field}

Thanks for your help.

[Former Member]

Create this formula using your field that contains the values and then use the formula to group on:

LEFT (, 😎

[Former Member]

Hi Derek

Create a formula field to strip the last 4 digits off the right, 6001.

Use Right function (search for the syntax in Crystal Help) to do this.

Now apply a group on this formula field.

Hope this helps!!!

Regards

Sourashree