7 Replies Latest reply on Oct 15, 2012 2:47 PM by philmodjunk

    Tallying individual item sales by name and assigning to customer?



      Tallying individual item sales by name and assigning to customer?


           I will have a database for digital download Products, and one for Customers. What I want to do is have a list or report of  how many of each product were sold, in addition to $ of sales. I've looked over the Sales and inventory type templates that 'come' with Filemaker and the BPS 3.0 solution suite (I am using version 10). The ready-made templates I looked at do reports of the sales figure $ tallies, but not the product quantity sales breakdowns. Inventory doesn't really help me much since these are unlimited digital products that will never be out of stock. I just want a running tally of "Product 1 has sold 200 times", and in the customer list, keep track of what items they have bought.

           Since on an invoice, there may be many line items showing each individual item. So I'd want these items to be summed up in a report from any customers who bought it.  

           The second thing is how to handle repeat customers. For example, a customer may buy item 1, then come back a month later and buy item 2. So I'd also want the customer to be combined so that the customer name is not listed twice like being 2 customers, so I'd want the name to be recognized then have the sale items (and future sales items) assigned to that one customer.

           I would be importing the sales records from PayPal.

           Is this too complicated to set up in Filemaker? If not, how would I go about doing this? I was going to start from scratch which I've done before, but it may be better to modify a ready-made template that's already structured with reports and such.

           A developer with software sales would have this kind of information for his customers, so it's typical sale detail information. They would also keep track of serial numbers. I just don't know how to do it in Filemaker.

           Thank you!

        • 1. Re: Tallying individual item sales by name and assigning to customer?

               Do you have these tables and relationships? (names may be different)


               If so, a summary report created on a layout based on your LineItems table should do the trick.

               See this tutorial thread, which happens to use an invoicing model in its example, to learn more: Creating Filemaker Pro summary reports--Tutorial

               Please note that this is an old thread that will not automatically pop up in Recent Items if you post a response to it. If you have any questions after reading that tutorial, please post them here or your questions may not be seen and thus may not get any response.

          • 2. Re: Tallying individual item sales by name and assigning to customer?

                 Thanks Phil. I seem to be having a problem prior to this step. I made a Sales Order (Invoice) table and was using a line item portal for this (based on other templates that use line item portals for the item entry). I am not able to enter anything in it though. So, I changed the portal to pick up fields from my Products table. Still didn't work. Then I added those same fields in the Sales Order table and used them, but that didn't work either. 

                 I don't know what I'm doing wrong. Which also leads me to the question: should the portal on this invoice feed from a separate line item table, or from the Products table?

                 See the pic for the relationship I have set up.

                 There is one extra thing to mention here. Normally in an invoice I'd use drop down value lists using table items, so that when I enter the SKU, it will fill out the item name for me. I'd also use calculations for totals, etc. But in this particular case, I will always be importing the data from a PayPal export, so I thought perhaps I should just have plain fields that can be populated with the import, which also means no calculations. Unless such imports can override the fields waiting for drop down input and won't interfere with fields that are set up to calculate or draw from value lists? Yes? No?

                 Thank you!




            • 3. Re: Tallying individual item sales by name and assigning to customer?

                   Your relationships look to be correct. Did you enable "allow creation of records via this relationship" for Line Items in the link from sales orders to line items?

              • 4. Re: Tallying individual item sales by name and assigning to customer?

                     That was it, and it totally slipped by me. Thank you! 

                     On the question of typical drop down lists and auto fill fields: if I will be importing csv data for sales (or any imports) as opposed to typing it in manually, is FileMaker able to import to those fileds properly if drop down / value lists are enabled? Or should the fields be manual input only?I would also think numerical fields should also NOT be set for calculation, correct, or is there an override in place for imports?


                • 5. Re: Tallying individual item sales by name and assigning to customer?

                       Field formats will have no effect on how the data imports. The key, when manually importing by selecting Import Records from the file menu, is to first select a layout based on the table into which you intend to import data. If you are going to import data into the portal's table, switch to a layout based on the portal's table before importing.

                       If you will be importing data over and over again, set up your import records process in a script so that you need only click a button to import your data.

                  • 6. Re: Tallying individual item sales by name and assigning to customer?

                         By the term "a layout based on the table", does this just mean the layout that holds that table's records, (e.g. Sales Order layout, using the sales order table's fields)? 

                         My sales orders entry layout will hold the basic information like Invoice ID, customer name, which are fields in the sales order table, and on this layout is a portal that contains the invoice's line items, like item number, item name, price, etc. which come from another table called "line items". From dissecting FM templates, it seems invoice forms are made this way.

                         So for importing, I would want to import to this layout which would mean some information would have to go into the main area (customer name, etc.) and the other information would go into the portal (such as the item number, item name, price). Would an import be able to "divide" itself like this: e.g. the proper information going into the main area and the portal?

                         Speaking of portals, this is the one thing that trips me up sometimes. Using this Sales Order as an example, should a portal there be a "sales order" with its fields drawing from the line items, or should the portal be a line item portal with the fields on it also being line items? Right now, I have both the portal and its fields are from line items.

                         I have another database involving portals and on one layout, it works, and on another page, I simply changed the field sources and it doesn't work—in fact it doesn't even show up on the page, although it seems it should. (and I have "sent to front")..but that's another post I'll be making to get clearer on portals.

                         Thank you!


                    • 7. Re: Tallying individual item sales by name and assigning to customer?

                           Layouts do not store data, tables do. Every layout refers to a specific table in the following manner:

                           Open Layout Setup and check the name selected in the "show records from" drop down. This, though it may look otherwise, is not the name of a table. Instead, it is the name of a table occurrence--one of the boxes found in Manage | database | relationships. If you hover the mouse over the upper left corner of one of these table occurrences, you'll see the name of the table occurrence's data souce table pop up in a tool tip. This matches to a name on the tables tab of manage Relationship, listed down the left hand side of that tab. Thus, a layout refers to a table occurrence, which in turn refers to a table. Thus, the data source table pointed to by the layout's specified table occurrence is the table that it is "based on".

                           Data shown in a portal does not come from the same table occurrence as the data shown on the rest of the layout. In your case, you have two tables, one probably called invoices and one porbably called lineitems. To import data from your csv file, you will need to import your data at least twice, once into the invoices table and once into the line items table. If you do this by selecting import from the file menu, you must select the invoices layout and import data from your csv, then you must change to a different layout, one that lists an occurrence of the portal's line item table, in order to import data in to the line items table.


                                it seems invoice forms are made this way.

                           For data entry purposes, you are correct that this is the most typical approach used. It is not used for printing the invoice in the starter solutions, however.


                                Right now, I have both the portal and its fields are from line items.

                           That would normally be the correct design for an Invoice Layout with a portal for listing line items. But there are exceptions to that.

                           You may find this link on Table Occurrences helpful: Tutorial: What are Table Occurrences?