I received a "Tech Tip" from Support regarding
the "how to". I've pasted that into the body
below for those who choose to use it. For our
instance it appears that the technique does not
work if you are on a SQL database.
There is an XML sample which I can forward if you contact me directly:
bcarlson@....
Here is the contents of the tech Tip:
Summary: BAQ How to filter a BAQ on a null or blank field.
Book: Support Solutions
Page: 7290MPS
NOTE: This is not appropiate for 8.03.4xx on Sql
platforms. See CR 3445ESC. Targeted for 409
Page NO:
7290MPS
PROBLEM:
How to compare null values with a BAQ. How can I
filter on a null value with a BAQ?
STEPS TO REPRODUCE:
The easiest way to do this is to create a
criteria that compares against a field that is
completely empty. Ie, character10 in most cases
will contain no data. (any field that has no data in it will work)
To do this:
In this sample query I will use a query that uses
part and partmtl. Part.Character01 has some
fields that are populated and some that are NULL
(empty). This will demonstrate how to get only
the fields that are NULL and only the fields that are non null (populated).
1. Create a new query, give it a name and
description and then press the phrase builder button.
2. Select the part and partmtl table from the available table columns.
3. Press next.
4. At the table relationship screen press
next. (the default relationship will work) 5. At
the critieria screen for the Part table press the
add new criteria button, and for fieldname select
TypeCode, select the "=" operand and check the "is constant" checkbox.
6. In the constant text box put the letter
M. (we only want manufactured parts).
7. Press the add new criteria button again to add an additional criteria.
8. In the AndOr box select And
9. For Fieldname select Character01. (this is
the field we want to compare to the null fie 10.
ld). Ie, character 01 potentially has data in it, sometimes it may be null.
11. For the CompOp (operand), select "=".
12. For the ToDataTableID select the Part table.
In the ToFieldName select Character10.
Character 10 is the field that will always contain a NULL value in this case.
13. Press Finished.
14. Select which fields you want to display in
the display tab, and then go the analyze tab and press test.
15. This will show all records that Character01 has a NULL value for.
If we wanted to display all fields that are NOT
NULL, in step 10 you change the operand from = to <>. ("<>" means NOT EQUAL).
At 04:00 PM 3/19/2010, you wrote:
the "how to". I've pasted that into the body
below for those who choose to use it. For our
instance it appears that the technique does not
work if you are on a SQL database.
There is an XML sample which I can forward if you contact me directly:
bcarlson@....
Here is the contents of the tech Tip:
Summary: BAQ How to filter a BAQ on a null or blank field.
Book: Support Solutions
Page: 7290MPS
NOTE: This is not appropiate for 8.03.4xx on Sql
platforms. See CR 3445ESC. Targeted for 409
Page NO:
7290MPS
PROBLEM:
How to compare null values with a BAQ. How can I
filter on a null value with a BAQ?
STEPS TO REPRODUCE:
The easiest way to do this is to create a
criteria that compares against a field that is
completely empty. Ie, character10 in most cases
will contain no data. (any field that has no data in it will work)
To do this:
In this sample query I will use a query that uses
part and partmtl. Part.Character01 has some
fields that are populated and some that are NULL
(empty). This will demonstrate how to get only
the fields that are NULL and only the fields that are non null (populated).
1. Create a new query, give it a name and
description and then press the phrase builder button.
2. Select the part and partmtl table from the available table columns.
3. Press next.
4. At the table relationship screen press
next. (the default relationship will work) 5. At
the critieria screen for the Part table press the
add new criteria button, and for fieldname select
TypeCode, select the "=" operand and check the "is constant" checkbox.
6. In the constant text box put the letter
M. (we only want manufactured parts).
7. Press the add new criteria button again to add an additional criteria.
8. In the AndOr box select And
9. For Fieldname select Character01. (this is
the field we want to compare to the null fie 10.
ld). Ie, character 01 potentially has data in it, sometimes it may be null.
11. For the CompOp (operand), select "=".
12. For the ToDataTableID select the Part table.
In the ToFieldName select Character10.
Character 10 is the field that will always contain a NULL value in this case.
13. Press Finished.
14. Select which fields you want to display in
the display tab, and then go the analyze tab and press test.
15. This will show all records that Character01 has a NULL value for.
If we wanted to display all fields that are NOT
NULL, in step 10 you change the operand from = to <>. ("<>" means NOT EQUAL).
At 04:00 PM 3/19/2010, you wrote:
>[Non-text portions of this message have been removed]
>
>There is a way I understand some people have
>been successful with (I wasn't, though). Try
>linking your main table to Company (linking only
>through Company fields), and then do a selection
>criteria where Revision (in the part table you
>are using) is equal to Company.Character01
>(where you know Company.Character01 is not used, thus null.
>
>Hope it works for you.
>
>Regards.
>
>Roel Martinez
>ERP Analyst
>
>SABRE TUBULAR STRUCTURES (tm)
>www.sabretubularstructures.com<<http://www.sabretubularstructures.com/>http://www.sabretubularstructures.com/>
>8653 E. Hwy 67 | Alvarado, TX 76009
>Phone: 817-852-1700 ext. 11858 | Direct Line: 817-852-1858 |Fax: 817-852-1705
>
>[Non-text portions of this message have been removed]
>
>