Question

BAQ - New Invoices from customers who have not purchased for 2+ years

  • 8 April 2021
  • 5 replies
  • 151 views
EM81

I am trying to create a BAQ to display (month to month - using range parameters) the new invoices from customers who have not made a purchase in two or more years.  Epicor 9 used to have a “Last” option in the BAQ designer, I believe, but I haven’t had the need to do anything like that for a while, so I have no idea where to start. 

I also need to determine invoices (from this same monthly range) where an active customer is now purchasing from a new shop they’ve never bought from previously, or a shop they have not purchased from in at least two years.  I’m stumped.  

Any help would be greatly appreciated!

Thank you!

~Erin

5 replies

fred
Userlevel 3

Do a subquery to find Invoices (or orders) since two years ago.  Get the distinct CustNums.  Then have a top level query that does a join from customer to the subquery where you get all the Customers.  Set Subquery Criteria where the subquery is null (that way you only get customers who AREN’T in that subquery pulling back invoices for the last two years).

 

I’m not sure what you mean by “new shop”.  I’d need more information to help you with that one.

Fred Zelhart, CodaBears, Inc.
EM81

Thanks again, Fred.  By “new shop” I mean that the ProdCode on the line item is not a ProdCode the customer has ever purchased any products from either ever, or it has been at least 2 years.

~Erin
fred
Userlevel 3

For the “new shop”, do a similar subquery.  This one would be OrderDtl showing CustNum and ProdCode where the OrderDate is within the range you need.  Have that Subquery be a distinct CustNum/ProdCode.  Let’s call that suquery “O”.  

This next part is off the top of my head and there’s probably a more elegant solution.  If it were SQL instead of a BAQ there are WAY more elegant solutions.  Anyways, make a second subquery, call this “CPC”.  Put on Customer and ProdCode and join them ONLY ON Company and show CustNum and ProdCode.  Basically you’re going to get a row back for every customer and prodcode combination.

On your TopLevel, put on subquery “CPC” and join it to “O” where the join is having all records from CPC.  Do a subquery criteria where O.CustNum is null.  This way you are getting customers and prod codes that haven’t been ordered.  You can then join this to Customer if you need more than just the CustNum field.

Fred Zelhart, CodaBears, Inc.
EM81

Fred,

I may be understanding you wrong, but I want to see invoices with an InvcHead.InvcDate between 03/01/2021-03/31/2021 so that will put them in the first subquery you mentioned.  But I need to be able to check that the previous invoice was at least 2 years old.  So if they’ve ordered from us before, but not within two years, they count as “new business.”  

If I filter out customers that are in the first query, these will filter out as well, won’t they?  I need some way to compare the new invoice to the previous invoice to check for a two year gap.  Can this be done?

 

Oh wait.  I read that first query wrong.  It’s supposed to be invoices older than two years, right?  That makes more sense.  ;)  I’ve been working on these all day and my brain is fried.  I’d take a break to look with fresh eyes if I could but I need this done.  

~Erin
D
Userlevel 1

Hello,

Here’s how I would do this.

1.First, a BAQ with 2 date parameters: pDateFrom and pDateTo. This will be the date range for your ‘current’ invoices.

2.Add a query, let’s call it qInvoices for the ‘current’ invoices. This will be your main/top query. You will add the InvcHead table and add 2 criterias using the parameters. 

InvcHead.InvoiceDate >=pDateFrom

InvcHead.InvoiceDate<=pDateTo

3. Add another query qOldInvoices for the ‘old’ invoices. This will be your subquery with the same table InvcHead but with a criteria set using an expression like below:

InvcHead.InvoiceDate <= DateAdd(year, -2, pDateFrom)

You need just two fields in this query: Company and CustNum. Group the query by those 2 fields. 

4. Go back to your main query qInvoices, bring in the subquery qOldInvoices and make an INNER JOIN by Company and CustNum between them.

Dragos

Epicor ERP 10.2.700.18

Reply


Terms & Conditions