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 ?
to get a total count of records on my layout
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.
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 ANDInvoices::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.
Indeed you are correct Mr. Phil...I didn't read his question that way until you said that.
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
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.
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.
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.
Retrieving data ...