1 2 3 Previous Next 33 Replies Latest reply on Jun 30, 2017 6:40 AM by gofmp15

    How can I create tables with multiple keys

    liverpool101

      Sorry but I'm a newbie. Maybe this has been asked before.

      I'm trying to create a healthcare application and I need to be able to create the structure below:

       

      Patient Table - Primary Key MRN #

      Visit table - Key Date within MRN

      Orders Table - sequential starting at 1 (auto increment by 1) within visit date within MRN

       

      eg

      MRN 123

               visit 03/21/2015

                      order 1

                                2

                               3

       

           visit 04/12/2016

                    order 1

       

       

      mrn 6354

             visit 05/25/2015

                    order 1

                             2

       

      how can I accomplish this?

      Any help would be greatly appreciated

      (BTW I'm on Version 11 because I still run XP (and so do my clients))

      Thanks.

        • 1. Re: How can I create tables with multiple keys
          coherentkris

          SWAG ( stupid wild a$$ guess) but the op is tryIng to figure out how to solve the potential many to many between visits and orders.

           

          Date can be an attribute of a visit but should never be a key field in visits.

          If its left as a key field your doctor could only conduct one visit per day.

          Visits should have a primary key of its own.

          Visits should have a foreign key relationship to patients primary key

          • 2. Re: How can I create tables with multiple keys
            liverpool101

            These are CLIENT requirements. BTW I have been in Healthcare IT for 30 years total

            (going back to 1973). I realize it's old technology but it IS what the client is asking for.

             

            I just thought someone could help.

            I guess not.

            • 3. Re: How can I create tables with multiple keys
              philmodjunk

              Help is available, but they are asking questions that you should be asking your client. It's not always in you or the client's best interest to do what they try to require you to do. That's a tricky dance to make with many clients, but telling them that what they want to do has a serious downside is part of the expertise that they are paying you to provide to them.

               

              What you want is quite possible in FileMaker 11, it just doesn't look like the best approach. Explaining why you want to set it up this way may change our opinion, or we may make a counter suggestion that outlines a better approach.

              • 4. Re: How can I create tables with multiple keys
                coherentkris

                Hardware/OS/FM version aside...

                 

                If you have been in healthcare IT for that long (as a programmer/database engineer hopefully) YOU should be the one that decided the schema.

                 

                Is the client a software/it technology firm or healthcare industry?

                 

                when you are talking about medical records there may be laws involved that you will get tangled up with if you do something wrong.. the customer made me do it won't hold much weight if the customer holds you responsible for the design.

                • 5. Re: How can I create tables with multiple keys
                  liverpool101

                  It's for a hospital trying to duplicate an old Mainframe COBOL subsystem

                  from the 1990's.

                  After all the flames I've received I'll probably just walk away from this project/client.

                  • 6. Re: How can I create tables with multiple keys
                    coherentkris

                    flames not intended.

                    I was just trying to get more info so that you could get to a solution.

                    PS why would they want to duplicate a Model T Ford when they could get a new Camaro SS?

                    • 7. Re: How can I create tables with multiple keys
                      liverpool101

                      Sorry, I thought the diagram would explain the structure I was looking for.

                      I don't make the decisions for the hospital but apparently it's a system

                      they use every day and apparently they can't duplicate it using Epic

                      (which they're replacing the mainframe systems with).

                      • 8. Re: How can I create tables with multiple keys
                        philmodjunk

                        Please don't use the responses here as your reason to walk away. Base that decision on your working relationship with the client. If they aren't willing to be reasonable, then walk away for that reason.

                         

                        While you certainly shouldn't try to replicate the data models from a system that old in FileMaker and I definitely would not use FileMaker 11 to do so, you may need to replicate a certain measure of the current system's "look" in your interface design to ease transition. That doesn't require creating the "keys" that you described as part of the data model, but you may need them, at least at first as "labels" for helping users get to speed with the new system. The difference between a label and a key:

                         

                        A Key links tables in a relationship. Changing the value of a key, thus changes what records if any are linked in that relationship. A Label is not used as a match field in a relationship, but can be used as a way to sort and search records. Since there's no relationship involved, editing a label's value simply affects how that one record is searched and sorted.

                         

                        By the way guys, while I wouldn't call them "flames", there is a strong "dose of vinegar" in some of the replies here. It's a free country, but you might try be a bit gentler in your responses here and elsewhere in this forum when pointing out than the poster has presented some ideas that don't seem ideal for what they need. We want more people enthusiastically creating solutions in FileMaker, not less because they came to this forum and got chased away by negative responses.

                        • 9. Re: How can I create tables with multiple keys
                          liverpool101

                          Thanks. Like I said, I'll probably walk away from this.

                          (FYI I'm 65 years old and have been a Software Developer since I was 18 (1970)).

                          • 10. Re: How can I create tables with multiple keys
                            beverly

                            I'm so sorry you've gotten less than helpful responses. You may click the Actions link under any of the posts and Report Abuse (and I believe you can Delete replies).

                             

                            What you've asked should be possible. FileMaker has entry layouts and report layouts. I would think what you showed was a Report from 3 different tables. We may need to know what you know about FileMaker to proceed. We may answer with more questions, or reply with answers to basic FM skills that you may already know, or reply with suggestions to get training and/or work with a seasoned developer nearer to you (some can act as tutors, if needed). But rudeness should never be acceptible.

                             

                            Have you made a FileMaker database with the 3 tables:

                            Patients

                            Visits

                            Orders

                             

                            You might find you'll need an interim table (called a join table) sometimes between any two tables.

                            Will you be getting the data from the old system in any particular format (CSV, for example)? Perhaps these will help with the structure of your database (in what you need and where).

                             

                            Beverly

                            • 11. Re: How can I create tables with multiple keys
                              liverpool101

                              Sorry. I'm a total FM newbie trying to learn from the manuals.

                              Based on the replies I've received (except for yours)

                              I think I'll just pass on trying to learn/use FM.

                              • 12. Re: How can I create tables with multiple keys
                                liverpool101

                                Yes there was. IBM developed SHAS (Shared Hospital Accounting System)

                                back in 1965 and used to bundle it with their computers.

                                • 13. Re: How can I create tables with multiple keys
                                  siplus

                                  In software development, there is a moment you can get lost.

                                   

                                  It's when you let the client decide on anything else except "what".

                                   

                                  The client has only one corridor he can walk: the one going from "what data I have" to "what data I want".

                                  And some cameo appearance.

                                   

                                  Everything else , be it HW, SW, implementation, layouts, etc is your stuff. Rule it.

                                   

                                  Otherwise you'll be like a doctor who prescribes the meds your patient wants because has seen on Internet, based upon rules he read on a blog.

                                   

                                  You have a history backing you up, use it.

                                  • 14. Re: How can I create tables with multiple keys
                                    philmodjunk

                                    Yet how you say it is just as important as what you have to say. You can piss off a client by needlessly insulting them--losing the income and referrals you might have received from them or you can politely, but firmly provide the expertise you were paid to provide. If the customer rejects that advice, then you make the decision to walk away and part on much more respectful terms in the process.

                                     

                                    The same applies for how you advise someone seeking assistance in the forum. If you respond too harshly, they stop responding to you and aren't helped. Soften the tone a bit and they may listen better to WHY you are disagreeing with their premises.

                                    1 2 3 Previous Next