1 2 Previous Next 19 Replies Latest reply on Oct 31, 2013 9:58 AM by willrollo





           I have a customers table that is related to an invoice details table. This invoice details table has two subsequent tables related to it - Orders and Invoices. The idea is that an order can be created using the basic invoice details table, retrieving an order number from the orders table and then, if confirmed, converted to an invoice by getting an invoice number from the newly created invoice record from the invoices table. Anyway, my point is that a customer could have 5 linked invoice details records, 3 of which may be orders and two could be invoices. I want to have a calculated field that can count the number of invoices that the customer has, from the customers table. At the moment I can only count the number of invoice details records which doesnt help as I want to differentiate between the orders and invoices. The Invoice details table has fk_orders and fk_invoices fields that retrieve the serial numbers from the related tables when either is created,..

           thank you

        • 1. Re: Count

               This is another example of the "sum If" issue often asked about here in the forum. You want a count, total or other aggregate value of a set of related records, but only if a value or values in that related record meet certain criteria--in your case whether they are an order or also an invoice.

               Context, what you need to do with these counts and your own personal skill set and preferences will determine which method works best for you.

               See this thread for a summary of two of the most commonly used options: Sum_Calculation based on condition

               In addition to those methods, FileMaker 12 users can use Execute SQL to return a selective count of related records by using the WHERE clause in the SQL expression.

          • 2. Re: Count

                 That's great - thank you. Didnt think to look up Sum on the forum!


            • 3. Re: Count

                   Hi Phil

                   Still having a few issues with this...

                   Should I create a calc field in my Invoice details table that resembles something like this:


                   Should I also create a new Table Occurance of Invoice details, called Invoice details_Count that is related to my Customers table. ??

                   I am not sure which fields to link together. My Invoice details::fk_Order is the field that I wish to count how many unique numbers there are per customer..

                   Thank you

              • 4. Re: Count

                     If you look up aggregate functions in Help, you'll find that count ( field ) will return only two possible values: 1 or 0. That's because the function counts the number of fields listed  that are not empty. If FK_Order has a value it returns 1 if it does not, it returns 0.

                     On the other hand: Count ( RelatedTable::Fk_Order ) will count the number of non-empty fields in the set of related records.

                     To answer your other questions, I'd need more detail. Take it one sub set of related records to count at one time.

                     What is it that you want to count?

                     What values in what fields will distinguish these records from others in the table that you don't want to be part of this count?

                • 5. Re: Count

                       Hi Phil

                       I wan to be able to account the total number of orders that each customer has placed. The related invoice details table record that is created once an order is placed is not ideal, as this same record could be for a quote or an invoice. This is because some clients will straight out place an order but some may just request a quote - and this may never develop into a sale. The invoice details table has three further related tables  - quotes, orders and invoices. This just contain an invoice details FK, an auto serial field and date field. When the invoice details record if first created, a quote or order serial number is created and then set as a variable and copied back into the invoice details table into one of the three quote_fk, Order_fk or Invoice_fk fields.  So I cannot really do a count of the customer's related Invoice details records as they are not all orders. I have also created  a "type" field in the invoice details table that will contain the text, order, quote or invoice to help differentiate between records...

                       I want to have the total count displayed on the customers table...

                       Thank you!

                       Is that enough info? Attached is ERD.

                       PS put another post up a few days go regarding displaying results on a graph from two different variables....Did you see it as it has flumoxed me...


                  • 6. Re: Count

                         I wan to be able to account the total number of orders that each customer has placed.

                         Count ( Orders::fk_invoice details ID )

                         if defined in a calculation field in your customers (or did you call it clients?) table will count those and not count any invoice details records that are not converted into orders.

                         Variations of this can count your quotes and invoices.

                    • 7. Re: Count

                           So on the Invoice details table I should have a calculated field 

                      Count ( Orders::fk_invoice details ID )

                      And when on the Customers table I can just add this to it from the table occurance Invoice Details?


                      • 8. Re: Count

                             Sorry - misread what you said..

                             There is no relationship between the orders table and the customers table - I have the invoice details table between.

                             The customer PK is different to the Invoice details PK. Should these have been set up the same so that there is a direct relationship throughout the tables


                        • 9. Re: Count

                               This should be defined in the customers table, not the invoice details table.

                          • 10. Re: Count

                                 Hi phil

                                 wasnt sure if you saw my last post as I can't get the required count working from my contacts table. Think due to the relationship as per diagram and also the fact that the invoice details table is between the contacts and orders tables 

                            • 11. Re: Count

                                   No, this should work from customers and the "in between" table is key part of what you need in order to get the correct count.

                                   Unfortunately, "I can't get the required count working" doesn't tell me enough to suggest why it didn't work for you. You'll need to describe in detail what you did and what incorrect results were returned.

                              • 12. Re: Count

                                     Thank you for getting back to me and persevering with this!

                                     I created a calculated field in my customers table that reads Count(InvoiceDetails::fk_Orders). 

                                     The thing is, there is an option, once an order has been created, to create a deposit that is linked to the original order. I use variables from the original order to copy the valures over to the new invoice details record so that they have the same order number and other details. 

                                     To get around this I thought I would remove copy the fk_order value to a new fields and then clear the contents of the original fk_order field. This is done when the deist invoice is created, however, the new deposit invoice still gets counted as a new order. 

                                     I thought the count would count the total quantity of unique orders for a customer. SO although two invoice details records have been created (one order and one deposit invoice) the result should only count 1 as they have the same values in fk_order...


                                     I hope that makes some sense!

                                     Thank you

                                • 13. Re: Count

                                       But that's not what I suggested. I suggested:

                                       Count ( Orders::fk_invoice details ID )

                                       You don't want a count of the invoice details records. You want a count of the number of Orders records that link, via Invoice details to the current customer record.

                                  • 14. Re: Count

                                         Thanks. I did try that originally but there is no relationship between my customers table and the orders table 

                                    1 2 Previous Next