7 Replies Latest reply on Sep 16, 2014 8:58 AM by philmodjunk

    Relationship to filter views between two tables.

    sccardais

      Title

      Relationship to filter views between two tables.

      Post

      I want to create a subset list of Users based on two tables:

             
      • Users (List of all past and present customers, trial subscribers)
      •      
      • Surveys (list of all Users who have completed a Satisfaction survey)

      The two can be linked by UserID. Starting with the Users table, I want to filter out the people who have completed a Survey within the past 12 months. The Survey Date is in the Surveys table.

      How can I filter my view to see only Users that have not completed a Survey in the previous 12 months? Can this be done with a simple relationship between the two tables? How would it be structured?

      The ultimate goal is to create a new list each month that filters the Users list to omit Users who have submitted Suveys in the previous 12 months and further filters the list by other criteria that I know how to do.

       

       

        • 1. Re: Relationship to filter views between two tables.
          philmodjunk

          In Users, define a calculation field, cLast12 as:

          Let ( T = Get ( CurrentDate ) ; Date ( Month ( T ) - 12 ; Day ( T ) ; Year ( T ) ) ) //Text in blue might be replaced by the value 1

          Then this relationship:

          Users::UserID = Surveys::UserID AND
          Users::cLast12 > Surveys::SurveyDate

          will match to all users that did take a survey over the last 12 months. If you perform an Omit find on the Users layout where you specify an asterisk in the Surveys::SurveyDate field, you'll get a found set of all users that have not recently completed the survey.

          • 2. Re: Relationship to filter views between two tables.
            sccardais

            Thank you.

            Since Surveys is a subset of Users, should the relationship be based on Users:UserName=Surveys::UserName AND Users::c_Last12≥Surveys::SurveyDate?

            Is the formula for c_Last12 essentially the date 12 months from the current date?

            Thanks.

             

            • 3. Re: Relationship to filter views between two tables.
              philmodjunk

              Is the formula for c_Last12 essentially the date 12 months from the current date?

              Yes, this computes a date 12 months in the past. Whether you need to compute that accurate to the day or just the first of the month is your call to make given the needs of your system.

              I made a last minute change to the relationship definition you may have missed if you are using email to read my responses as the change was made after I posted.

              You match by UserID (never by name here, too much chance of duplicate names!) as well as by this calculation field.

              And as I re-examine that post, I see I have an inequality backwards as this relationship matches to the users that did fill out a survey over the past twelve months....

              Users::UserID = Surveys::UserID AND
              Users::cLast12 < Surveys::SurveyDate

              • 4. Re: Relationship to filter views between two tables.
                sccardais

                I'm a little confused about the relationship between Users and Surveys.

                The two tables are now related based on two match criteria:

                Users::UserName=Surveys::UserName AND Users::c_Last12 ≥ Surveys:: c_Survey_Date

                Considering the relationship, I expected to see only related records on this layout which is based on Users as shown on the bottom diagram from the Relationships graph.

                I feel like this is a fundamental misunderstanding on my part. Give the relationship, why am I seeing unrelated records on this layout?

                 

                • 5. Re: Relationship to filter views between two tables.
                  philmodjunk

                  To repeat, you should not match records by name. It's far too easy to get a mismatch due to duplicate names or names that were misspelled. In your original comment, you mentioned a "userID" field common to both. This should be an auto-entered serial number or UUID value in the user table--not a name.

                  Users::UserID = Surveys::USerID

                  should limit the relationship to just surveys by a given User record.

                  By including: a second pair of fields that match by date you then match only to records by a given user meeting the specified date criteria.

                  Users::UserID = Surveys::USerID AND
                  Users::cLast12 < Surveys::SurveyDate

                  Should give you a match to all surveys by a given user that are less than 12 months old. This is to identify the users you want to exclude from your found set.

                  A few more details. Make sure that your fields that calculate a date have "Date" specified as the result type. Then c_Last12 must be defined as an unstored calculation field while c_Survey_Date cannot be unstored. Why is it calculation field here? (assuming that c_ identifies it as a calculation field) It should be a simple date field that records the date during which the survey was completed.

                  • 6. Re: Relationship to filter views between two tables.
                    sccardais

                    Thanks.

                    I agree with you about the Username vs unique, auto created serial number but I'm working with an external database that uses the Username as a unique identifier and I can't change that. I do check for duplicates though. So far, none.

                    I'll check that the calculation results are as you described.

                    C_SurveyDate is a calculation based on a time stamp of the survey date. It may not have been necessary. If necessary, I'll make the changes you suggest.

                    Thanks again.

                    • 7. Re: Relationship to filter views between two tables.
                      philmodjunk

                      As long as C_SurveyDate is indexed, the fact that it's a calculation shouldn't be an issue. My concern was that if it referenced a field in a related table, a global field or Get ( CurrentDate ) in order to calculate the date, it might not be a field that can be stored/indexed and thus couldn't be used in this relationship as a match field.