wildmatch of two fields from different tables - giving two return values in straight line table

[GR456]

Problem with wildmatch of two fields from different tables - giving two return values in straight line table.

Description of logic:

*Check text field (LDText.Test1)

          *If it contains both

               field (INSP Report)

               field (ID#)

          *Then return

               field (WONUM)

expression:

     if(wildmatch([LDText.Test1],'*'&[Insp Report]&'*'&[ID#]&'*'),[WONUM])

PROBLEM: If it matches - I end up with two (2) line entries in my straight line table

     *One  with the field (WONUM) populated correctly

     *Another with a null populated

Simplified Example of Return data where Test1 meets the wildmatch  criteria and Test2 & Test3 do not meet the wildmatch criteria:

         Test1    WONUM1

         Test1    -

         Test2    -

         Test3   -

Any ideas or help on how to only get the one (1) return line with the field (WONUM) when the wildmatch criteria is met?

[Taoufiq_Zarra]

@GR456 

Maye  be check " Suppress when Value is Null"

[GR456]

Unfortunately, when I tried the suppress when null option, it only displays the rows of data where there is a value for WONUM - and I need the table to display the other columns (fields) whether the WONUM is populated or not. Any other ideas - or is there something else I can do to the suppress option to make it work?

[stevejoyce]

How bout like this...

     if(aggr(sum(wildmatch([LDText.Test1],'*'&[Insp Report]&'*'&[ID#]&'*')),LDText.Test1) >0,[WONUM])

Trying to aggregate count matches on LDText.Test1, if it's got atleast 1 match then return WONUM

[GR456]

That didn't give the result I want/need either. I believe I'll divide the information into two tables so that I can use your suggestion to suppress when null. I appreciate the help!