3 Replies Latest reply on Jun 22, 2010 9:19 AM by philmodjunk

    Merge Payment and Session Table



      Merge Payment and Session Table


      Hello forum,


      I have a client database that I am creating.  The main tables are Session, Payment, and Client.  Session is just basically an appointment table.


      My question is, I need a way to look at all the sessions and payments for a client.  I would like to see this in a list view.


      So basically I would like a transaction table.  In this way I could show a list of both transactions, (payments and sessions).   






      FMP Advanced 11

      Mac OSX 10.6.4

        • 1. Re: Merge Payment and Session Table

          Are clients required to pay for sessions on a session by session basis, or could one payment be applied to several sessions?

          • 2. Re: Merge Payment and Session Table

            Thank you!  I thought no one was going to reply.  Often times a cash client will pay in-session, but they also might pay for the current session and previous sessions if they have missed a payment.  Client covered by Insurance is a little different.  Insurance checks come in and could be for multiple clients and multiple sessions.  


            So yes one payment can be applied to several sessions.

            • 3. Re: Merge Payment and Session Table

               I thought no one was going to reply.  


              Well you posted after 5 PM PST, on  a Friday. Many of us don't access the forum over the week end... :smileywink:


              I think I follow what you want. You can have many sessions to one payment record, so you need a way to list all the relevant sessions to which a given payment is applied. (I don't think you need to support allowing a client to make multiple payments for a given session, but that is also supported by this model.)


              This relationship would seem to work:  Sessions---Transactions>----Payments


              Your transactions table would have two fields: PaymentID and SessionID


              Payments:: PaymentID = Transactions:: PaymentID

              Sessions::SessionID = Transactions:: SessionID


              On your Payment layout, you can include a portal to Transactions with a drop down for SessionID so that you can select applicable Sessions by selecting one such session in each row of the portal as needed. (You can use a two column, conditional value list for this field).


              On a Sessions based layout, you can place fields from the Payment and transactions tables to show payment informaiton relevant to that session.