Count number of entries from a multi value field

[n2nuk]

Hi there,

Is it possible to count the number of entries i have in a multi value field, each value is separating by a colon (,) and in some instance can contain upto and above a 1000 entries. the field looks like 4532232,5565443,3434343,5656556,23234344,..

Thanks in advance

 

[IanWaterman]

Can't test this as do not have access to Crystal at present, create this formula and it should work!

@fieldcount
stringVar array x;
x := Split({yourlistField}, ";");-- You said delimiter is colon but entered a comma change "," if that is your delimiter
Count(x)

Limitation might be 1000 as I think Crystal limit for arrays is 1000 elements
In which case you can estimate Character length for 999 entries and then split field into two parts using Left() and Mid() functions expand the above formula accordingly and then add the two counts.

Ian

 

[n2nuk]

Hi Ian,

thanks for getting back to me, the delimiter is a comma, my bad! the array length is an issue as it complains that the array length must be between 1 and 1000! I understand your comment regarding splitting the counts and then count the totals, but would really appreciate it if you are able to point me in the right direction!

 

[IanWaterman]

Do you know roughly how many characters make an array of 999 elements?

Lets assume it is 4000

@fieldcount
whileprintingrecords;
stringVar array x;
StringVar Array y;
x := Split(left({yourlistField},4000), ",");
if length({yourlistField}) > 4000 then Y:= Split(mid({yourlistField},4001), ",");
if length({yourlistField}) > 4000 then Count(x)+Count else Count(x);

Downside is that you are likely to split field between commas. so will end up with a count which is 1 too high

Ian

 

[n2nuk]

thanks for the update, unfortunately it generating an error saying that the word Else is missing, i have tried a few permutations, but no joy!

 

[IanWaterman]

Try changing this line

if length({yourlistField}) > 4000 then Y:= Split(mid({yourlistField},4001), ",");

to

if length({yourlistField}) > 4000 then Y:= Split(mid({yourlistField},4001), ",") else y:= ("");
OR
Y:= (if length({yourlistField}) > 4000 then Split(mid({yourlistField},4001), ",") else ("");

I don't have crystal at present so working a bit blind.
Ian

 

[n2nuk]

Many thanks for all your assistance, however still no joy, but its Friday and the sun is shining, which is a bonus in this part of the UK so am off!

If i haven't harangued too much already ill be in contact on Monday!

 

[IanWaterman]

Sorry all out of ideas
Try breaking formula down into smaller parts and see which bits work. eg

@fieldcount
whileprintingrecords;
stringVar array x;

x := Split(left({yourlistField},4000), ",");

Count(x)

If that does not work Try reducing left function to less then 4000.
It will be an interative process.

Good luck
PS looks like our burst of summer in the UK has come to an end!!