4 Replies Latest reply on May 1, 2017 6:15 PM by abruehl

    Auto Generating Content Entered into One Layout into Another Layout


      Hi all, I think I'm pretty remedial when it comes to FMP, but here goes. I'm working on creating a table to keep track of my finances. I work in the healthcare field, so sometimes I get paid in full right away (cash) or sometimes I get a co-pay and then, a few weeks later, a check from the insurance company.


      I currently have a table ("Daily Money") set up that tracks patient visits with the following fields:

      Patient's Name

      Date of Treatment

      Amount Paid Cash

      *Amount Paid Insurance

      *Date Insurance Check Received 


      I pay myself monthly, so I'd like to do a monthly summary of how much I got in cash and how much I got in insurance. What I've worked out currently is not working because it doesn't go by the date of insurance payment, but rather by the date of initial treatment. In other words, if I saw a patient in March, but received the insurance check in April, it's putting all that money in March instead of March and April. Also, this data won't even show up until April, after I've already paid myself for March.


      So, then I was thinking of creating a different layout that's just for insurance, but still entering all of the data into the Daily Money table. So, I want some of the data from Daily Money (that with asterisks above) to automatically fill the Insurance table. In other words, it would automatically create records with duplicate data every time I fill those fields in on Daily Money. Then I could separately summarize that.


      I've already created parent child relationships between these two layouts and now I think I need to create a script, but I have no idea how.


      Also open to suggestions on how to make the entire process easier! Thanks!

        • 1. Re: Auto Generating Content Entered into One Layout into Another Layout

          It's not just a different layout that you need, but a related table. This can be table of all payments received both as cash and from insurance checks. These records can link to your current table to show the treatment date and other details, but with separate payment records for each payment received and a date received field, you can sort your records by month and then by payment type to get a sub total in cash, sub total of insurance payments as well as then a monthly total of the two combined. This requires a single summary field placed in different sub summary parts. your report can list the individual payments or just have one row for each each type of payment and its subtotal.


          And here's a month calculation that can allow you to easily group payments by month:


          PaymentReceived - Day ( PaymentReceived ) + 1


          Select a date result type. It calculates a date for the first day of the same month as PaymentReceived so it gives all payments of the same month the same value so that sorting can group them together, yet sorts into correct chronological order.

          • 2. Re: Auto Generating Content Entered into One Layout into Another Layout

            Thanks, philmodjunk


            I'm not sure I follow this though. I think that maybe I'm doing some of these things already but then definitely missing others. Here's what I have so far:

            Screen Shot 2017-05-01 at 3.38.14 PM.png

            So, starting on the left upper part, that's the data I fill in every time a patient comes in. The left lower part is what I fill in when I bill insurance and when I receive an insurance check. All of the stuff on the right are calculations (determining credit card fee, paying my mentor (Russ), paying myself (Alex), and putting money into savings for taxes).

            This is what I'm working with right now and am unable to do a sort where I can get the monthly totals for what I need to pay myself, my mentor, and my savings account (because insurance payments are being sorted by the Treatment Date (Tx Date) and not by the date the check is received (Ins Paid Date).


            This is how the sort is set up at the moment. Please don't pay too close attention to the individual fields as I'm pretty sure some of them are correct and some aren't (and some are missing). I keep starting and stopping this project and it's a bit of a mess at the moment.

            Screen Shot 2017-05-01 at 3.52.11 PM.png

            I was trying to do a leading sub summary sort for the cash and a trailing sub summary sort for the insurance, but this is not working correctly.


            Finally, here's what I have for the relationships. I definitely do not feel confident in this!

            Screen Shot 2017-05-01 at 3.44.48 PM.png


            I believe that your month calculation is what I have for the MonthSort field. The way I have MonthSort set up at the moment is this calculation: Month ( Tx Date_Pk )&" "&Year ( Tx Date_Pk )  I am very open to change though!


            Not sure any of this makes sense or if what I have above is so off-base that you won't be able to make sense of it, but I appreciate all of your help.


            Thank you!

            • 3. Re: Auto Generating Content Entered into One Layout into Another Layout

              Doesn't like what I am suggesting much at all.


              The relationship between your table of patient visits would have exactly ONE match field on each side of the relationship and there should be a portal where all types of payments are entered on your layout.

              • 4. Re: Auto Generating Content Entered into One Layout into Another Layout

                Ok, thanks. I think I don't have enough background knowledge to understand this.