3 Replies Latest reply on Jan 23, 2017 8:03 AM by philmodjunk

    Newsletter subscription management via FM 15 Pro Advanced - stuck on list of issues per subscriber

    argoron

      I am trying to manage subscriptions and distribution of newsletter issues via FM 15 Pro Advanced.

      The problem I'm facing is on the following point: inside the layout for each subscriber I'd like to include a list of all published newsletter issues,
      and see whether the subscriber has received or not each individual issue.

       

      I have three tables - Subscribers, Issues and Mailings. The latter is a join table between Subscribers and Issues and has the following fields:

      • SubscriberID (link to Subscribers);
      • IssueID (link to Issues);
      • MailingDate

      That table will give me the issues that the subscriber has already received, but I do not know how to also get hold of the issues the subscriber has not yet received. I'm quite new to FM and any help on this would be greatly appreciated.

        • 1. Re: Newsletter subscription management via FM 15 Pro Advanced - stuck on list of issues per subscriber
          philmodjunk

          You've gotten off to a good start, but here's a way to re-arrange these tables in a relationship that will list all issues and then an object in the portal row can indicate whether or not that issue appears in the join table for a given subscriber.

           

          Subscriber::anyField X Issues|All::anyfield

           

          Issues|All::__pkIssueID = Mailings|All::_fkIssuesID AND

          Issues|All::gSubscriberID = Mailings|All::_fkSubscriberID

           

          You can put a portal to Issues|All and get a list of all issues. You can place a picture object--such as an image of a "tick mark" in the portal row and give it this Hide Object When Expression:

           

          IsEmpty ( Mailings|All::_fkIssuesID )

           

          The final details:

           

          gSubscriberID is a field with global storage. It needs to be assigned the correct value via a script. You set up the OnRecordLoad trigger to run this simple script:

           

          Set Field [Issues|All::gSubscriberID ; Subscriber::__pkSubscriberID ]

          • 2. Re: Newsletter subscription management via FM 15 Pro Advanced - stuck on list of issues per subscriber
            argoron

            Thanks for the quick answer ! Unfortunately there's quite a few concepts in your response I don't (yet) manage to get my head around for being really new to FM, and I'm afraid it'd take me forever before I manage to implement this. I was however thinking about the issue and wanted to run an alternative idea by you and have your thoughts: each time a new issue or a new subscriber is created, one of two scripts would run automatically:

             

            New Issue

            - go to issues table

            - set a variable with id of new issue

            - go to subscribers table

            - set variable (array ?) with value of the summary field of list of existing subscribers IDs

            - go to join table

            - loop through values in subscriber ID list, and add entry with: issue_id, subscriber_id, distribution_timestamp (empty)

             

            For new subscribers it would be the same process, just inverting issues and subscribers of course.

             

            Thus, I could quite easily filter for values with timestamp filled (have been sent) and timestamp empty (not sent yet).

             

            Would that be a feasible solution, too ? I've never played around with scripts in FM. Could the described above be scripted ?

             

            Thanks in advance for your help !

            • 3. Re: Newsletter subscription management via FM 15 Pro Advanced - stuck on list of issues per subscriber
              philmodjunk

              Yes it could be scripted and doesn't necessarily replace my previous suggestion--which isn't as complex as you might think. What you describe here is actually more complex.

               

              Note that nearly any serious work work with FileMaker requires a good grasp of relationships (my suggestion) and a good working grasp of how to create and use scripts (your idea).