Prevent Formula for Executing

[jannoth]

To easy to explain this, sorry - so I'll explain using a short outline of my report structure:

GH1 - ManagerID (suppressed)
GH2 - StaffType
GH3 - StaffID (suppressed)
DETAILS (suppressed)
GF3 - summaries per StaffID (eg No of qualifications passed)
GF2 (suppressed)
GF1 - summaries per ManagerID

GF3 contains 8 running totals (one per "Qualification") and 2 key formulas:
Formula1 examines the 8 running totals per StaffID and their values (1= passed that Qualification)
(ie count how Qualifications were passed by that member of staff).

Formula2 examines Formula1 and counts if value is <12
(ie how many staff have yet to complete all 12 qualifications)

The problem
===========
The running totals and two formulas in GF3 work fine, but I need only the summaries at manager level (ie GF1). But, when Formula2 is also placed inside GF1 it is sometimes incremented by 1 (depending on value of Formula1 inlast record). So, the same formula is displaying differing values between GF3 and GF1 (

Here's Formula2:

WhilePrintingRecords;
NumberVar TutorsWithAllMoodleModules;
If Formula1 <12 Then TutorsWithAllMoodleModules := TutorsWithAllMoodleModules + 1;
TutorsWithAllMoodleModules;

Sorry it's so long winded, I couldn't think of a way to summarise and still give you a chance to help. I've tried lots of things already but your suggestions are bound to work. You've always managed to help me before, so my fingers are crossed - with thanks in advance.

J

 

[fisheromacse]

create a new formula placed in the Group Footer and call it something like GF1Formula2Display, and have it reference Formula2...for the contents of the new formula, put the name of the formula it is displaying.

 

[jannoth]

Thanks for such a speedy response, but that was one of the things I'd previously tried to no avail (

New formula called DISPLAY:

Version 1
=========
@Formula2 (ie the name of Formula2)

Version2
========
Whileprintingrecords;
NumberVar TutorsWithAllMoodleModules (ie the variable name inside Formula2).

In both cases, the value was incremented by 1 again

 

[lbass]

You have to remove all other versions of the display formula from the GF1. You should be using Version2 only in GF1. You also need a reset formula in GH1:

Whileprintingrecords;
NumberVar TutorsWithAllMoodleModules;
if not inrepeatedgroupheader then
TutorsWithAllMoodleModules := 0;

-LB

 

[jannoth]

Hi, thanks for joining in the "fun" LB

Sorry for confusion, but the two versions I referred to previously weren't used concurrently - they were examples of what I'd tried at separate times. I'm sticking with version 2 now (as you also suggest). Also, when I previously referred to 8 qualifications/running totals, it should have read 12.

As for the reset idea, I already have such a formula in GH1 - which is reseting the variable fine.

Everything works fine except for the display variable in GF1 occasionally differing from the counter in Formula2, because it sometimes is greater by just 1.

BTW, I've identified a pattern... If the final record in GF3 is counted (ie Formula1 <12) then the value of the display variable in GF1 will be GF3 + 1. However, if Formula1 =12, then Formula2 in GF3 isn't incremented (correct) and the display variable in GF1 is also correct.

Does that tell you anything?? It just confuses me more haha or %-)

Thanks again - looking forward to more wisdom!

 

[lbass]

Please show the ACTUAL content of formula1 and formula2, not descriptions.

-LB

 

[jannoth]

Reset (in GH1):
WhilePrintingRecords;
NumberVar TutorsWithAllMoodleModules := 0;

Formula1 (in GF3) - real name is "@Cnt Moodle Modules Started Per Tutor". It counts running totals if their value=1 (ie a Qualification is taken):
{#Cnt M01 Per Tutor} + {#Cnt M02 Per Tutor} + {#Cnt M03 Per Tutor} + {#Cnt M04 Per Tutor} + {#Cnt M05 Per Tutor} + {#Cnt M06 Per Tutor} + {#Cnt M07 Per Tutor} + {#Cnt M08 Per Tutor} + {#Cnt M09 Per Tutor} + {#Cnt M10 Per Tutor} + {#Cnt M11 Per Tutor} + {#Cnt M12 Per Tutor}

Formula2 (in GF3) - counts instances where Staff haven't taken all Qualifications ie Formula1 <12):
WhilePrintingRecords;
NumberVar TutorsWithAllMoodleModules;
If {@Cnt Moodle Modules Started Per Tutor} <12 Then TutorsWithAllMoodleModules := TutorsWithAllMoodleModules + 1;
TutorsWithAllMoodleModules;

Display (in GF1):
Whileprintingrecords;
NumberVar TutorsWithAllMoodleModules;

J

 

[lbass]

The problem is that you are referencing a formula (@Cnt Moodle Modules Started Per Tutor} that accumulates values in a second formula--so that the running totals accumulate one more instance; instead you should be referencing a variable from Formula1 which maintains its value.

Set up {@Cnt Moodle Modules Started Per Tutor} like this:

whileprintingrecords;
numbervar x := {#Cnt M01 Per Tutor} + {#Cnt M02 Per Tutor} + {#Cnt M03 Per Tutor} + {#Cnt M04 Per Tutor} + {#Cnt M05 Per Tutor} + {#Cnt M06 Per Tutor} + {#Cnt M07 Per Tutor} + {#Cnt M08 Per Tutor} + {#Cnt M09 Per Tutor} + {#Cnt M10 Per Tutor} + {#Cnt M11 Per Tutor} + {#Cnt M12 Per Tutor};

Then in your second formula, use:

Evaluateafter {@Cnt Moodle Modules Started Per Tutor};
WhilePrintingRecords;
NumberVar TutorsWithAllMoodleModules;
numbervar x;
If x < 12 Then
TutorsWithAllMoodleModules := TutorsWithAllMoodleModules + 1;

-LB

 

[jannoth]

I've never tried that before and we are getting close (ie you are!!).

The problem now is that the second formula is (reworded as you suggested) is counting every GF3 record, regardless of the value in {@Cnt Moodle Modules Started Per Tutor}.

What do you suggest?

 

[lbass]

Please chk the value of x in the group footer #3 by adding "x":

Evaluateafter {@Cnt Moodle Modules Started Per Tutor};
WhilePrintingRecords;
NumberVar TutorsWithAllMoodleModules;
numbervar x;
If x < 12 Then
TutorsWithAllMoodleModules := TutorsWithAllMoodleModules + 1;
x

Then report back with sample results.

-LB

 

[jannoth]

Hi LB, it's a new day so I followed your instructions again ... slowly and exactly :

FORMULA1 (just as you suggested):
=================================
whileprintingrecords;
numbervar x := {#Cnt M01 Per Tutor} + {#Cnt M02 Per Tutor} + {#Cnt M03 Per Tutor} + {#Cnt M04 Per Tutor} + {#Cnt M05 Per Tutor} + {#Cnt M06 Per Tutor} + {#Cnt M07 Per Tutor} + {#Cnt M08 Per Tutor} + {#Cnt M09 Per Tutor} + {#Cnt M10 Per Tutor} + {#Cnt M11 Per Tutor} + {#Cnt M12 Per Tutor};

Formula2 (as you 1st suggested but with brackets added after Evaluateafter function)
==============================
Evaluateafter ({@Cnt Moodle Modules Started Per Tutor});
WhilePrintingRecords;
NumberVar TutorsWithAllMoodleModules;
numbervar x;
If x < 12 Then
TutorsWithAllMoodleModules := TutorsWithAllMoodleModules + 1;

Display
========
Whileprintingrecords;
NumberVar TutorsWithAllMoodleModules;

Results
=======
Pls ignore my last reponse yesterday (my typing caused error). Error was as originally ... Display formula was greater than Formula2 by 1.

Adding "x" this morning just broke Formula2 (I can't work out what it was counting).

So, Formula1 seems OK, Formula2 (without final "x") seems OK, and Display seems OK but still sometimes disagrees with Formula2.

Does my summary make sense?

J

 

[lbass]

Please make sure that the only formula in GF#1 is the display formula--no old copies of other formulas you tried.

-LB

 

[jannoth]

WOW!!

How did you know there were any LOL??

I removed a percentage formula and suddenly it all added up.

Thank you so, so, much LB.

J