7 Replies Latest reply on Mar 21, 2014 10:02 AM by DanFarrell

    Count other fields

    DanFarrell

      Title

      Count other fields

      Post

           Hi 

            

           I have a filemaker pro 12 database of customers.

           The different products we sell each given a different field - and each one has a choice of entries: Paid, Reserved or is left blank.

           I would like to be able to tell which customers have bought a previous product, and which ones are first time buyers.

           Can I do a count/summary/calculation in a new field that will tell me how many fields contain 'paid' a particular record has? I have tried to use various formulae - including PatternCount and Sum but seem to be getting it wrong.

           Thanks

        • 1. Re: Count other fields
          philmodjunk

               Can you describe the tables, fields and relationships of your database in more detail?

               I think that the structure of your database will make what you want very difficult where a design change could make much simpler, but I need to know the basic design of your database to see if I am guessing correctly.

          • 2. Re: Count other fields
            DanFarrell

                 Ok

                 We sell workshops, and we need to regularly run registers of attendance for each course - so each product (workshop) has its own field (displayed as a column in table view).  Each of these workshop columns has either 'paid' 'reserved' or nothing in them - so we can see on the register needs to pay etc. 

                 Each customer is entered as a new field - so we have all of their details and then entries in each column that's applicable.

                  

            • 3. Re: Count other fields
              DanFarrell

                   *Each customer is entered as a new record.

                    

              • 4. Re: Count other fields
                philmodjunk

                     You can edit a post after you post it if you click the "edit Post" or "Edit" link to re-open it for editing: wink

                     As I suspected, the basic structure of your database makes this a difficult, if not impossible thing to do.

                     You really need a set of tables and relationships that look like this:

                     Customers----<Invoices-----<InvoiceData*>-----Workshops**

                     Customers::__pkCustomerID = Invoices::_fkCustomerID
                     Invoices::__pkInvoiceID = InvoiceData::_fkInvoiceID
                     WorkShops::__pkWorkShopID = InvoiceData::_fkInvoiceID

                     For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                     This design is patterned after the Invoices starter solutions that come with FileMaker 11, 12 and 13.

                     *In FileMaker 11, InvoiceData is named LineItems
                     **In the starter solutions of all three versions, Workshops is named Products

                     A Portal to InvoiceData is used to list each item that a customer has Paid for or Reserved. It takes what you have as multiple fields in the same records and makes them a set of related records with one record for each item that the customer reserves or pays for.

                     This makes it easy to produce summary reports based on the InvoiceData table that can give you counts of Paid and Reserved entries for each workshop.

                • 5. Re: Count other fields
                  DanFarrell

                       Ok thanks for trying to help.

                       How would you suggest importing my existing data into the new model?

                       I am happy with importing the customer data into the customer fields - but will there be a way of importing the customer data and preserving which workshops they're each booked into?

                        

                  • 6. Re: Count other fields
                    philmodjunk

                         It's definitely possible to do that, but you'll need to plan carefully. You'll also need to evaluate my suggested broad outline of a database design to make sure that it will work for you. I may be making assumptions about your business model that are not correct. In some cases, for example, you might not need an invoices table since you seem to be tracking the sale/reserved status of each workshop independently.

                         Here's a rough outline of the process that might work for you.

                         a) Define your customer, Invoices and InvoiceData tables and relationships.

                         b) if you do not have a field in your current table that serves as a unique ID for each customer, add one: Define a number field in the table, put it on a layout based on this table, Show All Records and then use the Serial Number option in Replace Field Contents to add a unique serial number to teach record in your existing table.

                         c) Go to a layout based on Customers and use import records to import records from your existing table into the customer table. Only import the ID number field into __pkCustomerID and any fields, such as customer name that are specific to the customer. After the Import update the "next serial value" setting on __pkCustomerID to be larger than the largest imported ID number.

                         d) Now go to the Invoices layout and use Import Records to import data into it to create your Invoices records. Map fields so that your ID field imports into _fkCustomerID. There may not be any other fields to map to your invoice table except maybe a date field if you have one. Be sure to enable auto-enter options during the import so that __pkInvoiceID will have a value in each record created by the Import

                         e) Define a relationship linking your original table to Invoices by matching _fkCustomerID to the ID field in your original table. Then create and perform a looping script that loops through your original records and uses the __pkInvoiceID of each related invoice record and the presence of data in each of the Workshop fields in turn to create records in InvoiceData with the correct _fkInvoiceID and _fkWorkShopID values.

                    • 7. Re: Count other fields
                      DanFarrell

                           Thanks for this - I will give it some thought - this is not a job for a tired Friday afternoon.

                            

                           Thanks for being so prompt and thorough!