Concatonate, exceeds 254 Characters 3

[AyJayEL]

I am trying to concatonte a field. However, occasionally I am getting a message that I have exceeded 254 characters. Can anyone help me with this? I think I remember someone saying in a previous thread that you need to create an array. I need help with that! ;-)

Learn something new every day *:->*
AyJayEl

 

[Madawc]

One solution in Crystal 8.5 is to split your text into two Formula Fields and put them together into a text object on the report. Text objects can be any size, as far as I know.

Madawc Williams
East Anglia, Great Britain

 

[AyJayEL]

Madawc, I don't think that will work as it's not a big field that I need to shorten.

I'll try to explain better.

Students can have several exclusions during a year, Instead of repeating rows for the student (or using a sub report) I want each exlusion listed in one field. So it would look like this normally;-

Name Date How long
Fred 01/02/02 2
Fred 04/02/02 6
Fred 03/03/02 8

I need it to be on one line like so;

Fred 01/02/02, 2, 04/02/02, 6, 03/03/02, 8

I can do this but sometimes (when they have been really naughty boys!) the characters exceed 254 and I get a message to the effect.

Is that clearer?

Learn something new every day *:->*
AyJayEl

 

[Naith]

You can double your maximum limit without an array.

stringvar y;
stringvar z;

if trim = ''
then
if length(z) + 1 > 254
then y := y + 'b'
else z := z + 'b'
else
if length + 1 > 254
then y := y //*//
else y := y + 'b';

Call z and y in seperate formulas, which you drop into a text box. If there's a likelihood you may go over 508 chars, use another variable which is triggered by the //*// line to alert you with "Values have been missed", or use the extra variable to increase the limit as above to 762.

All the best,

Naith

 

[Naith]

I knocked this up quickly and didn't convert it for you before posting. Sorry about that; replace the '1' in the example with length({your.field}) and the 'b' with {your.field}. The initial trimmed check just ensures that the z variable cannot be updated once the y variable starts accepting values.

Naith

 

[AyJayEL]

Thanks Naith, that works!

But now I have realised that my records wouldn't have exceeded 254 characters if they hadn't repeated. For instance I am trying to capture lots of data about a child on one line. Like, as above, the Exclusions, also their Special Needs, which schools they have attended etc. What I have found is that if a child has more than one Special Need say, then the Exclusions data will be repeated that many times. How can I prevent this happening. I tried using Sub reports but can't get the concatonated data to export to Excel (which is the ultimate aim).

Learn something new every day *:->*
AyJayEl

 

[Naith]

I'm sorry, but I'm having a little difficulty picturing what you mean. Could you take some time out to show me a snippet of your data, combined with what your formula is currently doing with that data, versus what it should be doing.

Thanks,

Naith

 

[pelajhia]

The second problem sounds like a join issue perhaps? Thanks for the great string idea Naith!

 

[AyJayEL]

Ok... err.. I'll try...

Very simply if Fred has one Special Need and has attended three schools a report would look like this;-

Name Need School
Fred HI 3456
Fred HI 4532
Fred HI 6573

If he had two Special Needs and 3 schools it would look like this;-

Name Need School
Fred HI 3456
Fred DWN 3456
Fred HI 4532
Fred DWN 4532
Fred HI 6573
Fred DWN 6573

As I have been asked for all the data on one row I have grouped by Fred's ID and created the above formula so that (hopefully) on the second scenario the record would look like this when placed in the group footer.

Name Need School
Fred HI, DWN 3456, 4532, 6573

What I get is;

Name Need School
Fred HI, HI, HI, DWN, DWN, DWN 3456, 3456, 4532, 4532, 6573, 6573

Can you see what is happening? Once I start to add in Exclusions it gets even worse.

Learn something new every day *:->*
AyJayEl

 

[Naith]

And what happens if you create a subreport grouped by Fred's ID, and Special Need and place it in the main report group header. (Suppress all the sections in the subreport, so it doesn't distort the size of the main group header.)

Catch the Special Needs in a shared variable and return them in the main report group footer.

The main report, meanwhile, groups by Fred's ID and school.

So your group footer would eventually be built like this:

Name Need School
{Table.KidsName} {@Shared Var Call Formula} {YourStackedString}

The idea would be to have a formula like the one above in the subreport just concentrating on the needs, and another one in the main report just concentrating on the schools. Because it looks like you can have a different number of needs and schools per kid, there's no point trying to handle both in the same formula. See what I mean?

Naith

 

[AyJayEL]

I do Naith, and I have tried it. The problem I come across then is the data won't export to Excel correctly. Try it and see what I mean.

I think I might rethink this. Just send all the raw data down to excel then use a pivot table to do some counting.

Learn something new every day *:->*
AyJayEl

 

[Naith]

Don't give up yet. I'm sure we can get what you want here.

Apologies for forgetting about the Excel point. You mentioned it earlier, but I guess it went straight in one eye and out the other.

Forget about the subreport, and try this modified approach, (which I admit I haven't tested, so suck it and see):

stringvar y;
stringvar z;

if instr(z,{your.field}) = 0 then
if instr(y,{your.field}) = 0 then
if trim = ''
then
if length(z) + length({your.field}) > 254
then y := y + {your.field}
else z := z + {your.field}
else
if length + length({your.field}) > 254
then y := y
else y := y + {your.field}
else z = z
else y = y;

Use this once for the Needs. Duplicate the formula and use it again for the Schools - obviously with different variables.

Naith

 

[Naith]

The first two lines should be:

if instr(trim(z),{your.field}) = 0 then
if instr(trim,{your.field}) = 0 then

Naith

 

[Naith]

Er...and the last two lines should be:

else z := z
else y := y;

Sorry, I'm really not very switched on today.

Naith

 

[Naith]

*sigh*

That's:

else y := y
else z := z;

The moral of this story: test your code.

Naith

 

[AyJayEL]

You are still a little star to me Naith.



Learn something new every day *:->*
AyJayEl

 

[Naith]

Bless you and your patience, my child.

 

[AyJayEL]

By George she's got it! I am going home now while I am ahead and I shall attempt the rest tomorrow.

Thank you for all your help and YOUR patience Naith. If I could I would award you with another star. But here, have this on me instead

Learn something new every day *:->*
AyJayEl