10 Replies Latest reply on Nov 5, 2012 12:29 AM by comment

    How to add a join table to an existing file

    pgrehan

      I am new to Filemaker and having been learning by doing. The only problem with this approach is that I sometimes recognize my mistakes long after I have made them. I recently realized that I wanted a join table between tables that were previously not directly related.

       

      The file currently has three tables relevant to my question: "Session" and "Payment" tables are each directly related to a "Client" table. I previously thought that I had no need to relate sessions and payments. I was wrong. (this is where I dope slap myself)

       

      To fix this, I want to create a join table between "Session" and "Payment", but I am concerend about the impact of doing so on the existing data (and relationships, scripts, etc) in my file.

       

      I assume I need to:

       

      1. Create a join table between sessions and payments.

       

      Do I simply delete the old relationship between Clients and Payments or create a new table occurance for Payments?

       

      2. Delete existing "payment" records and re-enter this data (assuming this will populate the join table)

       

      3. Update all scripts, portals, etc. to work with the new relationship.

       

      This "sounds" straightforward.

       

      Did I miss any steps? Any problems I need to avoid? Any hints or tips in working with join tables?

       

      I appreciate any guidance

       

      Thanks

       

      Patrick

        • 1. Re: How to add a join table to an existing file
          PSI

          Hi Patrick,

           

          Unless there is a lot more going on I see no need for a join table. You can have payments connected to client and session you just need the client ID and the Session ID in the payments table in order to see payments from either table. It is normal to show the payments a specific client has made and those payments are most likely entered in relation to a specific session.

           

          I join table is only required if you are in a many to many situation. i.e. one session can have multiple payment and payments can to be related to multiple sessions.

           

          John Morina

          CCQ-FM, Inc.

          • 2. Re: How to add a join table to an existing file
            pgrehan

            John

             

            Thanks for the reply. You raise a good point.

             

            Would I still be able to manage partial payments and payments for multiple sessions without a join table?

             

            You may have solved my issue. I want to be able to show what payments were made on session dates but it is not critical that I match the payments to the specific sessions. I am more interested in the running balance.

             

            Thanks

             

            Patrick

            • 3. Re: How to add a join table to an existing file
              PSI

              Patrick,

               

              Without having a better idea of how your system is setup i can't guarantee my suggestions. One questions about the payments, where is the total? Is it in the session? Would a specific client might a total due of xx.xx based on one or more sessions?

               

              In general payments that span over multiple sessions fit into a many to many instance where a join table would be useful...

               

              John Morina

              pgrehan wrote:

               

              John

               

              Thanks for the reply. You raise a good point.

               

              Would I still be able to manage partial payments and payments for multiple sessions without a join table?

               

              You may have solved my issue. I want to be able to show what payments were made on session dates but it is not critical that I match the payments to the specific sessions. I am more interested in the running balance.

               

              Thanks

               

              Patrick

              • 4. Re: How to add a join table to an existing file
                comment

                pgrehan wrote:

                 

                Would I still be able to manage partial payments and payments for multiple sessions without a join table?

                 

                If one session can have many payments, and one payment can cover many sessions, then you do need a join table between them.

                 

                However, if "it is not critical that I match the payments to the specific sessions" then by all means don't. It's not easy to set up and it provides an opportunity for data-entry mistakes (esp. when splitting a payment into individual sessions).

                • 5. Re: How to add a join table to an existing file
                  pgrehan

                  John and Michael

                   

                  Thanks for your replies. Been busy with paying work, so it took me a while to digest the responses to the original post. After reflecting on my database I think I may have identified a solution but wanted to check with seasoned FMPro designers to see if it makes sense.

                   

                  As mentioned previously, a client can have multiple sessions and may pay for those sessions on the same date (or the sessions) or at a seprate time. It is also possible that they may make a partial payment or even pay in advance. It is nice but not critical that each payment be matched to a particular session. The running balance is most critical. This was similar to what I posted before (I think).

                   

                  Here is what is new: I need to generate a portal that displays BOTH sessions and payments for invoices. I assume that if they are on separate tables that laying them out next to each other may not not look professional enough for business.

                   

                  Would the more elegant solution be to use a single table (let's call it transactions) and have each record include either a session, a payment, or both. If I need to have a user enter a payment (separate from a session) I can use a TO or portal filter to display only the relevant fields. I assume I would want each record to have a field that would identify it as a sessions, payment or both for easy filtering.

                   

                  Does this make sense? Is there a signficant drawback to this approach?

                   

                  Thanks in advance for your advice

                   

                  Patrick

                  • 6. Re: How to add a join table to an existing file
                    comment

                    pgrehan wrote:

                     

                    It is nice but not critical that each payment be matched to a particular session.

                     

                    That would be very easy - but what if the client has an outstanding balance of 3 sessions and he executes a payment that covers 2.5 sessions? Someone has to divide the payment into amounts and assign each amount to a session. If they pay in advance, that session may not even exist yet. As I said earlier, this is difficult to automate and error-prone if done manually.

                     

                     

                    pgrehan wrote:

                     

                    I need to generate a portal that displays BOTH sessions and payments for invoices.

                     

                    I am afraid that makes no sense to me. What would be the purpose of such animal and what would it look like?

                     

                     

                    pgrehan wrote:

                     

                    Would the more elegant solution be to use a single table (let's call it transactions) and have each record include either a session, a payment, or both.

                     

                    It depends on what data (fields) you need to record in order to describe each type. If a session is merely (or mostly) a transaction - i.e. a charge - then it might make some sense.

                    • 7. Re: How to add a join table to an existing file
                      pgrehan

                      Michael Horak wrote:

                       

                      pgrehan wrote:

                      I need to generate a portal that displays BOTH sessions and payments for invoices.

                       

                      I am afraid that makes no sense to me. What would be the purpose of such animal and what would it look like?

                       

                      It is not ususal to print out an invoice that includes a record of charges and payments that that looks something like this:

                       

                      Date Service Charge Payment Balance

                      10/2   123       $20       $20        $0

                      10/3   123       $20          0        $20

                      10/4   123       $20       $40        $0

                       

                      It would be possible to list the charges and payments on separate rows if necessary.

                       

                       

                      Michael Horak wrote:

                      pgrehan wrote:

                      Would the more elegant solution be to use a single table (let's call it transactions) and have each record include either a session, a payment, or both.

                       

                      It depends on what data (fields) you need to record in order to describe each type. If a session is merely (or mostly) a transaction - i.e. a charge - then it might make some sense.

                       

                      My session table has a variety of non-financial fields, but the payment table is essentially just set up to track dates and amounts of payments.

                      • 8. Re: How to add a join table to an existing file
                        comment

                        pgrehan wrote:

                         

                        It is not ususal to print out an invoice that includes a record of charges and payments

                         

                        I also get invoices that list charges and payments separately. But it can work either way. Note, however, that there is a complication when the invoice is not the first one issued to the client and the initial balance is not 0.

                         

                        pgrehan wrote:

                         

                        My session table has a variety of non-financial fields

                         

                        Well, if you want sessions and payments to live in a common Transactions table, then those fields need to be there -  you'd just leave them empty when the transaction is a payment. There is another method, where the session-specific fields would be in a sub-table with a one-to-one relationship to Transactions - but in most cases it's not worth the bother.

                        • 9. Re: How to add a join table to an existing file
                          pgrehan

                          Michael

                           

                          Michael Horak wrote:

                           

                          there is a complication when the invoice is not the first one issued to the client and the initial balance is not 0.

                           

                          I hadn't considered this. What is the complication? I would have assumed that I could create a summary field that represented the balance prior to the invoice (omitting the charges and payments in the current invoice). Am I mistaken? Is this not also a complication with the two (sessions and payments) tables?

                          Well, if you want sessions and payments to live in a common Transactions table, then those fields need to be there -  you'd just leave them empty when the transaction is a payment.

                           

                          If I use a single transactions table, couldn't I create a layout that displays a record with "just" the payment fields? I assume the session specific fields would be created but remain empty (and unseen).

                           

                           

                          I appreciate your help as I try to figure this out. If I understand your responses correctly, you are recommending that I have seperate session and payment tables connected with a join table and to list session charges and payments seperately on an invoice, but that using a single transactions table would be not be problematic (except for the two issues above?).

                           

                          Patrick

                          • 10. Re: How to add a join table to an existing file
                            comment

                            pgrehan wrote:

                             

                            What is the complication? I would have assumed that I could create a summary field that represented the balance prior to the invoice (omitting the charges and payments in the current invoice). Am I mistaken? Is this not also a complication with the two (sessions and payments) tables?

                             

                            A summary field can only summarize the found set or the related set. If the invoice is the found set of all yet uninvoiced transactions, then the previous balance is the sum of all transactions in the omitted set. In order to use it, you need to get it first - either by doing another find and "remembering" the sum, or by a separate relationship.

                             

                            A lot also depends on how many transactions a client will have overall, because there's a point where  summarizing the entire history of the client in order to get the previous balance becomes too slow.

                             

                             

                            I think at this time you should start experimenting and, if necessary, post any problems you come up against. That will also answer your other questions.