If Then Else

[wanzek1]

I have a formula that groups GL Accounts with a certain title. It appears to be working for all categories except the first. I have tried placing the Training category elsewhere but it always skips this grouping. What am I do wrong:

if {PRGL.GLAcct}="58050.0000" or {PRGL.GLAcct}="58100.0000" or {PRGL.GLAcct}="58110.0000" or
{PRGL.GLAcct}="58120.0000" or {PRGL.GLAcct}="58500.0000" or {PRGL.GLAcct}="58505.0000" or
{PRGL.GLAcct}="58800.0000" or {PRGL.GLAcct}="58095.0000" or {PRGL.GLAcct}="58095.0000"

Then "Training"

else if ({PRGL.GLAcct}= "60200.0020" or {PRGL.GLAcct}="60400.0020" or {PRGL.GLAcct}="60450.0020" or {PRGL.GLAcct}="60480.0020" or
{PRGL.GLAcct}="62050.0020" or {PRGL.GLAcct}="63000.0020" or {PRGL.GLAcct}="63300.0020" or{PRGL.GLAcct}= "63400.0020"
or {PRGL.GLAcct}="65400.0020" or {PRGL.GLAcct}="65405.0020")

Then "Accounting"

else if ({PRGL.GLAcct}= "60200.0023" or {PRGL.GLAcct}= "60200.0123" or {PRGL.GLAcct}= "60400.0023" or
{PRGL.GLAcct}="60400.0123" or {PRGL.GLAcct}="60450.0023" or {PRGL.GLAcct}="60450.0123" or
{PRGL.GLAcct}="60480.0023" or {PRGL.GLAcct}="62050.0023" or {PRGL.GLAcct}="63000.0023" or
{PRGL.GLAcct}="63000.0123" or {PRGL.GLAcct}="63300.0023" or {PRGL.GLAcct}="63400.0023" or
{PRGL.GLAcct}="65400.0023" or {PRGL.GLAcct}="65405.0023")

Then "BD"

else if ({PRGL.GLAcct}="58800.0004" or {PRGL.GLAcct}="60100.0004" or {PRGL.GLAcct}="60110.0004" or
{PRGL.GLAcct}="60400.0004" or {PRGL.GLAcct}="60450.0004" or {PRGL.GLAcct}="60480.0004" or
{PRGL.GLAcct}="63000.0004" or {PRGL.GLAcct}="63300.0004" or {PRGL.GLAcct}="63400.0004" or
{PRGL.GLAcct}="65400.0004" or {PRGL.GLAcct}="65405.0004" or {PRGL.GLAcct}="62050.0004")

Then "Crane Service"

else if ({PRGL.GLAcct}="60200.0028" or {PRGL.GLAcct}="60400.0028" or {PRGL.GLAcct}="60450.0028" or
{PRGL.GLAcct}="63000.0028")

Then "Construction Support"

else if ({PRGL.GLAcct}="58800.0003" or {PRGL.GLAcct}="60100.0003" or {PRGL.GLAcct}="60400.0003" or
{PRGL.GLAcct}="60450.0003" or {PRGL.GLAcct}="63000.0003")

Then "EPC"

else if ({PRGL.GLAcct}="60050.0000" or {PRGL.GLAcct}="65405.0000" or {PRGL.GLAcct}="60400.0000" or
{PRGL.GLAcct}="60450.0000" or {PRGL.GLAcct}="63000.0000" or {PRGL.GLAcct}="63400.0000" or
{PRGL.GLAcct}="65400.0000" or {PRGL.GLAcct}="60300.0000")

Then "Office"

else if ({PRGL.GLAcct}="51500.0000" or {PRGL.GLAcct}="51500.0300" or {PRGL.GLAcct}="51600.0000" or
{PRGL.GLAcct}="51600.0300" or {PRGL.GLAcct}="51650.0000" or {PRGL.GLAcct}="51650.0300" or {PRGL.GLAcct}="51880.0000"
or {PRGL.GLAcct}="51880.0300" or {PRGL.GLAcct}="51900.0000" or {PRGL.GLAcct}="51900.0300")

Then "Job"

else if ({PRGL.GLAcct}="64150.0000" or {PRGL.GLAcct}="64152.0000" or {PRGL.GLAcct}="64153.0000" or
{PRGL.GLAcct}="64154.0000" or {PRGL.GLAcct}="64158.0000" or {PRGL.GLAcct}="64159.0000")

Then "Houston"

else if ({PRGL.GLAcct}="60200.0022" or {PRGL.GLAcct}="60400.0022" or {PRGL.GLAcct}="60450.0022" or
{PRGL.GLAcct}="60480.0022" or {PRGL.GLAcct}="62050.0022" or {PRGL.GLAcct}="63000.0022" or
{PRGL.GLAcct}="63300.0022" or {PRGL.GLAcct}="63400.0022" or {PRGL.GLAcct}="65400.0022" or
{PRGL.GLAcct}="65405.0022")

Then "HR"

else if ({PRGL.GLAcct}="59150.0000" or {PRGL.GLAcct}="59152.0000" or {PRGL.GLAcct}="59153.0000" or
{PRGL.GLAcct}="59154.0000" or {PRGL.GLAcct}="59156.0000" or {PRGL.GLAcct}="59158.0000" or
{PRGL.GLAcct}="59200.0000" or {PRGL.GLAcct}="58800.0001" or {PRGL.GLAcct}="60100.0001" or
{PRGL.GLAcct}="60100.0101" or {PRGL.GLAcct}="60100.0201" or {PRGL.GLAcct}="60105.0001" or
{PRGL.GLAcct}="60110.0001" or {PRGL.GLAcct}="60115.0001" or {PRGL.GLAcct}="60400.0001" or
{PRGL.GLAcct}="60400.0101" or {PRGL.GLAcct}="60400.0201" or {PRGL.GLAcct}="60450.0001" or
{PRGL.GLAcct}="60450.0101" or {PRGL.GLAcct}="60450.0201" or {PRGL.GLAcct}="60480.0001" or
{PRGL.GLAcct}="60505.0001" or {PRGL.GLAcct}="62050.0001" or {PRGL.GLAcct}="63000.0001" or
{PRGL.GLAcct}="63000.0101" or {PRGL.GLAcct}="63000.0201" or {PRGL.GLAcct}="63300.0001" or
{PRGL.GLAcct}="63400.0001" or {PRGL.GLAcct}="65400.0001" or {PRGL.GLAcct}="65400.0101" or
{PRGL.GLAcct}="65400.0201" or {PRGL.GLAcct}="65405.0001")

Then "IPOG"

else if ({PRGL.GLAcct}="60200.0021" or {PRGL.GLAcct}="60400.0021" or {PRGL.GLAcct}="60450.0021" or
{PRGL.GLAcct}="60480.0021" or {PRGL.GLAcct}="62050.0021" or {PRGL.GLAcct}="63000.0021" or
{PRGL.GLAcct}="63300.0021" or {PRGL.GLAcct}="63400.0021" or {PRGL.GLAcct}="65400.0021" or
{PRGL.GLAcct}="65405.0021")

Then "IT"

else if ({PRGL.GLAcct}="60200.0027" or {PRGL.GLAcct}="60400.0027" or {PRGL.GLAcct}="60450.0027" or
{PRGL.GLAcct}="60480.0027" or {PRGL.GLAcct}="62050.0027" or {PRGL.GLAcct}="63000.0027" or
{PRGL.GLAcct}="63300.0027" or {PRGL.GLAcct}="63400.0027" or {PRGL.GLAcct}="65400.0027" or
{PRGL.GLAcct}="65405.0027")

Then "Legal"

else if ({PRGL.GLAcct}="60200.0025" or {PRGL.GLAcct}="60400.0025" or {PRGL.GLAcct}="60450.0025" or
{PRGL.GLAcct}="60480.0025" or {PRGL.GLAcct}="62050.0025" or {PRGL.GLAcct}="63000.0025" or
{PRGL.GLAcct}="63300.0025" or {PRGL.GLAcct}="63400.0025" or {PRGL.GLAcct}="65400.0025" or
{PRGL.GLAcct}="65405.0025")

Then "Project Control"

else if ({PRGL.GLAcct}="60200.0026" or {PRGL.GLAcct}="60400.0026" or {PRGL.GLAcct}="60450.0026" or
{PRGL.GLAcct}="60480.0026" or {PRGL.GLAcct}="62050.0026" or {PRGL.GLAcct}="63000.0026" or
{PRGL.GLAcct}="63300.0026" or {PRGL.GLAcct}="63400.0026" or {PRGL.GLAcct}="65400.0026" or
{PRGL.GLAcct}="65405.0026")

Then "QA/QC"

else if ({PRGL.GLAcct}="60200.0024" or {PRGL.GLAcct}="60400.0024" or {PRGL.GLAcct}="60450.0024" or
{PRGL.GLAcct}="60480.0024" or {PRGL.GLAcct}="62050.0024" or {PRGL.GLAcct}="63000.0024" or
{PRGL.GLAcct}="63300.0024" or {PRGL.GLAcct}="63400.0024" or {PRGL.GLAcct}="65400.0024" or
{PRGL.GLAcct}="65405.0024")

Then "Safety"

else if ({PRGL.GLAcct}="60250.0000" or {PRGL.GLAcct}="53100.0000" or {PRGL.GLAcct}="53100.0100" or
{PRGL.GLAcct}="53100.0300" or {PRGL.GLAcct}="53110.0000" or {PRGL.GLAcct}="53120.0000" or
{PRGL.GLAcct}="53130.0000" or {PRGL.GLAcct}="53140.0000" or {PRGL.GLAcct}="53150.0000" or
{PRGL.GLAcct}="53160.0000" or {PRGL.GLAcct}="53170.0000" or {PRGL.GLAcct}="53200.0000" or
{PRGL.GLAcct}="53200.0100" or {PRGL.GLAcct}="53200.0300" or {PRGL.GLAcct}="53250.0000" or
{PRGL.GLAcct}="53250.0100" or {PRGL.GLAcct}="53250.0300" or {PRGL.GLAcct}="53280.0000" or
{PRGL.GLAcct}="53280.0100" or {PRGL.GLAcct}="53280.0300" or {PRGL.GLAcct}="53350.0000" or
{PRGL.GLAcct}="53400.0000" or {PRGL.GLAcct}="53450.0000" or {PRGL.GLAcct}="53450.0100" or
{PRGL.GLAcct}="53450.0300" or {PRGL.GLAcct}="53455.0000" or {PRGL.GLAcct}="53455.0100" or
{PRGL.GLAcct}="53455.0300" or {PRGL.GLAcct}="54350.0000" or {PRGL.GLAcct}="54800.0000" or
{PRGL.GLAcct}="54800.0100" or {PRGL.GLAcct}="54800.0300" or {PRGL.GLAcct}="55100.0000" or
{PRGL.GLAcct}="55100.0300" or {PRGL.GLAcct}="55105.0000" or {PRGL.GLAcct}="55105.0300" or
{PRGL.GLAcct}="55110.0000" or {PRGL.GLAcct}="55115.0000" or {PRGL.GLAcct}="55115.0300" or
{PRGL.GLAcct}="55118.0000" or {PRGL.GLAcct}="55118.0300" or {PRGL.GLAcct}="55120.0000")

Then "Shop"

else if ({PRGL.GLAcct}="60100.0300" or {PRGL.GLAcct}="60110.0300" or {PRGL.GLAcct}="60200.0300" or
{PRGL.GLAcct}="60400.0300" or {PRGL.GLAcct}="60450.0300" or {PRGL.GLAcct}="60480.0300" or
{PRGL.GLAcct}="60505.0300" or {PRGL.GLAcct}="62050.0300" or {PRGL.GLAcct}="63000.0300" or
{PRGL.GLAcct}="63300.0300" or {PRGL.GLAcct}="63400.0305" or {PRGL.GLAcct}="64300.0300" or
{PRGL.GLAcct}="65400.0300" or {PRGL.GLAcct}="65405.0300")

Then "Solar"

else if {PRGL.GLAcct}="58050.0000"
then "Test"

else if ({PRGL.GLAcct}="58800.0002" or {PRGL.GLAcct}="60100.0002" or {PRGL.GLAcct}="60105.0002" or
{PRGL.GLAcct}="60110.0002" or {PRGL.GLAcct}="60115.0002" or {PRGL.GLAcct}="60400.0002" or
{PRGL.GLAcct}="60450.0002" or {PRGL.GLAcct}="60480.0002" or {PRGL.GLAcct}="60505.0002" or
{PRGL.GLAcct}="62050.0002" or {PRGL.GLAcct}="63000.0002" or {PRGL.GLAcct}="63300.0002" or
{PRGL.GLAcct}="63400.0002" or {PRGL.GLAcct}="65400.0002" or {PRGL.GLAcct}="65405.0002")

Then "Wind"

else if {PRGL.GLAcct}="25500." then "PTO"

else if {PRGL.GLAcct}="25550." then "Holiday"

else {PRGL.GLAcct}

 

[MCuthill]

wanzek1,

Not sure if the cause, but I noticed that the "Training" group appears to be the only set of [blue]OR[/blue] clauses missing the parathesis... perhaps something to check quick to ensure this is not the cause of the issue you are having.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[wanzek1]

Thanks. I tried that but it didn't help!

 

[AndyMc]

This doesn't answer your question, but for readability and trouble-shooting (gets rid of parens and or's ...)

if {PRGL.GLAcct} in ["58050.0000","58100.0000","58110.0000","58120.0000","58500.0000","58505.0000","58800.0000",
"58095.0000","58095.0000"] then
"Training"
else if {PRGL.GLAcct} in ["60200.0020","60400.0020","60450.0020","60480.0020","62050.0020","63000.0020","63300.0020",
"63400.0020","65400.0020","65405.0020"] then
"Accounting"
else if {PRGL.GLAcct} in ["60200.0023","60200.0123","60400.0023","60400.0123","60450.0023","60450.0123","60480.0023",
"62050.0023","63000.0023","63000.0123","63300.0023","63400.0023","65400.0023","65405.0023"] then
"BD"
...

 

[CR85user]

With any report of this type validate your results by turning the report on its head temporarily and selecting only those records which ought match your spec for the training category.

In other words either amend your selection criteria to:

{PRGL.GLAcct} in ["58050.0000",58100.0000","58110.0000","58120.0000","58500.0000","58505.0000","58800.0000","58095.0000","58095.0000"]

Or if you prefer to leave your selection criteria intact then perhaps amend the display properties of an object within the report to highlight when it matches your criteria for error checking purposes, for example amend the font colour property of an entry to read:

if {PRGL.GLAcct} in ["58050.0000",58100.0000","58110.0000","58120.0000","58500.0000","58505.0000","58800.0000","58095.0000","58095.0000"] then crred else crblack

Either route will help you quickly and visually confirm if such entries even exist within the records.



CR8.5 / CRXI - Discovering the impossible

 

[pmax9999]

What do you mean by "... it always skips this grouping ..."?

Are you saying that every {PRGL.GLAcct} that should be grouped into Training is showing up under its Account code, or are you saying it simply isn't appearing (under Training or its Account codes)? If the former, the only thing I can think of would be to check the data for leading or trailing spaces. If the latter, the then there isn't any data for those account codes.


Cheers
Pete