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

    Merge Payment and Session Table

    deliciousapple

      Title

      Merge Payment and Session Table

      Post

      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).   

       

      Cheers!

       

      Gene

       

      FMP Advanced 11

      Mac OSX 10.6.4

        • 1. Re: Merge Payment and Session Table
          philmodjunk

          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
            deliciousapple

            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
              philmodjunk

               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.