5 Replies Latest reply on Apr 12, 2011 9:32 AM by philmodjunk

    Many-to-Many Relationship

    crtopher

      Title

      Many-to-Many Relationship

      Post

      I need help setting the tables and relationships for this scenario. Sorry, it's complicated!

      I need to keep track of cases I do. The cases have patients, and each patient has a health fund. The health funds are grouped into health fund groups for the purposes of their rebates. I charge the patient a fee for a service, and the patient is variably rebated some or all of that fee by a mixture of government and health fund rebates. 

      The patient's fees are worked out according to 2 general types of schedules. The first type of schedule sets an amount in dollars for a certain service item (lets call it a fixed amount schedule - FAS). The second type is called a relative value schedule (RVS) and for each service item it sets a unit value (UV) (eg 6 units) and then an overall unit price that applies to every item (eg $10). Any case has a mixture of these two types of item. 

      The health fund groups have their own 2 schedules that determine how much they will rebate patients for the fees i charge. The government has its own 2 schedules. I set my fees according to my own 2 schedules.  The schedules are all the same structurally but differ in either the unit price charged/rebated (for the RVS) or the dollar amount for each item (FAS).

      The RVS table would have RVS_ID; RVS_ItemCode; RVS_ItemUV; RVS_ItemDescription

      The FAS table would have FAS_ID; FAS_ItemCode; FAS_DollarAmount; FAS_ItemDescription

      Associated with the RVS table there would be a unit price - indeed the RVS table is essentially identical for me, the funds etc, with the only difference being an external one (the unit price). The FAS schedules are also pretty similar except that each record in the different schedules would have a different dollar amount.

      Every case would have a variable number of each type of item (RVS or FAS) and each item could of course be involved with any number of cases - so I'm thinking a many to many situation that would involve a linking table. 

      What I need to do with every case, and here's the complicated tricky part but i can't work out, is to select a number of these different type of items, select the patients health fund, select from a number of different billing options (eg my own schedule, or I might choose to bill according to the health funds own schedule so that the patient will be rebated exactly what he's been billed, or even just plonk in a total dollar amount regardless of the items), and have an idea of both my total fee, and also the amount the patient will be rebated so I can work out what gap payment (if any) they will need to make. So really I need to calculate two total amounts (one for me, one rebated) for every case.

      The total fee would look something like this: (Total Units x Unit price) + Total Dollar Amount  (to take into account the two types of item RVS and FAS involved)

      I might stop there and see whether anyone can make any sense of what i've just typed! Thanks in advance

        • 1. Re: Many-to-Many Relationship
          philmodjunk

          Presumably only one schedule is applied to a given fee billed to a given patient? (I don't see any way to combine schedules for a specific fee.)

          Seems like you could treat an FAS as an RVS, but with only one unit per item. The math would seem to work out correctly here and that eliminates having two tables for two types of schedules here.

          • 2. Re: Many-to-Many Relationship
            crtopher

            Hi Phil - thanks for replying.

            You're right and I did think of combining the RVS and FAS schedules into one table - this could work. With regard to the patients bill, your're right, they would only be billed at one schedule rate but i need to also calculate what the govt/funds will rebate via their schedules so I can work out the patients gap payment. 

            I do have a specific question about my layout - I want to be able to filter a value list that one of the fields on the portal uses to get its values, based on a selection i make on the main layout. The list looks up a third table (items) which contains the items; another field on this third table categorises the items into billing groups (eg full fee, govt rebate etc). I know I would have to put a reference to this field on the main layout that contains the portal but am unsure how to link it all up.

            Cheers, Chris

            (ps should i re-post this as a new question?)

            • 3. Re: Many-to-Many Relationship
              philmodjunk

              You may have seen the first two links on conditional value lists before:

              Custom Value List?  (Forum tutorial)

              http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list (Knowledgebase article)

              http://www.4shared.com/file/f8NsU2DJ/ConditionalValueListDemo.html (Demo file you can download and experiment with--includes more info on how conditional value lists work and demos a trick with check boxes and conditional value lists.)

              • 4. Re: Many-to-Many Relationship
                crtopher

                Hi Phil Thank you so much for your time - your third link there is brilliant!. I got several great ideas out of it - hiding the ID number by putting the non-enterable display field on top (why didn't I think of that!), finding out that pop ups also hide the id number, and finally, using checkboxes for the category field to select several categories. 

                Thanks again!

                Cheers, Chris

                • 5. Re: Many-to-Many Relationship
                  philmodjunk

                  "hiding the ID number by putting the non-enterable display field on top"

                  Can't take credit for that innovation, I spotted it in a starter solution and have been using it ever since as a better alternative to pop up menus.