ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Evo-ERP and DBA Classic >> System Manager >> DE-a Wizard Query
http://www.istechforum.com/YaBB.pl?num=1643392222

Message started by Laura Oliver on 01/28/22 at 10:50:22

Title: DE-a Wizard Query
Post by Laura Oliver on 01/28/22 at 10:50:22

I love DE-a in DBA so I tried DE-a
in EVO, but I am not getting results in my columns when I hit execute.
I actually got results in one of my attempts but I don't know what i did and even in this somewhat successful attempt I didn't get anything but zero in my invoice subtotal and Invoice total column.
Below are my results before execute.

SELECT BKARINV.BKAR_INV_CUSCOD, BKARINV.BKAR_INV_INVDTE, BKARINV.BKAR_INV_CUSZIP,
BKARINV.BKAR_INV_SHPZIP, BKARINV.BKAR_INV_SHPVIA, BKARINV.BKAR_INV_SUBTOT,
BKARINV.BKAR_INV_TOTAL
FROM BKARINV
WHERE BKARINV.BKAR_INV_INVDTE >= '2021-01-01'
AND BKARINV.BKAR_INV_CUSZIP = '35124'
AND BKARINV.BKAR_INV_SHPZIP = '35124'
AND BKARINV.BKAR_INV_SUBTOT >= 1
AND BKARINV.BKAR_INV_TOTAL >= 1

I use this report to separate w/d shipments from Our Truck shipments when i buy business license for cities and counties. The cost is based on our truck shipments to that particular city or county so I have to weed out the w/c or hire carriers.

I must not understand the field filter. How do I tell it I want invoice dates between 1-1-21 and 12-31-21?
Thanks
Laura

Title: Re: DE-a Wizard Query
Post by prophet on 01/28/22 at 11:31:20

If you want to add a date range you can try the sample query below. See if it works out for you.

SELECT BKARINV.BKAR_INV_CUSCOD as 'Customer Code', BKARINV.BKAR_INV_INVDTE as 'Invoice Date', BKARINV.BKAR_INV_CUSZIP as 'Customer Zip',
BKARINV.BKAR_INV_SHPZIP, BKARINV.BKAR_INV_SHPVIA, BKARINV.BKAR_INV_SUBTOT,
BKARINV.BKAR_INV_TOTAL
FROM BKARINV
WHERE BKARINV.BKAR_INV_INVDTE >= '2021-01-01'
AND BKARINV.BKAR_INV_INVDTE <= '2021-12-31'
AND BKARINV.BKAR_INV_CUSZIP = '35124'
AND BKARINV.BKAR_INV_SUBTOT >= 1
AND BKARINV.BKAR_INV_TOTAL >= 1

Title: Re: DE-a Wizard Query
Post by Laura Oliver on 01/28/22 at 11:34:51

First thing I did wrong. I shuda used BKARHINV H H H H,

Also your filters of :
FROM BKARINV
WHERE BKARINV.BKAR_INV_INVDTE >= '2021-01-01'
AND BKARINV.BKAR_INV_INVDTE <= '2021-12-31'
is what i want but i need to go back and see how to add them. That will be a big help.
Thanks
Laura

Title: Re: DE-a Wizard Query
Post by prophet on 01/28/22 at 11:37:05

yes that is correct the BKARHINV and BKARHIVL are where the invoice records for AR are going to be.

Title: Re: DE-a Wizard Query
Post by Laura Oliver on 01/28/22 at 11:39:58

I just edit my previous post. I need to go back and figure out how to do a date range,

FROM BKARINV
WHERE BKARINV.BKAR_INV_INVDTE >= '2021-01-01'
AND BKARINV.BKAR_INV_INVDTE <= '2021-12-31'

Now that you showed me what it looks like maybe I can figure it out by myself.
Thanks

Title: Re: DE-a Wizard Query
Post by prophet on 01/28/22 at 11:41:56

Don't forget to change the BKARINV to BKARHINV