In InfoAssist+, the Filters dialog box has an option, "New Expression" next to the option to create a new filter. I have tried to input an if-then statement under that option but an error comes up saying it doesn't recognize the word, "THEN". How can this be solved?

This message has been edited. Last edited by: ecsuappdevstu2,

Give us what you wrote, please.

When adding a post, you should always try to provide the code where you can so we can help you better. When creating the post, there are buttons above where you type out the post. Choose the

</>

button at the very end and put your code you're sharing therein for us.

Thanks!

This message has been edited. Last edited by: CoolGuy,

IF &COAS EQ '1' THEN FGBOPAL.FGBOPAL.FGBOPAL_FUND_CODE EQ '916209' ELSE IF &COAS EQ '5' THEN FGBOPAL.FGBOPAL.FGBOPAL_FUND_CODE EQ 'X94000' 

My apologies. The code above is what I wrote that prompted the error message.

So, you need to walk through that statement in your head I think. You've got incomplete thoughts going on there.

It should read as follows:

IF &value EQ 1 THEN resultValue ELSE IF &value EQ 5 THEN resultValue;

Since you are actually trying to decode something, I'd use a DECODE statement instead/in conjunction with the DEFINE you're trying to write. Look that up in the docs to see how it works.

In WebFOCUS EQ is used for comparison not to populate values. I'm not sure what you're trying to create but you ought to take a look at IF THEN ELSE syntax in the manual under DEFINE to get a feel for what that should look like.

https://webfocusinfocenter.inf...ang/source/exp89.htm

What I'm trying to do is have one set of fund codes appear on a report if the &COAS (chart of accounts) is 1 and a different set of fund codes appear if the &COAS is 5. I think this may be a better example.

 WHERE IF &COAS EQ '1' THEN  FGBOPAL.FGBOPAL.FGBOPAL_FUND_CODE EQ '$$1$$$' ELSE IF FGBOPAL.FGBOPAL.FGBOPAL_FUND_CODE EQ '$$2$$$' ELSE 'NA'

The main question I'm asking is: can if-then statements be coded in the "New Expression" option?

The expression in Filter will always have a WHERE appended to it. From what you're saying, you really need to define a new field based on the logic you're showing.

COA/A2=IF &COAS EQ '1' THEN EDIT(FGBOPAL.FGBOPAL.FGBOPAL_FUND_CODE,'$$1$$$') ELSE 
       IF &COAS EQ '5' THEN EDIT(FGBOPAL.FGBOPAL.FGBOPAL_FUND_CODE,'$$2$$$') ELSE 'NA'

  

This message has been edited. Last edited by: BabakNYC,

Maybe use something like the following:

WHERE FGBOPAL_FUND_CODE LIKE '$$1$$$';

OR

WHERE FGBOPAL_FUND_CODE LIKE '$$2$$$';

See here for more info on LIKE and CONTAINS.

CoolGuy: The problem is they have to check for 1 or 5 in &COAS to determine what they want to filter on.

Yeah, didn't add that in. My bad. Kind of tricky, eh? lol Where's Francis or Waz when you need em?

Also, BabakNYC, doesn't your solution replace all existing values to contain either a 1 or 2 in them? Not merely select those specific values from the records? He wants to show existing values not modify existing values.

This message has been edited. Last edited by: CoolGuy,

In my example, I’m trying to show the difference between EQ and = as well as how you use IF THEN ELSE.

 WHERE ( FGBOPAL.FGBOPAL.FGBOPAL_COAS_CODE EQ '1' AND FGBOPAL.FGBOPAL.FGBOPAL_FUND_CODE EQ '111000') OR ( FGBOPAL.FGBOPAL.FGBOPAL_COAS_CODE EQ '5' AND FGBOPAL.FGBOPAL.FGBOPAL_FUND_CODE EQ 'X12000'); 

I tried this code above and it worked.

Sweet man! Glad you got it working.