Solved

Record counter reset when Customer Number changes

Posted on 2013-11-13
9
436 Views
Last Modified: 2013-11-14
I set up a counter to count the number of invoices a customer generated during a period of time.
//@Counter of Customer Invoices
NumberVar invcounter;
whileprintingrecords;
invcounter := invcounter +1;

//@Counter of Customer Number
NumberVar counter;
whileprintingrecords;
counter := counter +1;

This counter works...However I need to reset the counter of the customer invoices when the next new customer comes up (I am using grouping on the customer number and Customer invoice.  This is so  I can determine that the invoice number changes I can compare data from the previous invoice for a specific product to see if selling that product(sample) produced new sales.

How do I reset the counter back to "0" if the customer number changes?

In summary I am trying to see if a sample product sold yesterday produced a new sale of that type of product today.
0
Comment
Question by:Scott Johnston
  • 6
  • 3
9 Comments
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 500 total points
ID: 39646433
How is the report setup?

I assume you have
Group1 - Customer
Group2 - Product
Group3 or sort order - Invoice

If the report is limited to a specific product then the Group2 isn't needed.

In the customer group header add a formula - reset on customer change

whileprintingrecords;
NumberVar counter;
counter := 0;

Open in new window


In the invoice group header do the counting

You can check the previous record
whileprintingrecords;
NumberVar counter;
If OnFirstRecord or Previous({InvoiceNumberField}) <> {InvoiceNumberField} then
    counter := counter + 1;

Open in new window


mlmcc
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 500 total points
ID: 39646436
If you have the groups as above you can use a summary

If you have a product group

    DistinctCount({InvoiceNumberField},{ProductField})


If you are running for 1 product and just have the customer group

    DistinctCount({InvoiceNumberField},{CustomerField})


mlmcc
0
 

Author Comment

by:Scott Johnston
ID: 39646438
I just have:
 
Group1 Customer
Group2 invoice #
Details (products)
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

Author Comment

by:Scott Johnston
ID: 39646449
I like previousrecord, never used it,
when I try the command I get a error the formula result must be a Boolean?
0
 

Author Comment

by:Scott Johnston
ID: 39646459
hmmm, I took out ' : ' and it fixed the problem.
Previous record may not work because the invoice for each customer are not necessarly in order.

Customer A has invoice number 1234567 and his next invoice number is 2345690.

I got a false on every entry which I know there are 2 invoice in some cases.
0
 

Author Comment

by:Scott Johnston
ID: 39646476
I have distinct count setup already, I cannot use that to check to see if the record changed?
If I can I not sure how?
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 39646720
You have to have the report set up as I suggested to be able to use the previous idea.  If you don't then the previous record will most likely be for a different product.

What you are asking for is a difficult formula using arrays and counters.  I am not even sure if it is possible.

Can an invoice have 2 detail lines for the same product?

mlmcc
0
 

Author Comment

by:Scott Johnston
ID: 39649026
I beginning to think that as well...Marketing is trying to get data that will support a marketing program.  I have decided to break this into separate reports.
Thanks for the support!
0
 

Author Closing Comment

by:Scott Johnston
ID: 39649044
Thanks for the Support
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question