3 Replies Latest reply on Mar 21, 2011 12:22 PM by philmodjunk

    Joint Table



      Joint Table


      I have just started using FileMaker. I am stumped in a many to many situation as depicted below. The payment table has a primary key for each payment. The session table has a primary key for each session. A payment can go to multiple sessions. A session can receive payments from multiple payments. I actually also have a table that is what the join table ( session payment table) should look like meaning it has the six  records listed below with a foreign key linking it to both parent tables. I basically imported all historical data from Bento via Numbers.  This historical data has been entered into FileMaker.  The session table has been populated and the payment table has been populated. The payment table at this point contains the records that should be in the session payment Join Table. The session payment table however is empty and I am not sure how to populate it with records that reflect history.


      Any help would be greatly appreciated.


        • 1. Re: Joint Table

          Well, that's not how your join table should work. A record in a join table should link a single payment to a single session. To link one payment to several sessions, you need a join record for each session that all link to the same payment.

          Thus, your session payment table might have records like this:

          Each row is one record in the join table

          Session Payment Fraction Payment
          1 60 1
          1 100 2
          1 300 3
          2 40 1
          2 200 2
          3 200 3

          (Seems odd that you didn't apply all of payment 1 to session 1, then all of payment 2 to session 1, then split payment 3 over all three sessions to show them as paid in full, but that's a business issue, not a database issue.)

          Here's a many to many demo file you can examine. The two main tables are named "contracts" and "companies", but if you rename them "sessions" and "payments" you'll have the structure you need here. Note how portals to the join table are used in this demo file and feel free to follow up with more questions after you examine this file.


          • 2. Re: Joint Table

            The numbers I used are arbitrary and don’t reflect the reality of what I from a business perspective.


            What you have laid out as the join table I have already imported into the payment table. In other words, I have the equivalent of the six records that you had mentioned, right now, resident in the payment table (which, by the way, is really about 13,000 records).  The question I have is, what portion stays in the payment table ( and what would be the architecture in that? for example would the full payment one ($100) show up as one record?) and what portion goes into the join table and what portion stays in the session table (would the charge of $460 stay as one record in the session table?) Do I define the parent tables as having the primary keys and the join table as having the foreign keys?  Do I have to manually import into the session payment table?  Do I get that through portals? Complete newbie here. sorry.


            Really appreciate your taking the time out to answer this.


             By the way I couldn’t open your demo file. Any instructions that you can give me on that?

            • 3. Re: Joint Table

              Sounds like you imported data into the payments table that should be imported into the join table. Once that data is imported, you could use a script to produce matching records in either payments or sessions if you don't have data to directly import into those tables.

              Were you able to download the right file? (Click blue the download button, ignore the other buttons on the download page, wait 20 seconds for download link to appear, click it to download the file.)

              Each record in payment should represent one payment. Referring to the join table example, you would have 3 payment records with amounts of 100, 300 and 500 respectively. The join table links a given payment record to a given session record and then specifies what fraction of the total payment is applied to the bill for that session.


              Sessions::SessionsID = Session_Payments::SessionsID
              Payments::PaymentID = Session_Payments::PaymentID

              As you'll see in the demo file, you can place a portal to the join table to list data from both the join table as well as the other related table. A portal to Session_Payments on a payments layout can list all sessions to which the payment was applied. A portal to this same table on Sessions will list all Payment records linked to that session. Comparing the session cost to the total of Session_Payment payment fractions will tell you if a particular session is "paid in full" or not.