8 Replies Latest reply on Dec 12, 2013 2:06 PM by BillGreen

    related records by date range

    BillGreen

      Title

      related records by date range

      Post


           I'm stumped on what I thought should be fairly straight forward, and I'm sorry if this has been addressed elsewhere. I need to report on responses to email campaigns. My trouble is between 2 related tables:

           table 1, each record contains:

           campaign_ID: text

           email_ID: text

           emailSendDate: date (mm/dd/yyyy)

           nextSendDate: date (mm/dd/yyyy)

            

           Table 2, each record contains:

           campaign_ID

           responseDate: date (mm/dd/yyyy)

           other response data

            

           The relationship is based on campaign_ID, which is unique for every campaign but not for every email in the campaign. One of my goals is to see, over a period of time, which email version is performing better.

           I've tried a portal in table 1 where the relationship is defined as:

           campaign_ID = campaign_id

           I'm filtering the portal with:

           table 2::responseDate >= table 1::emailSendDate and table 2::responseDate <= table 1::next_sendDate

           The portal displays the same data in every record of table 1 - the data for record 1. I thought that the AND criteria would filter the portal results based on the email send dates.

            

           I've tried to remove the portal filter and set up the criteria in the relationship but I'm not getting anywhere. I don't have the relationship set up correctly and need some advice, please.

        • 1. Re: related records by date range
          philmodjunk

               I would make sure that your date fields are all really fields of type date and not text. Then double check the design of your layout and portal. Both the portal filter you describe should work.

               But please note that when using a filtered portal, editing the value of one of the fields referenced in the portal filter does not automatically update what records will appear in your portal. If you think that may be why this doesn't appear to work for you, perform a script with this script step after editing such a field:

               Refresh Window [Flush Cached Join Results]

               That isn't a step I usually recommend because it can really bog down screen updates in some circumstances, but it's an easy way to test for this issue. If that turns out to be the only issue, we can set up a relationship that won't need this added script step to get the same results.

          • 2. Re: related records by date range
            BillGreen

                 Thanks Phil.

                 The nextSendDate in table 1 is a calculation returning a date: [If (frequency = 0; emailSendDate + daysFromLaunch; emailSendDate + frequency)]... frequency and daysFromLaunch are number fields.

                 In table 2, the responseDate is a calculation returning a date: [Date ( Month(Date) ; Day(Date) ; Year(Date) )] where the field "Date" is a timestamp formatted as " 11/1/2013 12:00 AM "

                 Could my problem be related to any of this? I ran the script you suggested but no change. BTW, using FMP 12 advance, Mac OS 10.8.5. Anything else I can provide that might help?

            • 3. Re: related records by date range
              philmodjunk

                   Are any of these calculations unstored calculations? Unstored calculations can't be used on the "far" side of a relationship as a match field.

              • 4. Re: related records by date range
                BillGreen

                     responseDate in table 2 is indexed - index all, but nextSendDate in table 1 is unstored, referencing an unstored calculation field " daysFromLaunch ". I changed daysFromLaunch to index all, then was able to changed nextSendDate to index all, flushed the cache but am still not seeing what I would expect to see.

                     table 1 is sorted by emailSendDate, table 2 is sorted by responseDate, the portal showing records from table 2 is sorted by responseDate.

                • 5. Re: related records by date range
                  philmodjunk

                       What do you see in the portal when you remove the portal filter and just have it show the related records? (keep Campaign ID as the only match field for this test.)

                  • 6. Re: related records by date range
                    BillGreen

                         I see all records in the related table by campaign_ID in the portal in each record in table 1

                    • 7. Re: related records by date range
                      philmodjunk

                           Which confirms that your basic relationship and the portal/layout design is working. I was rather expecting there to show an issue with your layout/portal set up here, but that appears to be ruled out now.

                           Taking a new look at: Table 2::responseDate >= table 1::emailSendDate and table 2::responseDate <= table 1::next_sendDate

                           Is your layout based on Table 1 or Table 2?  Check Layout Setup | Show Records from. The name selected there should exactly match one of the two table occurrence names (the name to the left of ::) shown in your portal filter expression. Then check Portal Setup | Show Related Records from and see what name appears there. It should show the other of the two table occurrence names used in your portal filter.

                           IF that is not the case for either your layout or your portal, that would explain why the portal filter is not evaluating correctly.

                      • 8. Re: related records by date range
                        BillGreen

                             Oh wow. You are good. The portal filter expression referencing table 1::yada was incorrect... a different relationship.