8 Replies Latest reply on Aug 13, 2015 3:15 PM by sccardais

    Count Related Records Multiple Criteria

    sccardais

      Title

      Count Related Records Multiple Criteria

      Post

      My question is about creating a relationship between two tables to count the number of related records based in multiple criteria.

             
      • Table A has fields "email_address" and "Date" and "g_Constant" (set to "Client") and "ID"
      •      
      • Table B has fields: "email_address" and "Status" and "ID"

       

      For records in Table A with Table A::Date < 12 months ago, I want to count the number of records in Table B with an email address that matches records in Table A and with Table B::Status = "Client."

      The email address is not unique in either Table but the "ID" field is unique in each.

      Summary: Starting with a subset of records in Table A (Date < 12 months ago), how many records are there in Table B with a matching email address AND with Table B::Status = "Client."

      I'm using FMPA v 14.0.1

      How should I do this?

        • 1. Re: Count Related Records Multiple Criteria
          philmodjunk

          So you perform a find to produce a found set of records on a Layout for Table A, then need the count of related records in Table B over all the records in this found set....

          Define a calculation field in Table B with a number result type:

          Count ( Table B::ID )

          Define a summary field in Table B that computes the total of this new calculation field.

          And now you are done. You can perform any find you want on Table A and this summary field will give you the count of related Table B records across the entire found set of records. It will not be a fast calculation. You may have to wait if there are a large number of records in your found set and related table, but it should work.

          This assumes the following relationship:

          Table A::email address = Table B::email address AND
          Table A::g_Constant = Table B::Status AND
          Table A::ID = Table B::ID

          • 2. Re: Count Related Records Multiple Criteria
            sccardais

            Just to clarify - the ID field in both tables is unique to each table. Basically, they are unique serial numbers created in each table when records are created so any match would be coincidental.

            Assuming I create a new TO (TableA_TableB), the summary field created in Table B should be placed on a layout based on the new TO? Correct? 

            For what it's worth, I'm scratching my head about why the calc and summary fields go into Table B instead of Table A. Is there an easy answer?

            Thanks for your help. 

            • 3. Re: Count Related Records Multiple Criteria
              philmodjunk

              Just to clarify - the ID field in both tables is unique to each table. Basically, they are unique serial numbers created in each table when records are created so any match would be coincidental.

              I caught that they are unique, not that they are independent values. That actually clears up some rather nagging questions that had been echoing in the back of my mind as I posted my previous comment.

              They should not then be part of the relationship and you should only use the other two pairs of match fields.

              Table A::email address = Table B::email address AND
              Table A::g_Constant = Table B::Status

              It may even be possible to only match by email address if you you modify your find criteria to also specify that Table A records have at least one related Table B record with a Status of "client".

              • 4. Re: Count Related Records Multiple Criteria
                sccardais

                I have not been able to get this relationship to work yet. I’m sure it has something to do with Context.

                 

                I’m trying to count the number of records in table USERS that have the same email address as a found set in table SURVEYS. I've simplified this a little by removing the 2nd criteria "Status=Status". 

                 

                TO in SURVEYS relates to USERS with this relationship - SURVEYS::email_address = USERS::email address.

                 

                In USERS, I created a calc field, c_Count_Org_ID (unique field).

                 

                Also in USERS, I created a summary field that Totals c_Count_Org_ID

                 

                Is this the right structure?

                 

                To get the count, should I put the summary field in a layout based on SURVEYS or USERS?

                 

                When I place the Summary field on a layout based on Surveys, I get a crazy result - more than the total number of records in USERS.

                 

                What am I doing wrong here?

                • 5. Re: Count Related Records Multiple Criteria
                  philmodjunk

                  You've got that backwards.

                  The calculation and summary fields need to be defined in Surveys if you want to create a found set of surveys records and get a count of Users records related to the records of that found set. The calculation field counts the related users for one such survey record and the summary field then totals up these counts across the entire found set.

                  But isn't there only one user for a given survey record? That would seem to eliminate the need for the count function. And could there be several surveys with the same email address in your found set? And would you then want to count the same user twice? I wouldn't think so and that will require a change to this approach if my guesses are correct here.

                  What version of FileMaker are you currently using? (I can think of a trick that will only work in FileMaker 13 or newer that would avoid counting the same user twice and another method that can work for FileMaker 12 or newer only...)

                  • 6. Re: Count Related Records Multiple Criteria
                    sccardais

                    I must have misunderstood your directions in your first reply yesterday. Or - more likely - I didn't explain our situation clearly.

                    We are using FMPA 14.0.1

                    Yes - each Survey record comes from on Username.

                    Yes, several Surveys could have the same email address. This is because our customers often share email addresses. We cannot change this behavior.  We do not want to double count them. Good catch!

                    Until now, we have been using the Username to exclude people who submitted surveys in the previous 12 months from our monthly invitation list.  e.g. If we have a Survey from Username x within the past 12 months, that Username is excluded from the invitation list. But, since users share email addresses, even Users who completed a survey in the previous 12 months sometimes get another invitation - intended for another User who happens to be sharing the same email address.

                    We want to understand the effect of changing the exclusion criteria from Username to email address.

                    The relationship between Surveys and Users based on email address should help us quantify the effect of changing the criteria to email address.

                     Would love to hear about the approach you mentioned that would get around the double counting problem.

                    Thanks again for your help. 

                    • 7. Re: Count Related Records Multiple Criteria
                      philmodjunk

                      FileMaker 13 added a new type of summary field, the "list of" summary field. This summary field will produce a return separated list of values from the specified field over the total current found set. Such a return separated list of values makes the perfect "Multi-value key" for matching to records in another table. But a summary field can't be used as a match field in a relationship so we have to add one more field to the table in order to get the needed relationship to work.

                      Define sEmailList as a "list of" summary field that "lists" your email field. Then define a calculation field cEmailList with a text result type and enter the name of our new summary field, sEmailList as the sole term for its calculation.

                      Now we can use cEmailList as a match field to link your Survey Table to an occurrence of the Users table by linking it to the email field in the users table. And now a count function defined in the Survey Table or a summary field defined in the User table can return the total number of user records related to your found set of survey records.

                      Hmm, but I was assuming only one user record with the same email address. I was speculating that two survey records with the same email would identify the case where a user took the same survey twice. Your latest post suggests that what you really want is the number of unique emails found in your current found set and there are other methods for getting that unique count that do not require a relationship to you users table at all.

                      • 8. Re: Count Related Records Multiple Criteria
                        sccardais

                        Thank you, Phil.

                        Regarding your last paragraph, could I use the ExecuteSQL function to create a list of unique email mail values in Surveys?

                        Something like:

                        ExecuteSQL ("SELECT DISTINCT ResponderEmail FROM Surveys";"";"")

                        and base the relationship on that list? Do you think that would work and avoid double counting?