I am trying to do a simple BAQ on AP Invoices and a post date; at least I would have thought this was simple
The Post Date is only in the GLJrnDtl and since it has a Debit and Credit entry it is giving me both. I have tired various ways to narrow it down.
This is what I have now and it shows both lines for each group. I do have the Query options set to Distinct
One question is I added a GroupID on the Table Relations and fixed my duplication but it removed the other group…Why??
Put a table criteria on GLJrnDtl to only select records where CreditAmount <>0. That should eleiminate all the Debit records, leaving you “half” of each GL tran
Thank you…that did it. I had tired with the Debit Amount >0 but it didn’t work. Thanks you 
can you post the query phrase when you get a chance. It is best to select all copy and paste vs. a screen shot.
Thanks,
select distinct
[APInvHed].[InvoiceDate] as [APInvHed_InvoiceDate],
[APInvHed].[GroupID] as [APInvHed_GroupID],
[APInvHed].[InvoiceNum] as [APInvHed_InvoiceNum],
[Vendor].[VendorID] as [Vendor_VendorID],
[Vendor].[Name] as [Vendor_Name],
[APInvHed].[DocInvoiceAmt] as [APInvHed_DocInvoiceAmt],
[GLJrnDtl].[PostedDate] as [GLJrnDtl_PostedDate]
from Erp.APInvHed as APInvHed
inner join Erp.Vendor as Vendor on
APInvHed.Company = Vendor.Company
and APInvHed.VendorNum = Vendor.VendorNum
inner join Erp.GLJrnDtl as GLJrnDtl on
GLJrnDtl.Company = APInvHed.Company
and GLJrnDtl.APInvoiceNum = APInvHed.InvoiceNum
and ( GLJrnDtl.CreditAmount <> 0 )
thanks
BAQ works like a charm but my dashboard is not working…I deleted my Tracker View and tested it open…it ran fine. Once I add a filter on Invoice date is chokes and returns nothing. ODD…
Single filter on the Invoice date? As in Invoice date = ... ?
Or do you have ranges? Start Date >= ... and End Date <= ...
I did kind of both.
First I had a start and End on Invoice date
Then I did Invoice Date >=
To have both you need two separate fields. In the BAQ, make a calc field that is just a copy of the date filed. The in the Dashboard, filter on each. One being >= and the other being <=
Really? I customize the tracker view most of my trackers are that way and never had an issue. But I will try it.
A dashboard Tracker view only lists each field once. How are you doing the second input? A customization?
Adding the additional field did not work.
Not sure I follow but when I customize tracker view it is just like a customization. I select the EpiDate from the tool box and then make the Tracker Query control true and select the query and then set the dashboard conditions
You don’t have to have two fields. The user guide shows you how to add another tracker field that will work on one BAQ field.
However, your method of adding a second. duplicated field, is probably better/easier.
Okay. The customization method should work. (And I use that method too).
I am just baffled that the BAQ works fine but once I put a filter on is in the Dashboard it returns nothing.
so when you run the dashboard without any trackers, does it run? edit I see you tried that, good.
Did you try just adding the first tracker (no customization) does that work?
Also, did you remove the filters you had on the BAQ?
Double check the original control that the Tracker creates with the one you add for the other end of the range. Should almost be identical with one having “LessThanOrEqualTo” and the other “GreaterThanOrEqualTo”.
Make sure you don’t have these two backwards. As in
InvoiceDate <= 3/1/2019 AND InvoiceDate >= 3/15/2019
that would return nothing
Sorry guys…I must of had something wrong and just didn’t see it. I just created the tracker view and it is filtering properly. Thanks for the 2nd eyes.