We previously created a BAQ to tell us how long customers take to pay (days to pay). BAQ works well.
We are now trying to add a display field to the BAQ to show us HOW customers pay (check, electronic funds transfer, etc.). We had thought that would simply be a matter of adding a display field from the payment method table. However, when we do that, our query results are reduced significantly; 27 hits (versus 2,000 before adding that field).
Does anyone know why this is happening? The only thing we can think of is that customer payments are entered in batches. Is it possible the payment method isn’t being applied to all payments in the batch?
I think the only change I made way to se the join for the PayMethod table to “All Row From Customer”. Here is the SQL if that helps:
/*
* Disclaimer!!!
* This is not a real query being executed, but a simplified version for general vision.
* Executing it with any other tool may produce a different result.
*/
select
[Customer].[Name] as [Customer_Name],
[Customer].[CustID] as [Customer_CustID],
[InvcHead].[LegalNumber] as [InvcHead_LegalNumber],
[InvcHead].[InvoiceAmt] as [InvcHead_InvoiceAmt],
[InvcHead].[InvoiceDate] as [InvcHead_InvoiceDate],
[InvcHead].[ClosedDate] as [InvcHead_ClosedDate],
[PayMethod].[Name] as [PayMethod_Name],
(convert(int, datediff(day, InvcHead.InvoiceDate, InvcHead.ClosedDate))) as [Calculated_DaystoPay]
from Erp.InvcHead as InvcHead
inner join Erp.CashDtl as CashDtl on
InvcHead.Company = CashDtl.Company
and InvcHead.InvoiceNum = CashDtl.InvoiceNum
and ( CashDtl.TranType = 'PayInv' )
inner join Erp.Customer as Customer on
CashDtl.Company = Customer.Company
and CashDtl.CustNum = Customer.CustNum
left outer join Erp.PayMethod as PayMethod on
Customer.Company = PayMethod.Company
and Customer.PMUID = PayMethod.PMUID
where (InvcHead.OpenInvoice = false)
group by [Customer].[Name],
[Customer].[CustID],
[InvcHead].[LegalNumber],
[InvcHead].[InvoiceAmt],
[InvcHead].[InvoiceDate],
[InvcHead].[ClosedDate],
[PayMethod].[Name]
Got it, thanks. That definitely helped bring in the full list of results. However, most of the records are not pulling in a pay method result, it’s just blank. So I guess I’m left with my original question. If there are a number of customer payments being processed in a batch, is it possible the payment method isn’t being applied to all items in the batch?
I think the pay method gets assigned based on the customer’s pay method. If the customer doesn’t have a pay method setup, then it won’t pull one in. You could check to make sure the pay method is setup for every customer. If it is setup correctly, then I would run a few test batches to see where the pay method is falling off. For example, setup a new test customer, and leave the pay method blank. Then try to process a payment from them to see what happens.
