1 2 Previous Next 23 Replies Latest reply on Apr 17, 2012 9:41 PM by AlejandroAlanis

    Field Repetition Rule

    AlejandroAlanis

      Title

      Field Repetition Rule

      Post

      Hi guys,

      I'm doing a DB for my dad, he's a doctor and he wants to have all patient files and notes... everything.

      I have patient table with: patientID, name, lastname, evrything... and phone table with phone_name and phone_type, and patientID (as FK from patient table), the fields values for phone_type are: [Home 1 | Home 2 | Office 1 | Office 2 | etc. ]

      My question is how I can make that for a patient, if I choose phone type Home 1, and then in other field, choose it again Home 1, says that that value is repeated, but when I change to another patient I could choose again Home 1 without restriction.

      In Phone table ID's speaking:

      patientID | phone_type

      ----1---- | --Home 1

      ----1---- | --Home 2

      ----2---- | --Home 1

      ----2---- | --Office 2

      etc...

      But to restrict when I insert the phone type value if I repeat Home 1 for patientID 1, to make a message saying that is repeated and you need to change it, or something like that.

      Thanks guys.

      Sincerely,

      Alex Alanis

        • 1. Re: Field Repetition Rule
          bumper

          Perhaps you have a field validation set for the phone type field to only allow unique entries?

          I am also assuming that you have a field for the actual phone number and that each one of the lines shown above is a separate record within a separate table for phone numbers. If not then you need to restructure your file something like this:

          Patient Table               PhoneNumber Table

          pk_pt_ID--------------<fk_pt_ID

          Patient tName             PhoneType

          etc.                          PhoneNumber

          You can set the relationship to allow for auto creation of a record from the Patient Table in the PhoneNumber Table. This allows you to have as many phone numbers for a patient as you need, be it one to ten.

          If all of this is confusing, then you can do search on relational databases, etc. in this group or go through the material that came with FMP. Some of the databases that come with the program actually use the capabilities of FileMaker, but the Contact Management file does not.

          • 2. Re: Field Repetition Rule
            philmodjunk

            I think the issue here, is that for a given patient, you want to limit each record so that any phone type is used only once for that patient. Define a text field with this auto-entered calculation: PatientID & PhoneType

            Now you can specify unique values for this new field and you will get an validation error message if you try to select say Home 1 a second time for the same patient. (PS. Not sure why that's so important. These "type" labels rarely need to be validated in this format as they are usually just a useful label and values such as "home", "Cell", "office", "Email" are all you really need. (I often include email addresses in phone number table.)

            • 3. Re: Field Repetition Rule
              AlejandroAlanis

              thanks guys, that's what I was saying to the client, that there are useful labels, and it may be better to put, office, home, cell, etc... without numbers like home 1, or something like that.

              I have another problem, I have a patient layout with all the information and I have a notes layout with noteID_PK, patientID_FK, diagnostic, comment and treatment, I added a button in the patient layout to going to notes, and I just made it with go to layout "notes", and in "notes", I made a button to add a note with new record, my problem is that when I push new record, the PatientID disappear and I want that note to relate with the patient that I'm seeing in his layout.

              Example, if I'm seeing patient # 325 and I push notes, and in notes push new note, want to still seeing the patientID 325 in the note layout without doing it manual.

              Thank you so much.

              Sincerely,

              Alex Alanis

              • 4. Re: Field Repetition Rule
                philmodjunk

                Assuming you have two tables, Patients, Notes that are related like this:

                Patients::PatientID_PK = Notes::PatientID_FK

                Set your button up with this script:

                Freeze Window
                Set Variable [$PatientID ; PatientID_PK]
                Go To Layout [Notes]
                New Record/Request
                Set Field [Notes::PatientID_FK ; $PatientID]

                From here you can either use Go To Layout [original layout] To return to your layout or you can leave the user on the notes layout depending on what you want to happen next. (Freeze window isn't needed if you leave the user on the notes layout.)

                You can also place a portal to Notes on the Patient layout and simply log notes by entering text into the bottom blank row of the notes portal. This requires enabling "Allow creation of records via this relationship" for Notes in the relationship between these two tables.

                • 5. Re: Field Repetition Rule
                  AlejandroAlanis

                  Wow Phil you're a master, it worked like a charm, I have other doubt, in notes layout I want to add a backward and forward buttons to see past notes, but when I did it, it shows me all notes for all PatientID, so how I can limit the buttons just to see just the notes just for the patient I'm working on.

                  Thank you so much for all your help

                  Sincerely,

                  Alex Alanis

                  • 6. Re: Field Repetition Rule
                    philmodjunk

                    I take it that you want to see the notes on a notes layout...

                    From the Patients layout add a button to run this script:

                    If [Notes::PatientID_FK // check to be sure at least one note record exists for this patient]
                       Go To Related Record [Show only related records; From table: Notes; Using layout: "Notes" (Notes)]
                    End If

                    Now on your Notes layout, clicking the pages of the "book" control or dragging the slider beneath it will enable you to see other notes recods--but only for this patient as long as you don't perform a find or use one of the "Show" options in the Records menu to bring up other Notes records. You can also add buttons that use go to Record/Request/Page to flip through your records or even jump to the first or last note record for this patient.

                    (It's also possible to set up a single row notes portal on your patient layout and set up buttons and scripts to step through the notes in the portal by manipulating the portal's filter.)

                     

                    • 7. Re: Field Repetition Rule
                      AlejandroAlanis

                      My only problem is if there isn't a note for that patient I can't enter to the notes of the patient via that button. My idea was to make a script like this, I don't know if it can be done, but something like this:

                      If

                      ID_Note_PK is empty, set the doctor name in his respective field in notes table (and it will generate a noteID_PK number automatically right?)

                      else

                      Go To Related Record [Show only related records; From table: Notes; Using layout: "Notes" (Notes)]

                      I made the portal on patient to see all notes, and the doctor will know if he have notes or haven't.

                      Thank again

                      Sincerely,

                      Alex Alanis

                      • 8. Re: Field Repetition Rule
                        philmodjunk

                        The script can be updated to do that.

                        If [Notes::PatientID_FK // check to be sure at least one note record exists for this patient]
                           Go To Related Record [Show only related records; From table: Notes; Using layout: "Notes" (Notes)]
                        Else
                           Set Variable [$PatientID ; value ; Patients::PatientID_PK
                           Go To Layout ["Notes" (Notes) ]
                           New Record/Request
                           Set Field [Notes::PatientID_FK ; $PatientID]
                           Enter Find Mode [] //clear pause check box
                           Set Field [Notes::PatientID_FK ; $PatientID]
                           Perform Find[]
                        End If

                        Performing the find isolates the newly created record in a found set of one record. I did not include steps to enter a doctor name as I don't know from your description where that info is recorded. If it is recorded in the Patient record, there is no need to also enter it into the Notes record, your notes record can display data from the related Patient record, you can simply add the fields from the Patient table directly to your Notes layout.

                        If NoteID_PK is define as an auto-entered serial number, each new record will receive a unique number automatically each time a new record is created.

                        • 9. Re: Field Repetition Rule
                          AlejandroAlanis

                          Hi Phil, thanks for your script, I change the structure, because that way it wasn't working, I modified to this:

                          If[IsEmpty(notes:PatientID_FK)]
                             Set Variable [$PatientID ; value ; Patients::PatientID_PK
                             Go To Layout ["Notes" (Notes) ]
                             New Record/Request
                             Set Field [Notes::PatientID_FK ; $PatientID]
                             Enter Find Mode [] //clear pause check box
                             Set Field [Notes::PatientID_FK ; $PatientID]
                             Perform Find[]
                          Else
                             Go To Related Record [Show only related records; From table: Notes; Using layout: "Notes" (Notes)]
                             Go To Record/Request/Page [Last]
                          End If

                          Is working perfect. I almost finish this, but I have 2 problems/doubts again, I hope last 2.

                          First, on medical_record table, I have a field called allergies and I want to make a square draw on notes layout, now if the allergies filed is empty, is ok, but how can I make it if the allergies record have something written, I want that square appear in notes layout, in other color from the background, is there any way to to that?

                          And Second, this have a) and b).

                          a) I have a doctor table, in notes I will put the doctor name to know who was the doctor that made that note and timestamp, first I want to related the doctor table that have his ID and his name, with the username that the admin give to him, example: if userID is JohnD, related with his ID 18, and in notes layout, appear the name of the doctor 18 "John Doe"

                          b) Last, how I can lock a note after 24 hours a doctor created that note.

                          Thank you again for all your help.

                          Sincerely,

                          Alex Alanis 

                           

                          • 10. Re: Field Repetition Rule
                            philmodjunk

                            For your "square", you can use conditional formatting on a "do nothing" button.

                            1. Use the button tool to place a square button on your layout.
                            2. Use the effect drop down at the top right corner to "no effect" to flatten the button into a simple square or rectangle.
                            3. Right click it and use conditional formatting with this expression:
                            4. IsEmpty ( Medical_Record::Allergies )
                            5. To change the color to match the layout background and thus "hide" it from view when there are no patient allergies.

                             

                            a) How do you determine which doctor is making the note? Will each doctor be issued their own account name and password?

                            b) You can use Manage | Security to lock records. You can set up this expression to lock records more than 24 hours old:

                            ( Notes::CreationTimeStamp + (24*3600) ) < get ( CurrentTimeStamp )  // 24 * 3600 is the number of seconds in 24 hours

                            See "Editing record access privileges" in FileMaker Help and check out this particular sub section: "Entering a formula for limiting access on a record-by-record basis" for a detailed description of how to set this up.

                            • 11. Re: Field Repetition Rule
                              AlejandroAlanis

                              Square is Working perfect, thanks

                              a) Well after migrating the old DB, there is a Doctors table with the Doctor_ID and Doctor_Name fields, and are related to Notes Table and Diagnosis Table. but yes, they will have their own account name and pw too.

                              What I want is for example: when doctor account johnc creates a Note, check on Doctors Table, which is the ID of johnc and use that ID instead making a new one. So maybe making a new field in Doctors Table with his username, and compare it, the problem is I don't know how to use the ID that is designated for that doctor instead creating a new one, each time the make a new note.

                              b) Waiting to receive the timestamp note from the old DB to try it.

                              Thanks again for everything

                              • 12. Re: Field Repetition Rule
                                philmodjunk

                                If it is always the Doctor recording their own notes, (Not a nurse or other assistant logged in under a different account name and password), then you can set up an auto-enter option that looks up the Doctor ID from a table of Doctor Account names and IDs. I'd set up a table for this with the Doctor's name, a serial number field for Doctor IDs, the Doctor's account name and then fields for any other info about that one doctor that needs to be recorded in the database.

                                Then you can define a text field in notes to auto-enter the user's account name when a new record is created (On creation | Name on the auto-enter tab in Field Options).

                                That enables you to set up this relationship:

                                Notes::AccountName = Doctors::AccountName

                                With that relationship, you can also add an ID field to notes that uses a looked up value setting to copy the Doctor's ID number from the related Doctors record.

                                • 13. Re: Field Repetition Rule
                                  AlejandroAlanis

                                  Ok, I made it work.

                                  Now the problem is after a doctor generate a note, there are patients that have appointments with more than one doctor, and all doctors need to see all notes generated from more than one doctor of one patient. In my relationship Notes - Doctors, I have: Notes::AccountName = Doctors::AccountName AND Notes::Doc_FK = Doctors::Doc_PK, but it isn't showing the doctor name on notes when I review a notes from a Patient

                                  • 14. Re: Field Repetition Rule
                                    philmodjunk

                                    That's not the relationship I suggested for entering the Doctor's ID and it's not the one to use for displaying the notes either. The relationship between patients and notes should only be based on the PatientID. The relationship that is used so that the looked up value setting on the Doctor ID field works should only be based on the account name.

                                    1 2 Previous Next