Formula Syntax Issues

[Mav3000]

Hi, for the life of me I have no idea why the formula below isn't working. I have 11 'Stages'. Each one on its own in its own formula does what I need - flag '1' if the date is over 1990.

However, when I combine them into a single formula, the formula returns zero. Not all dates will have values. I have CR (10) set to return default values for null fields.

I think there's a formatting issue somewhere, but can't spot it. Can anyone help me?

The formula:

// Stage 01
local numbervar Stage01;
if {@Current WIP S01} > 0 and {dtblDMA0001Jobs.D_S1_Ready_To_Schedule} > date(1990,01,01) then Stage01:= 1;

// Stage 02
local numbervar Stage02;
if {@Current WIP S02} > 0 and {dtblDMA0001Jobs.D_S2_Ready_To_Schedule} > date(1990,01,01) then Stage02:= 1;

// Stage 03
local numbervar Stage03;
if {@Current WIP S01} > 0 and {dtblDMA0001Jobs.D_S3_Ready_To_Schedule} > date(1990,01,01) then Stage03:= 1;

// Stage 04
local numbervar Stage04;
if {@Current WIP S04} > 0 and {dtblDMA0001Jobs.D_S4_Ready_To_Schedule} > date(1990,01,01) then Stage04:= 1;

// Stage 05
local numbervar Stage05;
if {@Current WIP S05} > 0 and {dtblDMA0001Jobs.D_S5_Ready_To_Schedule} > date(1990,01,01) then Stage05:= 1;

// Stage 06
local numbervar Stage06;
if {@Current WIP S06} > 0 and {dtblDMA0001Jobs.D_S6_Ready_To_Schedule} > date(1990,01,01) then Stage06:= 1;

// Stage 07
local numbervar Stage07;
if {@Current WIP S07} > 0 and {dtblDMA0001Jobs.D_S7_Ready_To_Schedule} > date(1990,01,01) then Stage07:= 1;

// Stage 08
local numbervar Stage08;
if {@Current WIP S08} > 0 and {dtblDMA0001Jobs.D_S8_Ready_To_Schedule} > date(1990,01,01) then Stage08:= 1;

// Stage 09
local numbervar Stage09;
if {@Current WIP S09} > 0 and {dtblDMA0001Jobs.D_S9_Ready_To_Schedule} > date(1990,01,01) then Stage09:= 1;

// Stage 10
local numbervar Stage10;
if {@Current WIP S10} > 0 and {dtblDMA0001Jobs.D_S10_Ready_To_Schedule} > date(1990,01,01) then Stage10:= 1;

// Stage 11
local numbervar Stage11;
if {@Current WIP S11} > 0 and {dtblDMA0001Jobs.D_S11_Ready_To_Schedule} > date(1990,01,01) then Stage11:= 1;

// Add Them All Up
Stage01 + Stage02 + Stage03 + Stage04 + Stage05 + Stage06 + Stage07 + Stage08 + Stage09 + Stage10 + Stage11;

 

[CoSpringsGuy]

Does the field that is used to create the formula {@Current WIP S##} always contain a value or could it sometimes be null? Same question for {dtblDMA0001Jobs.D_S10_Ready_To_Schedule}

By the way, I noticed in stage 3 you used {@Current WIP S01} instead of {@Current WIP S03}. I assume it was a copy and paste issue..

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[Mav3000]

Hi CoSpringsGuy,

The formula {@Current WIP S##} evaluates to '1' if certain criteria are met, or '0' (else 0) if they are not met. This therefore is either a 1 or a zero, and does work as required.

The fields {dtblDMA0001Jobs.D_S##_Ready_To_Schedule} can sometimes be null.

The error in Stage 03 is indeed a typo!

Could the null field be the issue here?

 

[Madawc]

You're using variables as if they were formula fields. Your method would have worked if the formula field itself had a value, something like

if {@Current WIP S01} > 0 and {dtblDMA0001Jobs.D_S1_Ready_To_Schedule} > date(1990,01,01) then 1 else 0

You could change each of the 11 that way and it should work. Or else pick up the eleven variables in your total formula and add them into a grand total.

It would also have been simpler from the beginning if you'd done them as running totals. (If you're not already familiar with Crystal's automated totals, see FAQ767-6524.) In that case you could have put the test itself in a formula field, as a boolian, something like

{@Current WIP S01} > 0 and {dtblDMA0001Jobs.D_S1_Ready_To_Schedule} > date(1990,01,01)

This would return 'True' or 'False'. You could then reference the boolian in formula of the running total.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[Mav3000]

Hi Madawc,

Thanks for your input. I have not used running totals because this formula is to be used at the detail level. As I underatand it, Running totals are used in GH and RH levels.

I don't quite understand what you mean about using variables as if they were formula fields. Is this in relation to the last line where all of the variables are added up?

 

[Madawc]

You have given the formula field and its variable the same name: they could equally well be different. You then refer to that name, but I'm pretty sure it is looking at the formula field, which has no value in it.

Running totals need to be in footers to have the correct values. Check out the FAQ I mentioned in my last post.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[lbass]

You need to change each formula to check for nulls:

local numbervar Stage07;
if isnull({dtblDMA0001Jobs.D_S7_Ready_To_Schedule}) then
stage07 := 0 else
if {@Current WIP S07} > 0 and
{dtblDMA0001Jobs.D_S7_Ready_To_Schedule} > date(1990,01,01) then Stage07:= 1;

Then you should be able to add all formulas together.

-LB

 

[Mav3000]

Excellent - thank you very much LB!