record selection based on criteria

[Lhuffst]

I have 4 tables. 3 of which relate. The 4th is a table of default comments. I am using crxi with oracle tables.

The main table has idnum, subnum, commentflag...
The usercomments are in a separate table but linked to the main table. The default comments are not linked at all.

I need to print the following on my report.
IdNum, SubNum, Comments

If an idNum doesn't have any usercomments (commentflag isn't checked), then I am supposed to print standard comments but only the ones with an id of 999

I am trying to figure out how to write a formula that says if the commentflag is check, print the usercomments from the usercomment table else if the comment flag is not checked then print only the default comment with an id of 999.

I created a text box and have this formula.

If {PARKNPLAN.COMMENT_FLAG} = 'y' then
    {PARKNPLAN.COMMENTS}
else if {PARKNPLAN.COMMENT_FLAG} = 'n' and {COMMENTREF.ID} = 999 then
        {COMMENTREF.DESCRIPTION}

I think I'm close but the problem is where I have and {COMMENTREF.ID} = 999

What I need to say is if {PARKNPLAN.COMMENT_FLAG} = 'n' then print only the comment for {COMMENTREF.ID} = 999

Any ideas?
Thanks
Lhuffst

 

[lbass]

Your formula looks correct. What happens when you try it? I think it is odd not to link the table at all, and this will slow your report. Is there no way to link it to the usercomments table?

-LB

 

[Lhuffst]

LB
When it runs, I don't get any errors, I just do get the description that goes with the 999. It shows as a blank. My personal belief is that if the user doesn't add comments then the 999 should be listed in the main table. This would link the tables.

I wanted to try it this way first to see if I could get it working. I plan on talking to the coders on Monday. Thanks for looking.
Lhuffst

 

[Lhuffst]

Since the tables were not linked and I had blanks from my above code, I ended up doing the following.

On the user comment field, I added a supress formula that if the comment flag was N supress this field.

I added the default comment field on top of this with the supress formula of supressing if the comment flag was Y.

I did pose the question of linking the tables but this is a workaround for now.
Thanks LB
Lhuffst