7 Replies Latest reply on Oct 11, 2010 9:59 AM by philmodjunk

    Invoice count per custemer



      Invoice count per custemer



      when i make a new order for a custemer i want to count the invoicenumber for every custemer for that year

      is this possible ?

        • 1. Re: Invoice count per custemer

          to get a total count of records on my layout

          1. I put a field in the table called AllRecords or TotalRecords
          2. Make it a calculation field using Get ( TotalRecordCount )
          3. Then on the layout I put something like this...
          4. Record @@ of <<AllRecords>>
          5. Then I can always reference it from that table later also if I need it for anything.
          • 2. Re: Invoice count per custemer

            Yes, but Job Van Baars wants the count for each customer. This is indeed possible and there are different options depending on what you want to see in your layout.

            If you want to enter/select a customer on an invoice layout and see the total number of invoices created for that customer for the same year, you can use a relationship and a calculation field that uses the count function to count the number of related records. If you want to see a report that lists all customers for the current year with the number of invoices next to their name, you can use a special summary report layout with a summary field defined to return the "count of" a non-blank field such as the invoiceID field.

            Option 1:

            You need  a field that uniquely identifies each customer. This is usually done with a CustomerID field that links invoices to a customer or contacts table. You need  a field that returns the year of the invoice date, define a calculation field, cYearInvoice as Year(InvoiceDateField) where InvoiceDateField is a field that auto-enters the creation date.

            Open Manage | Database | Relationships

            Click the Invoices table occurrence box to select it and then click the button with two green plus signs to make a new copy of it.

            Name the new table occurrence box "SameCustomerYear".

            Create this relationship linking the two table occurrences:

            Invoices::CustomerID = SameCustomerYear::CustomerID AND
            Invoices::cYearInvoice = SameCustomerYear::cYearInvoice

            Now click the fields tab and define this calculation field in Invoices: Count ( SameCustomerYear::CustomerID ) and it will return the number of invoices by the same customer for the same year as the current invoice.

            • 3. Re: Invoice count per custemer

              Indeed you are correct Mr. Phil...I didn't read his question that way until you said that.

              • 4. Re: Invoice count per custemer

                Thank you very much it works

                only one thing

                when i make a new order the first order for every custemer starts with noting and the second order starts with 1 i want it to start with 1



                • 5. Re: Invoice count per custemer

                  Better check your set up. The first invoice should count as 1 as long as both invoice fields used in the relationship have a value and the record is committed.

                  • 6. Re: Invoice count per custemer

                    The answer you gave me works but it  starts with 0 because it only counts the orders that are already in the system so that is why it starts with 0 with the first order with a new customer because there are no orders in the system,  when i delete a order and make a new one i have 2 orders with the same order number


                    i will try to explain what i wanted to do

                    i want to make a order number that is build up from customer ID/ Year and /order number. and i want to see how many order that customer did that year so that is why i want to count the order number for every customer.

                    • 7. Re: Invoice count per custemer

                      What I described was a set up to work from the Invoices layout, not customers. Since you have to create an invoice first, it will match to itself as invoice 1 and each subsequent invoice will count up from there. Note that the invoice record must have valid values for CustomerID and Year and be committed before the correct number will be displayed. In many layouts, this will be auto-matic unless you have disabled "Save record changes automatically" in Layout Setup.

                      I would not use the method you describe for generating order numbers as it is very problematic to insure that all invoices get a unique order number and this is not necessary to see how many orders were placed by a customer in a given year. Use an auto-enter serial number to unqiuely identify each order, but use a calculation field like I orginally described to count the number of invoices thus created.