5 Replies Latest reply on Nov 14, 2016 8:31 AM by philmodjunk

    Conditional COUNT of field in related table



      Conditional COUNT of field in related table


      I am creating a database for my firm that has two tables: Customer (Parent) and Invoice (Child). The aim of the database is to carry out various analysis. I would like to COUNT a specific field in the Invoice table that has a "Paid" or "Unpaid" answer to show management how many total 'Unpaid' invoices exist. This is equivalent to Excel COUNT(IF)

      I have tried using 'If' statements but to no avail. As a newbie I'm not sure where the correct approach would be using CASE and a variable or LOOP function.

      Thanks in advance for any advice.



        • 1. Re: Conditional COUNT of field in related table

          You can't use an If or case function in the parent child to selectively count related records in the child table. But you have a number of other methods that can produce those totals.

          You can define a summary field in the invoice table that counts the Invoice ID or other field that is never empty. You can then put two one row filtered portals to invoice on a customer layout with a portal filter that filters for "paid" or "unpaid" records. If you put the summary field inside these one row portals, they'll produce the count you want. This is a "display only" trick. You can't use these totals in other calculations or on other layouts.

          You can define relationships that match only to paid and only to unpaid invoices as well as by Customer ID. Then count functions can be put in calculation fields in Customer that counts your invoices.

          You can put your If or case calculations in the child table. Example: If ( InvoiceStatusFIeld = "Paid" ; InvoiceID ). This calculation field will be empty if the invoice is not paid. Now you can count this calculation field from customer and get a count of all paid invoices for that customer.

          You can do it all on the customer table by setting up a summary report where you see two totals, one is the count of all paid invoices for that customer and one is all unpaid invoices. You can use a script from the customer record that pulls up such a report for just that customer, it can even be popped up in a floating window if desired.

          ExecuteSQL can selectively count your records using a WHERE clause that specifies the invoice status or it can group the records by status and report both totals in the same field.

          (I'm throwing a lot at you and only general descriptions, if you want, pick a method and if you can't get it to work, post back here iwth questions about that method and I'll discuss that one method in more detail.)

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Conditional COUNT of field in related table

            I have a small solution to this based on Phil's suggestion above which i thought I'd share:

            I have two tables: Supplier and Sundry. I have a Payment field which is a checkbox based on a single "Yes" value in a value list. I have an 'Amount' Field, which is the cost on the sundry invoice.

            In the Sundry table I have a calculation field called "AmountIfUnpaid" with the following:

            If ( Payment = "Yes" ; 0 ; Amount )

            i.e. : If the Payment check box is checked, set the AmountUnpaid to 0. Otherwise set it to the amount listed in "Amount".

            I then have a "TotalUnpaid" Summary field which counts all the 'AmountUnpaid' Fields.

            This can then be listed on the Supplier's page. 

            It works for me, but do point out if you think anything about this is not quite right

            Thanks for idea Phil

            • 3. Re: Conditional COUNT of field in related table

              Hello Phil.


              I have a similar problem.

              I have a portal on my "main" table which shows related records from a child table "exams". Furthermore, the "exams" table is related to a table "modalities".

              Each portal row lists details regarding the exam which was performed with a specific modality (CT, MRI, X-Ray ...). I want to calculate the total number of exams for each record in the "main" table - I figured this one out, I used the function "Count ( Exams::Main_ID_FK )".

              However, I am stuck when trying to count the number of exams for each modality. From what you suggested earlier I think the executeSQL variant would be most appropriate since it should dynamically update when I make changes in the portal without having to perform another action (ok could perhaps use a script that is trigged when I change something in the portal but that sounds more complicated to me). However, I was not able to figure the correct use/syntax of the SQL function.


              Could you or anyone please help?


              • 4. Re: Conditional COUNT of field in related table

                Try something along the lines of


                ExecuteSQL ( "

                  SELECT modality, COUNT ( * )

                  FROM Exams

                  WHERE theForeignKeyForMainInExams = ?


                  GROUP BY modality

                  ORDER BY modality


                  " ; Char(9) ; "" ; MainTable::primaryKey


                • 5. Re: Conditional COUNT of field in related table

                  You could also use a summary field in the portal table and a set of one row filtered portals--another option mentioned years ago when I first responded here.


                  Relationships that match to only one modality are also possible with a bit of creative relationship design.


                  Be careful of ExecuteSQL using "Group By". There's a knowledge base article cautioning that such queries can be exceptionally slow to evaluate so test them on realistic numbers of records to be sure that you aren't going to get an unacceptable delay waiting for the calculation to update.