How to get a count of items in an customer record?

[machtenx]

I have a database of customers, inside that database is a portal to a database of invoices.

I want to find out how many of a certain item number they have purcashed.

I tried using the exact function in a calculation field but it returns only a 0 or a 1. how else can I get it to count how many of a certain item they have purchased?

 

[machtenx]

I need an exact number rather than just a true or false

 

[NorthStarDA]

are the products kept in a 3rd database related to invoices or are you just trying to count invoices? if you are only trying to count invoices for that customer you could add a calc field in your customers table.

Count(relationship_name::customer_id) --return number

assuming that you are relating your customers to the ivoices by customer id.

 

[machtenx]

Products are not kept in any database at this point. I'm basically setting up these two databases to get information from exported files from myob.

Number of invoices is not really useful, it's the number of invoices with a certain product that have an invoice total greater than 0.

sorry if I'm making it too complicated; bottom line I want to know how many of each product a customer has purchased

 

[NorthStarDA]

how exactly are the products stored? is it a repeating field in invoices? is it only 1 qty field in invoices?

 

[machtenx]

I just realized I've mis-stated my invoice database. it is actually more of an invoice line items database in that each record contains one product item number and a quantity, the item number. I am using the company name as the primary key. there is only one quantity field in "invoices" I should call it "invoice lines

 

[NorthStarDA]

ok, then my previous example should work with a little modification

Sum(relationship_name::quantity) --return number in a calc field in customers

 

[machtenx]

this gives me a total number of items purchased, I need to know how many of each item has been purchased.

 

[NorthStarDA]

im a bit confused on it, if you have 2 tables:
table 1: customers-
companyName
//one record per customer

table2: invoice_line_items-
companyName
productNumber
quantity
//one record for each separate productNumber on an invoice

and the 2 are related by companyName, a simple portal in customers --> invoice_line_items showing fields productNumber and quantity would be a list of each product and how many of them were purchased, would it not? is this what you are looking for?

 

[machtenx]

Your statements are correct. I see where I was unclear now.

There is a count for how many of each item is purchased for that line, as you said.

To clarify, I want a grand total of each item. In other words I want to know how many of item x customer y has purchased in his whole history across all invoices.

Sorry for not being more clear about that!

 

[JeanW]

Make a concatened field with item x and customer y, this will give you the unique key where you can make the count/sum with.
Make a summary report that can show those details.

HTH

 

[Schlogg]

No need to even do that. Run a report from the Invoice Line file. Sort on Customer\Item #. Summarise by Item #. You can get the customer details by using a relationship back to the customer file.

Cheers,
Paul J.