5 Replies Latest reply on Feb 3, 2015 6:48 AM by MatteoPirelli

    Need your wisdom on a relationship and export issue

    MatteoPirelli

      Title

      Need your wisdom on a relationship and export issue

      Post

      Good day all.

      I have this problem that I'm stuck with from quite some time now, without a feasible solution. I'll try to be as concise as possible.

      I have three tables: subscriptions, users, magazine issues. Every subscription is tied to a "company_code", and every user has a "company_code". You can have n subscriptions for a given company_code and users on a given company_code. Some sample data:

       

      USERS

                                                                                                                             
      company codeemail
      A000001johndoe@mail.com

       

      SUBSCRIPTIONS

                                                                                                                                                                                                                             
      company codemagazinestart dateend datesub. type
      A00000101MAG01/01/201501/12/2015YEAR

       

      MAGAZINE ISSUES

                                                                                                                                                             
      magazineissue_codepublication_date
      01MAG01MAG1500031201/02/2015

      the final outcome I need is a csv file with, on each line:

      email, magazine, start date, start issue, end date, end issue

      The issue I'm running into are the following:

      - the "email" field doesn't get repeated on every line but just on the first occourrence  

      - don't know why but i get a lot of empty records with just the magazine_issues for issues that are not  part of the subscription (i have attached a screenshot of the exported file to give you an idea)

      I also have attached my relational structure. Is it correct for what i want to achieve? Does anyone has a solution for the non-repeating values for related records?

      Thanks in advance for your time.
      Matteo

       

       

      records.png

        • 1. Re: Need your wisdom on a relationship and export issue
          philmodjunk

          Why is the match field called "company code" (or just code as shown in your screen shot)?

          This field neither identifies the user nor the subscription given this statement:

          You can have n subscriptions for a given company_code and users on a given company_code

          That statement describes a many to many relationship where one user can have many subscriptions and a given subscription can have many users. But that isn't really consistent with your relationship screen shot.

          It looks like a user can have many subscriptions, but if these are printed hard copy magazines, it would make more sense if a given subscription can only be linked to a single user.

          So I can't quite parse out all the details, but you a re correct to focus on the relationships. If you have a one to many relationship as I am guessing, you can successfully export your data from the context of the "many" table. If you have a many to many relationship, you need a join table and then you would export your data from the context of the join table.

          What you show, is typical of trying to export data from the context of the "one" side of a relationship.

          • 2. Re: Need your wisdom on a relationship and export issue
            MatteoPirelli

            Sorry I'm kinda new and I' having a hard time grasping some concepts. Regarding you statement:

            it would make more sense if a given subscription can only be linked to a single user

            In reality, any given subscription is linked to a single company. Then a company can have any given number of users.

            What I need is a way to display, for each user, all the subscriptions he/her is entitled to because his/her company has it

            Would you mind giving me an example of how you would build the relationship? I don't get this part:

            If you have a many to many relationship, you need a join table and then you would export your data from the context of the join table

            Thank you for your patience

            • 3. Re: Need your wisdom on a relationship and export issue
              WBSimon

              Create a new layout from the subscriptions table instead of the users table. You can populate it with all the information you need. 

              • 4. Re: Need your wisdom on a relationship and export issue
                MatteoPirelli

                 

                I tried that, Brent, but now i get this output when exporting:

                The first line is good, the second and third line should've been identical except for the email field, because this is the output for a single subcription for a company with 3 users.

                I have attached the sample fmp12 file I'm using, if someone could take a quick look at it, I'll be eternally grateful (username is admin, no password needed)

                 

                • 5. Re: Need your wisdom on a relationship and export issue
                  MatteoPirelli

                  I made some progress.

                   

                  The relationship graph is this one:

                  I've added two calculated fields to the "subscriptions" table: start_fasc and end_fasc (starting and ending issue of the sub), which i get from the relationship with the "issue" tables.

                  This way I can get an almost correct file. 

                  Output when I export from the users table:

                  You see, now I'm missing just the email field on every other record

                  Output if exported from the "subscriptions" table:  

                  Now you see I'm missing the details of the subscriptions

                  I feel I'm getting close to the solution, any hint?