1 2 Previous Next 17 Replies Latest reply on Aug 27, 2013 8:26 AM by philmodjunk

    Using the table relationship to restrict data going from Table A to Table B

    DRR

      Title

      Using the table relationship to restrict data going from Table A to Table B

      Post

           I am trying to solve what I thought was going to be a simple problem.

           I have a table A which includes an entire psychiatric practice's patient demographics. On a layout for that table, I am able to check a box that indicates which therapists have access to a patient's file. Using permissions, I was able to solve that pretty easily. When a therapist logs in, he or she only sees records she has been given permission to see.

           Where I am stuck is that because her layouts reflect back to the entire patient table A, despite being restricted on which patient's she can see, the whole patient table comes up when she goes to try to add a new document. In other words, if she creates a new patient and then goes to create a note for that patient, IDs of all available patients come up as choices to assign the new document to.

           I first tried to solve this by having  a second table occurance of that Patient table A, thinking I could somehow make that the therapist's table of patients and then use that as the reference of available patient's to make a new document for, but I have no way of figuring out how to have that table occurance only have those patients in it she has been given permission to see. 

           I then tried to make a whole new table, but couldn't figure out how to have it automatically populated with patients from Table A. I could use look ups for each and every field I guess, but I don't know how it would restrict which records go into that table. 

           Any ideas to get me started?

           Thanks

           DRR

        • 1. Re: Using the table relationship to restrict data going from Table A to Table B
          SteveMartino

               I guess the first question to resolve is why do all patient Ids pop up when adding a note?  Are notes a different table?  

               After creating a new patient, I would think you would want to commit the record first, apply permissions as above, then script adding a note so it will only add the note to that patient.  Maybe the notes could be added thru a portal.

               i would also think, thru custom menus that you have to remove all menu items and keyboard shortcuts that allow users to do things like add a new record and show all records.   Users would have to access these through buttons that first check their permissions.

          • 2. Re: Using the table relationship to restrict data going from Table A to Table B
            philmodjunk

                 Did you set record level access to permissions on that table to limit access on a record by record basis?

                 What do you mean by "IDs of all available patients come up as choices to assign the new document to". I'm guessing that you are describing a value list.

                 If so, you should be able to set up a conditional value list that only lists patients that "belong" to that therapist.

            • 3. Re: Using the table relationship to restrict data going from Table A to Table B
              DRR

                   Both of your thoughts and suggestions are excellent and I have to say I didn't think about those options. 

                   PhilModJunk you are correct in that it is a value list, referencing the Patient Table. I had thought I explored limiting the value list by a conditional variable, but must not have been doing it correctly, as I could never get it to work. I will look more closely at my access permissions to assure that I have set the limit to be on a record by record basis. That may solve the problem quite simply.

                   Steve Martino, yes notes are a different table and will link to the individual patient by their ID's. Those ideas about scripting and restricting how the new notes are made are excellent. I currently have a button that prompts one to make a new blank note, but hadn't thought about adding scripting to automatically make the connection to the current patient, thus eliminating the need for them to scroll through the list of patients in the patient table. 

                   Thanks. I will post back if I find something that I can make work so others may be able to use the information

              • 4. Re: Using the table relationship to restrict data going from Table A to Table B
                philmodjunk

                     For Record Level Access control: 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 description of how to set this up.

                     For conditional value lists, see the following links:

                     There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in FileMaker 12.

                     The last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list.

                     Forum Tutorial: Custom Value List?

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

                     Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

                     Hierarchical Conditional Value lists: Conditional Value List Question

                     Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

                • 5. Re: Using the table relationship to restrict data going from Table A to Table B
                  DRR

                       PhilModJunk: Thanks for the links. I am going through them one by one. First note. I did have access limited on a record-by-record basis, but I'm not sure if my calculation is the best. 

                       I have the checkbox series on a layout where the person who enters the patient name and demographics checks off who can access that record. For a therapist named "Paula," her access to records is determined by the calculation: PatternCount (Patient_Clinicians_who_can_access_checkbox; "Paula, LISW" ) = 1

                       That seems to work, but when she logs in and flips through patient files and one comes up she doesn't have access to, it shows the template with "No access" in every field. It would obviously be nice if it just hid those records completely.

                  • 6. Re: Using the table relationship to restrict data going from Table A to Table B
                    philmodjunk

                         Instead of:

                         PatternCount (Patient_Clinicians_who_can_access_checkbox; "Paula, LISW" ) = 1

                         Use either:

                         Not IsEmpty ( Filtervalues ( Patient_Clinicians_who_can_access_checkbox; Get ( AccountName ) ) )

                         or

                         Not IsEmpty ( Filtervalues ( Patient_Clinicians_who_can_access_checkbox; $$UserID ) )

                         In the second example, you'd use a script with Get (accountName ) to look up the correct value for $$UserID. FilterValues avoids cases where similar values might produce an incorrect result with patterncount.

                         To hide the "no access" records from a user, any find performed by them or by a script will automatically omit records for which they do not hav access. You can set up a script to perform a find when they first open the file or when they first access the layout to hide such records, then only Show All Records and Show Omitted Only can bring up such records. If you have FileMaker Advanced, you can use it to set up your layouts with custom menus that either do not have these menu options or where selecting them performs scripts that perform finds to find all records or show omitted only and thus keep the "no access" records hidden from view.

                    • 7. Re: Using the table relationship to restrict data going from Table A to Table B
                      DRR

                           Every time I start to get discouraged about this stuff, I get another bump of help. This forum is phenomenal and I appreciate your help PhilModJunk.

                           I tried using the: Not IsEmpty ( Filtervalues ( Patient_Clinicians_who_can_access_checkbox; Get ( AccountName ) ) ) for the record restriction and it resulted in her not having access to any records. I guess I am confused since it appears a bit circular. The checkbox field generates a text response. When her box is checked, the response is "Paula (last name), LISW" only and not any kind of permission. The permission is generated only by another script or function that would say, in essence, only allow the account "paula" (in her case) to see records checked with the box that produces the result "Paula (her last name), LISW" What am I messing up here?

                      • 8. Re: Using the table relationship to restrict data going from Table A to Table B
                        philmodjunk

                             The assumption behind

                        Not IsEmpty ( Filtervalues ( Patient_Clinicians_who_can_access_checkbox; Get ( AccountName ) ) )

                        Is that one of the values selected in the checkbox field exactly matches the text returned by Get ( AccountName). That may or may not be the case depending on how you set up the value list for the check box and the account names for your user.

                        • 9. Re: Using the table relationship to restrict data going from Table A to Table B
                          DRR

                               With all the help, I have been able to make it work so that when Paula logs in, she only sees her records and the records she isn't credentialed to see are not included. However, I'm back to my original problem:

                               I need to write a script that will somehow gather the ID of the patient she wants to create her first progress note on, create the new note, and apply that patient's ID to it. As was noted in the advice above, this will eliminate the problem of her having access to all records in the database, and simply makes a new note only for that particular patient. This seems like it would be simple, but I find myself stuck. Any thoughts would be appreciated.

                               Thanks

                          • 10. Re: Using the table relationship to restrict data going from Table A to Table B
                            philmodjunk

                                 Your original posts had me thinking that the problem was with allowing access to the correct records.

                                 Why is a script necessary to do that? I'll post one, but keep in mind that a portal can be used where the user creates new records in a notes table simply by entering that note into a text field.

                                 If the user is on a record for that patient, the following code can create a record in a related notes table linked to that record by patient ID:

                                 Set Variable [$PatientID ; value: Patients::PatientID ]
                                 Go to Layout ["Notes" ; (Notes) ]
                                 New record/request
                                 Set Field [Notes::PatientID ; $PatientID ]
                                 //here you can leave the user on this layout for recording the note or the user can return to the original layout and use a portal to notes to enter that info into the new blank record this script has just created.

                                 And the above script can be set to run with "full access privileges" if that is needed to work with your privilege set settings if they would otherwise keep this script from working.

                            • 11. Re: Using the table relationship to restrict data going from Table A to Table B
                              DRR

                                   You are correct in that I wanted to solve two problems. I'm wondering if your definition of a "note" is different than mine. When I say note, I am meaning a completely new document connected to that patient. Once the first one is made, the next ones will just be added as records in that table. I'll attach an image of the Patient (start) page where the demographics are placed, diagnoses etc., and a "note" template just for clarity. I guess I'm thinking that your solution about the portal may not work as well (?). I don't know. 

                              • 12. Re: Using the table relationship to restrict data going from Table A to Table B
                                DRR
                                /files/26682d168a/2.png 1280x898
                                • 13. Re: Using the table relationship to restrict data going from Table A to Table B
                                  philmodjunk

                                       You are describing what I had in mind. Creating a new notes record in a related table of notes. If your note is simple, a portal can be used. If it is complex, the script that I posted can be used to go to a different layout and create a record in that layout's table which is linked to the current record in the patient table.

                                  • 14. Re: Using the table relationship to restrict data going from Table A to Table B
                                    DRR

                                         OK. Thanks for clarifying. I was able to use your script and it is working quite nicely. Thank you very much for putting in the time to help me out. The only minor snaffu is that if I test it out and make a few "new" progress notes, everything seems to fill (Patient ID and Name) nicely and as expected. However, if I click back to the original document or the "Patient" page, then click back to the accumulated progress notes, one will have the name erased or appear blank, even though the ID remains in place. The other notes in the collection appear to still have the name. Strange. 

                                    1 2 Previous Next