3 Replies Latest reply on Dec 9, 2016 10:11 AM by TimothyRamos

    Populate Join table for billing cycle


      I am designing a solution for my therapy practice. The contracts I have allow for billing to occur twice a month (15th and the 30th).I would like to be to write a script that will allow me to create an invoice and search out and display all unpaid therapy notes. I have a Therapy_Notes (TN) table and Invoice (In) table which are joined by an Therapy_Invoice Table.


      I would like to do the following:

      1. Build a script that will take the Parent ID from the Invoice Record

      2. Perform search for all the unpaid therapy notes and grab all relevant Therapy Note ID's

      3. Lastly, go to the Therapy_invoice Join table and input all Relevant Therapy note ID's and single parent Invoice ID.


      I have been able to do the following:

      Create the new invoice, Set the variable to the invoice ID, goto therapy note layout and perform search for all eligible therapy notes.


      I am having trouble with:

      capturing all relevant therapy note IDs and creating/populating the necessary records in the therapy_invoice Join table.

        • 1. Re: Populate Join table for billing cycle



          Thank you for your post!


          I am going to move this thread from the FileMaker Community Feedback Space, which is specifically for input on the Community itself, to the Discussions Space where you should receive more views and potentially more advice on this topic!



          FileMaker, Inc.

          • 2. Re: Populate Join table for billing cycle

            Why do you need the join table?


            From your description, it appears that you have this relationshp:




            I make that conclusion because you describe searching for Therapy_Notes by Invoice ID.


            A join table would enable you to link one Therapy_Notes record to more than one Invoice. Is that something that you actually need to do here?


            Seems to me that once you have that find performed, all you need is a layout based on THerapy_Notes where each record is a row in a list view with fields from Invoice included in the header and possibly a footer or trailing grand summary to be able to print out an invoice for that client.

            1 of 1 people found this helpful
            • 3. Re: Populate Join table for billing cycle

              Thank you. Sorry for the late response. I went with your advice and scrapped the invoice system I had going. Your method simplified the process.