trying to create the following formula

[espltd]

Hi I am new to crystal report formulas but need to do the following. I have a field that looks like,
1
1
2
2
3
4
and I need to have it show up as
000001-02
000001-01
000002-02
000002-01
000003-01
000003-01
I tried using a next function in vb syntax but get an error.
thanks.

 

[Turkbear]

Hi,
Please explain how the results you want relate to the data in the field.
In other words, how does 1 become 000001-02?








To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[espltd]

Hi thanks for the quick response. the database puts out a number 1 but I need to display it on a report as 000001-01.
For example if there are 4 fives in a row I need to display it on the report as
000005-05
000005-04
000005-03
000005-02
000005-01
the actual for the column without a formula is just
5
5
5
5
5
I tried using
ToText(Cstr({table.field},"000000"))+"-0"+ToText(MyNumber,0,"","") where MyNumber is the 5,4,3,2,1 in the -xx portion. this works but I need to count ahead or something to know how many times the number repeats and then if 5 times use a 5 for MyNumber.

 

[lbass]

First insert a group on {table.field}. Then create two formulas:

//{@init} to be placed in the group header:
whileprintingrecords;
numbervar x := count({table.field},{table.field})+1;

//{@newfield} to be placed in the detail section:
whileprintingrecords;
numbervar x;
stringvar str;
x := x - 1;
str := totext({table.field},"000000") + "-" +
totext(x,"00");

This will display them in descending order within the group. You can suppress the group header and footer.

-LB

 

[espltd]

ok thanks for the information, will give it a try. Does seem easier to do it in a single thread.

 

[espltd]

Hi LB, looks like it worked but reversed the order of the numbers. For example the raw data is
10
9
9
8
to 1.
but the report puts out
000001-01
000002-01
000003-02
000003-01
to 10. Any suggestions? thanks.

 

[lbass]

I am unclear what you want to do here. I thought you wanted the extensions on the number to appear in descending order, since all your examples show that. I don't know if you now are saying you don't want them in descending order, or if you want both the original number and its extension in descending order. So please show what you want to see.

-LB

 

[espltd]

yes the extensions are in decending order and this is working as desired, but the actual number order (not the extension) seems reversed now.
for example raw data is
10
9
9
8
7
what is desired on display is
000010-01
000009-02
000009-01
000008-01
000007-01
what I am getting is
000007-01
000008-01
000009-02
000009-01
000010-01
note the number extensions are working correctly now (decending), just the order the numbers (not extensions)are listed seems ascending but would like them decending as well. Not sure what is causing the number order (not extension section) to be going from lowest to highest as the raw data is highest to lowest.
thanks again for the help.

 

[Turkbear]

Hi,
You can, maybe,create a formula for just the first 6 chars of @newfield and sort by that instead of the entire formula result.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[espltd]

I think the group sort order may be what is changing the order, will look into changing this.

 

[lbass]

This is because when you inserted the group, you didn't change the group order. Go to report->change group expert (or just group expert)->options->check "descending".

-LB

 

[espltd]

Hi thanks for the response. I went to group expert, options and selected decending but now it is grouping incorrectly, placing the largest group first. for example
group1
000007-04
000007-03
000007-02
000007-01
group2
000005-03
000005-02
000005-01
group3
000006-02
000006-01
-I want it to list group 7, then group 3 then group 2, decending by the 00000x number.

 

[espltd]

got it working now, thanks for all the help.

 

[lbass]

I think you mistakenly went to the group sort expert. Go into that and undo what you did. Then go to the group expert and change the order there.

-LB

 

[espltd]

Hi LB, yes that is what I did, looks like the group sort expert allows sorting by group count number. Thanks for the help.