Splitting a Field and a Different Description

[scott1971]

Guys,

I am on CR XI. I have a list of 3 to 5 digit codes in the format 000 running to 999.99 Each of these codes has a descriptive.

I have created a formula to only show the first 3 digits of the code ie 000 to 999 so as it will 'roll' the codes together. What I also need to add is the correct descriptive but that isn't happening. I think it's getting the first descriptive it hits on the database so instead of getting the 000 descriptive it is getting the 000.15 etc etc.

I am grouping on my formula as I need to do a count as well. Any ideas how I get the correct descriptive to show?

 

[lbass]

Please show the content of your formula.

-LB

 

[scott1971]

Hi lbass

The formula is left ({Field_Name},3). The field has values and descriptions as follows:

001 Finger injury
001.00 Finger injury not specified
001.01 Finger injury (skin)
through to
999 Foot injury
999.99 Foot Injury (toe)

I want to return the initial 3 digit code only but I need to count ALL the codes that start with those 3 digits BUT I need to return the descriptives of Finger injury, foot injury etc etc.

cheers

Scott1971

 

[lbass]

If you just place the actual code (not your formula) from the description table and the description on the detail section of a new report, what do you see? Also, what is the datatype of the code?

-LB

 

[scott1971]

Hi there

If I do that then it's spot on, I get the correct descriptive at each level as shown in my previous post, it's only goes pear shaped when I try and roll the codes together. what I think it does is it finds the first instance of any of the codes used from the file and returns that, not the corresponding code.

The field is a 6 character 'String'.

Really struggling to get past this and the report really couldn't be simpler!

cheers

Scott1971

 

[lbass]

I was asking to see a sample as described in my last post. Also, you earlier said that the string was 3 - 6 characters in length, but now you are saying it is 6? If so, then how does the parent record display? 100.000? Please do show a sample that is sorted by the code field.

-LB

 

[scott1971]

lbass,

The Code I showed in my post is exactly what I see without using my formula. The field length in the database is 6 characters which includes the decimal place but there are obviously blanks in there as there are times when only the 3 or sometimes 5 characters are displayed.

The only codes I want to display are the 3 digit ones, I don't want to display anything else but I need to count ALL the codes that start with those 3 digits.

Sorry if I'm confusing the issue here.

cheers

Scott1971

 

[lbass]

You should be able to group on:

left({table.code},3)

Then add a sort on {table.code} so that the three-digit codes appear first, and then drag the description field into the group header, and suppress the detail section.

The only problem would be if a 3-digit code did not appear in the data, but the 6-digit code did, but if these are parent-child, there should always be a three-digit code.

-LB

 

[scott1971]

lbass,

sorry, I should have said, I have to join this file to another one that uses these codes against a claim number. My count is done on that so when I try and return the description it returns the first one it finds within that range which is seldom the 3 digit code.

Sorry to go on with this one but I just can't get this working at all.

 

[scott1971]

sorry, I should have said, some of the codes might not be used so with the 000 code the only code we have used is 000.9 so that is the description that is returned as it clearly can't return something that isn't on the database as being used. there lies my problem I think.

Cheers

Scott1971

 

[lbass]

Please show a sample of data in your actual report including the claim number data, and also note what you are grouping on, etc.

-LB

 

[lbass]

If you don't mind the performance hit, you could add the description table in a subreport and then link the code in that table to the main report group formula:

left({table.code},3)

-LB