Filtering with Formulas

[JANELLEB]

I'm really not sure how to approach this, so any helpful tips would be great. I created the following formula in formula editor:

IF {INDV_DEMO.MAIN_CSREP}=true then {Name.FULL_NAME} else
if {INDV_DEMO.MAIN_REP}= true then {Name.FULL_NAME} else " "

I am trying to pull the MAIN_CSREP if there is one, but if not then I want the MAIN_REP. Every company has a MAIN_REP. When I insert the formula above in the details section, I get both rep names for companies that have both. I only want one name. Any ideas?

 

[Roadkill150]

Hello,
Here is a quick workaround to this issue.. place a counter in the detail section, supress the counter so you don't see it and then conditionally suppress your detail section if counter>1 Seagate Certified RCAD Specialist.
-Bruce Thuel-Chassaigne
roadkill150@hotmail.com

 

[Ngolem]

I think you need to describe your problem a bit better.

Are INDV_DEMO.MAIN_CSREP and INDV_DEMO.MAIN_REP boolean values in themselves? you are implying as such in the formula.

In both cases you are drawing in the field {Name.FULL_NAME} ... How do you know which it is? Main_CSREP or MAIN_REP??

How is INDV_DEMO table linked to the Name table?

Have you grouped your records by INDV_DEMO.MAIN_REP first then INDV_DEMO.MAIN_CSREP ?

Please answer these questions because by itself this formula doesn't mean much to me

IF {INDV_DEMO.MAIN_CSREP}=true then {Name.FULL_NAME} else
if {INDV_DEMO.MAIN_REP}= true then {Name.FULL_NAME} else " "

Jim

JimBroadbent@Hotmail.com

 

[JANELLEB]

Thanks for the inquiry, Jim. INDV_DEMO.MAIN_CSREP and INDV_DEMO.MAIN_REP are boolean expressions. They are represented by checkboxes in my database software. INV_DEMO is a table that is connected to the NAME table by ID number.

Basically, here's how we are set up. We have company records that hold all a company's info, including Accounts Receivable. I am trying to pull the companies that have been invoiced. That is not a problem. The problem comes in when I need to add a name to send the invoice to. All the individuals associated with the company are connected through their id numbers back to that main company record. I would like the INDV_DEMO.MAIN_CSREP to get the invoice, however, if there is not one I would like it to go to the INDV_DEMO.MAIN_REP. When I insert the above referenced formula field, I just get both reps for companies that have both.

I insert the name by means of a subreport. I have not tried to group by INDV_DEMO.MAIN_CSREP then by INDV_DEMO.MAIN_REP because I thought that would still just give me both names.

Thanks so much for any tips you can offer.

 

[JANELLEB]

Bruce, thanks for the counter suggestion. I am working on that angle.

Janelle

 

[Ngolem]

I think that Bruce's idea works fine as long as you make certain to reset the counter with each new invoice Jim

JimBroadbent@Hotmail.com