I've poured over this forum, and although there are similar threads to my question, I can't get it to work with my problem.
Tables:
PRODUCT (detail of things we sell by PLU#)
INVHEADER (top level info of an invoice)
INVDETAIL (detail by PRODUCT# of an invoice)
DEPT (department to which each PRODUCT# belongs)
DEL_PRODUCT (any PRODUCT that is deleted goes here)
I'm trying to run a report filtering by INVHEADER date and EMPLOYEE number, and DEPT number with the detail being INVDETAIL. If I don't include DEPT in the selection formula or GroupBy, then I get all invdetail for those dates and employees. Once I include DEPT, then it selects only INVDETAIL records that it finds in the PRODUCT table or DEL_PRODUCT table.
The problem is that there are a couple of INVDETAIL plu#s that are not in the PRODUCT table or DEL_PRODUCT table. Therefore they won't have a DEPT #. I want to include them on this report.
The selection formula I've tried is:
{INVHEADER.RegisterNo} in [3, 1] and
{INVHEADER.InvoiceTime} in DateTime (2008, 01, 07, 08, 00, 00) to DateTime
(2008, 01, 07, 23, 59, 00) and
{INVHEADER.EmployeeNo} <> 50 and
{INVHEADER.Type_Of_Invoice} in ["Sales", "Void"] and
isnull({PRODUCT.Dept}) or {PRODUCT.dept} in [25, 26, 20, 19, 18, 17, 16,
15, 14, 13, 12, 11, 10]
Thanks.
Mike
Tables:
PRODUCT (detail of things we sell by PLU#)
INVHEADER (top level info of an invoice)
INVDETAIL (detail by PRODUCT# of an invoice)
DEPT (department to which each PRODUCT# belongs)
DEL_PRODUCT (any PRODUCT that is deleted goes here)
I'm trying to run a report filtering by INVHEADER date and EMPLOYEE number, and DEPT number with the detail being INVDETAIL. If I don't include DEPT in the selection formula or GroupBy, then I get all invdetail for those dates and employees. Once I include DEPT, then it selects only INVDETAIL records that it finds in the PRODUCT table or DEL_PRODUCT table.
The problem is that there are a couple of INVDETAIL plu#s that are not in the PRODUCT table or DEL_PRODUCT table. Therefore they won't have a DEPT #. I want to include them on this report.
The selection formula I've tried is:
{INVHEADER.RegisterNo} in [3, 1] and
{INVHEADER.InvoiceTime} in DateTime (2008, 01, 07, 08, 00, 00) to DateTime
(2008, 01, 07, 23, 59, 00) and
{INVHEADER.EmployeeNo} <> 50 and
{INVHEADER.Type_Of_Invoice} in ["Sales", "Void"] and
isnull({PRODUCT.Dept}) or {PRODUCT.dept} in [25, 26, 20, 19, 18, 17, 16,
15, 14, 13, 12, 11, 10]
Thanks.
Mike