Formula Worksheet syntax

[Former Member]

I am working on a formula and am having some trouble with the syntax, and wondered if anyone might have any suggestions.

Here is a little background:

I have a client who wants a particular page(s) to print with the report if the entry for that report meets the a set of criteria. To do this I have placed the pages in the Report Footer and set up a formula that evaluates the criteria and suppresses the Report Footer if the entry does not meet them.

Criteria:

1. Must have a TypeField (varchar) of "A"

2. Must have a SubTypeField(varchar) of "B" or "C"

3. Must have a ClassField(varchar) of "D" or "E" and the YearField(varchar) must be <= the current year (2010) minus 50 years.

Here is my code:

// setting the year and then converting the number to a string

numberVar x := (YEAR (CURRENTDATE) - 50);

stringVar y := ToText (x);

// setting up the criteria

// the idea is that if the entry does not meet the following criteria (hence NOT),

// the page(s) in the Report Footer are suppressed

NOT

(

( {dbTable.TypeField} = "A" AND ( {dbTable.SubTypeField} = "B" OR {dbTable.SubTypeField} = "C" ) )

AND

( {dbTable.ClassField} startswith "D" AND {dbTable.YearField} <= y ) OR

( {dbTable.ClassField} startswith "E" AND {dbTable.YearField} <= y )

)

I think the issue lays in the YEAR(CURRENTDATE) process(?)

Any suggestions are welcomed.

Thanks!

[Former Member · Accepted solution]

I don't think that is the problem. I suggest the following:

// setting the year and then converting the number to a string
numberVar x := (YEAR (CURRENTDATE) - 50);

// setting up the criteria
// the idea is that if the entry does not meet the following criteria (hence NOT),
// the page(s) in the Report Footer are suppressed 
NOT (
     ({dbTable.TypeField} = "A" AND (({dbTable.SubTypeField} = "B") OR ({dbTable.SubTypeField} = "C" )))
     AND ((({dbTable.ClassField} startswith "D") OR ({dbTable.ClassField} startswith "E"))
                AND (tonumber({dbTable.YearField}) <= y)
     )
)

Fuskie

Who thinks it would be easier to compare year as a numberic value and rearranged the parentheses to make them more explicit...

[Former Member · Accepted solution]

Fuskie,

Thanks for the suggestion. I took your advice on applying the tonumber conversion to the field, and making my parenthesis more explicit. Everything seems to be working now. Here is my final code- feel free to critique if you see more deficiencies.

// this includes the full criteria which basically just adds a "when ClassField starts with D, YearField is compared to YEAR(CURRENTDATE) - 50"

// and "when ClassField starts with E, F or G; YearField is compared to YEAR(CURRENTDATE) - 25".

// set the numberic year values

numberVar x := (YEAR (CURRENTDATE) - 50);

numberVar y := (YEAR (CURRENTDATE) - 25);

// set up the criteria

NOT

(

( {db.TypeField} = "A" AND ( ({db.SubTypeField} = "B") OR ({db.SubTypeField} = "C") ) )

AND

(

( ({db.ClassField} startswith "D") AND (tonumber({db.YearField}) <= x) ) OR

( ({db.ClassField} startswith "E") AND (tonumber({db.YearField}) <= y) ) OR

( ({db.ClassField} startswith "F") AND (tonumber({db.YearField}) <= y) ) OR

( ({db.ClassField} startswith "G") AND (tonumber({db.YearField}) <= y) )

)

)