5 Replies Latest reply on Jan 6, 2013 1:59 PM by philmodjunk

    need quick advice on linking tables



      need quick advice on linking tables


           I would search this but it needs to be done quickly and I am a beginner, so I would greatly appreciate some input. I have a table of records that is currently accessible to the Admin, Guest, and a couple users with various privileges. I need to create another table that is similar to the original one but only shows some of the fields in the original table. Only this table will be accessible by one of the users and not the original table. I need to have a checkbox in the original table that users with the read/write privilege set can check or uncheck. If the record is "checked", I need to have this record show up in the second table that will be available to the user with access to only that table (read only). So, basically, users with read/write privileges and/or the Admin can select which records to display to the account that only sees the limited table. Is this the best way to do it or is there an easier way, and can somebody give me a general quick walkthrough?


           Again, I greatly appreciate any help on this.

        • 1. Re: need quick advice on linking tables

               Well you don't use one table to "show fields" from another table usually.

               Would it be correct that only certain records in your table should be accessible to your specified user? If so, there are built in methods you can use with Manage | Security to limit access to records on a record by record basis.

               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.

          • 2. Re: need quick advice on linking tables
            That is correct. One of my users will be able to specify which records are visible to another user by checking a box. The reason I thought I would need two tables would be that I may want to limit which fields in the table are visible to the specified user while all of the fields are still visible to the rest of the users. I will look at that section in the help and see if it will answer my question. Thank you.
            • 3. Re: need quick advice on linking tables

                   Hmmm, FileMaker can control access to records rather than fields via Manage | Security.

                   One work around that is very similar to what you originally described is to split a table into two parts. Make TableA, the parent record with all fields that can be accessed by all users. Make TableB, the child record with only a foreign key field plus the fields where you want to limit access.

                   The relationships between the two tables might look like this:


                   TableA::__pkTableAID = TableB::_fkTableAID

                   If you enable "allow creation of records via this relationship" for TableB in the above relationship, you can put fields from TableB on your TableA layout and interact with them pretty much as though all the fields are still part of TableA.

                   And now the Record Level Access control that I referred to in that FileMaker help article can be set up for TableB.

              • 4. Re: need quick advice on linking tables

                     Thanks for your reply. A couple things:

                     I created Table B, and created only the fields I want to display in Table B. I used the relationships tab under Manage Database and set the firlds in Table B to equal the corresponding fields in Table A. There are no records in Table B however. Should I import my .fmp12 file into Table B for this to work? Wouldn't the relationships be overwritten by this action? 

                     Your relationship equation above; where would I enter that? 

                     So basically, you're saying that my Table A layout doesn't even have fields from Table A, but Table B, and that is how Table B displays those fields? 


                     Thanks again for your help.

                • 5. Re: need quick advice on linking tables

                       What I posted identifies the match fields used for the relationship. See the first post of this link for a more complete explanation of the notation that I used: Common Forum Relationship and Field Notations Explained

                       Importing data will not alter relationships.

                       But if you have existing data in Table A that you need to copy into Table B, Import records can be used for that, but you need to be careful to update the correct match field in Table B when you do so or the newly imported records won't be properly linked to your Table A records.

                       Using the notation from my first post,

                       TableA::__pkTableAID = TableB::_fkTableAID

                       When importing from Table A into Table B, map __pkTableAID to _fkTableAID so that you have matching values. Once you are confident that you have the data properly imported, there will be fields in Table A that you can delete from that table as their now resides in Table B. Your layout will also need to be updated to show fields from Table B instead of Table A.

                       It will be a could idea to make frequent back up copies during this process just in case you find that what you did was not correctly set up and you need to try again.