Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Is there a formula to stop formula when the page turns?

Status
Not open for further replies.

kg2report

IS-IT--Management
Apr 5, 2018
25
US
Hi.

I was wondering if there is a way I can force the formula to not function for the next page or previous page. Any help would be greatly appreciated.


IF PageNumber > 0 then
Left ({table1_by_row.name_last}, 1)


Thanks.
kg
 
Please see your other post where I responded with the below formula. Not sure if you have changed your desired result.

whileprintingrecords;
stringvar x;
stringvar y := x;
numbervar rpt;
x := {Employee.Last Name}[1];
if
x=y then
rpt := rpt+1;
if
pagenumber = 1 then
x else
replicatestring(chr(9), 6*(pagenumber-rpt))+x

The above formula placed in the page footer would show the same letter on repeating pages but not tabbed over while new letters would be tabbed over.

-LB
 
Hi LB. Thanks for responding. This is great. However, I was wondering now if there is a way the letter would tab over to the right and change to reflect the first letter of the user's last name as each page turns...? Thanks so much for your help on this.

kg
 
How is what I suggested different from what you are looking for? Or did you mean you wanted the letter to appear at the right margin of the page footer?

-LB
 
I need it so when the letter changes (because the page number changed) even if it's the same letter, the next one would still tab over. For example, if page 2 is "A" and page 3 still has an "A", the second A would tab over to the right because the page changed. The tabs are going to be located at the bottom of a page with landscape layout.

Thanks again.

kg
 
whileprintingrecords;
stringvar x;
x := {Employee.Last Name}[1];

if pagenumber = 1 then
x else
replicatestring(chr(9), 6*pagenumber)+x

-LB
 
Thanks- this helps a lot!!

Question: if I want to change the number of spaces the letter tabs over to the right to two spaces, do I just change the 9 in the formula to 2? Or would I change the 6 to a 2?


kg
 
Hi LB.

So I changed the formula to the following... I need one more help, please if you could. How can I force the letter to start at the very beginning of the line once the space allotted for the field has been used?
The letter goes to the next line. I had formatted the field to have Exact line spacing instead of multiple, and the letters still bleeds to the next line. Thanks for your patience and help.


whileprintingrecords;
stringvar x;
x := {Employee.Last Name}[1];

if pagenumber = 1 then
x else
replicatestring(chr(32), 3*pagenumber)+x


kg
 
Create a {@reset} formula and place it in the page header. Replace the number 31 with the pagenumber you see when the second formula is just about to wrap to the next line.

//{@reset} for page header:
whileprintingrecords;
stringvar p;
numbervar i;
if remainder(i,31)=0 then(
i := 0;
p := "";
);

//{@initial} for page footer:
whileprintingrecords;
stringvar x;
numbervar i := i + 1;
stringvar p;

x := {Employee.Last Name}[1];
if i = 1 then
p := x else
p := replicatestring(chr(9),i-1)+x;
p

Note that chr(9) is the ANSI character for tab and cannot be replaced.

-LB
 
Hi LB.

Just to confirm and to make sure i did it correctly. I created a formula field named "reset" and put the formula below that you provided in the field's Formula Editor. Then I dropped that "reset" field any where in the Page header.

whileprintingrecords;
stringvar p;
numbervar i;
if remainder(i,31)=0 then(
i := 0;
p := "";
);

THEN

I created another formula field named "initial" and put the following formula below that you provided in the field's Formula Editor. That field is placed in the Page Footer.

whileprintingrecords;
stringvar x;
numbervar i := i + 1;
stringvar p;

x := {Employee.Last Name}[1];
if i = 1 then
p := x else
p := replicatestring(chr(9),i-1)+x;
p

Is what I did correct because it did not work for me and the letter in "initial" field is no longer tabbing to the right as the page changes.


Thanks again.

kg
 
Did you remove previous formulas? I did test this and it worked perfectly for me.

Did you verify the point at which the formula tries to wrap in the page footer to see whether 31 needs to be replaced by some other pagenumber in the reset formula?

-LB
 
I did delete previous formulas. I created the fields again.
How do I verify the point at which the formula tries to wrap in the page footer? I tried changing the 31 to the smallest number possible and up to 3000, and no luck. When I click the next page arrow, the letter changes to reflect the first letter of the user's last name, but no longer tabs or move to the right.


kg
 
Turn pages and watch the letters move to the right and then when it wraps go back to the page just before it wrapped and observe the pagenumber.

-LB
 
Also make sure the reset formula is in the page header and not the report header.

-LB
 
The letters no longer move to the right. Do you know why it could be? Even the other formula you provided, the letters do not move to the right anymore. I've triple checked the formula, and they are correct.

I've also made sure the reset formula is in the page header and not the report header.


kg.
 
Also, the report is grouped by user's zip code- would that affect the formula and deter it from working properly? I've tried lots of times, but still not able to get it to worked...


kg.
 
Did you change the 31 based on the pagenumber when the formula would wrap?

Please show the current formulas you are using.

-LB
 
Below is the formula I used. The letters do not move at all to the right even if the page number changes... so the field doesn't even wrap. I did try changing the 31 from 1 to 300, and it still didn't do anything.

Formula entered in field "reset" (placed in page header):

whileprintingrecords;
stringvar p;
numbervar i;
if remainder(i,1)=0 then(
i := 0;
p := "";
);

Formula entered in field "initial" (placed in page footer):

whileprintingrecords;
stringvar x;
numbervar i := i + 1;
stringvar p;

x := {User.name_last}[1];
if i = 1 then
p := x else
p := replicatestring(chr(9),i-1)+x;
p



kg.
 
The reset formula is the problem. You need to replace the “1” in the remainder function with the pagenumber just before the formula wraps in the page footer. Place the pagenumber function in the page footer. Start out with some number like 50 in the reset formula, so that it won’t wrap before the initials reach the right margin. Then page through the report until the next page causes the formula to wrap. Then go back one page and check the pagenumber. It will probably be something like 31 (depends upon your font, of course). Plug that number into the reset formula so that the remainder function looks like this:

If Remainder(i,yourpagenumberbeforewrapping)=0 then (

-LB
 
Hi LB. Just want to give you an update. It finally worked! I had to fudge with the size of the field and font formats, but it works. Thanks so much for your help on this!!! Very much appreciated for your help and patience.


kg.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top