11 Replies Latest reply on Feb 21, 2014 9:02 AM by lijnbach

    Making records invisible, in all cases.



      Making records invisible, in all cases.


      I desperate need to make records invisible, in all cases.


      The database in Filemaker contains employees. As well “active” employees as “inactive” employees. (Inactive means they are not working for the company anymore).


      According our tax authorities we are obliged to keep these inactive records for seven years, but, they may not be changed in any case.


      I have no problem writing a script that selects only the “active” employees and use these records in a lay out, and the “inactive” employees in another lay out in which only viewing is possible.


      But, if somebody uses the button “view all” they can see and change also the inactive employees. The same problem with the standard search in the menu, they can find and change the inactive employees. (And also when they push the green button).


      I worked a lot with Access in the past, and in access this is a peace of cake. Just make a query and use the query for a lay out. I know Filemaker doesn’t have this function, but is there another way? (I like Filemaker, but to be honest, I miss the query function terribly).


      And, I also searched in the forum. I found similar questions, but not an answer that helps me to solve this problem. Also I don't want to dump the inactive records in another table, because sometimes "inactive" employees become "active" again. So, I want them all in one table.


      Can somebody help me out?


      Hans Lijnbach.


        • 1. Re: Making records invisible, in all cases.

               Sounds like you need to use manage security to ban access to inactive employee records.

               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.

               Note that if access to a specific record is not allowed for the current user, finds will automatically omit the "no access" records from the found set. If the user selects Show All or Show Omitted only, they can still bring up the records, but they will be covered by a "no access" grey screen. (And with FileMaker Advanced, you can use a custom menu to  keep even this from happening.)

          • 2. Re: Making records invisible, in all cases.

                 O.K. Thanks so far Phil, sounds logical.

                 But, it is the same user, as well for the active as the non active employees. The only thing is, in one lay out they can do everything (active employees), and in the other lay out (inactive employees) only 4 fields can be changed. (E.g. use the button from non active back to active). It is just that the "active" and "inactive" employees may not be mixed up in the same lay out, so the one who is responsible, can make no mistakes when she is very busy. (What happened in the past).

                 Would this be possible with security?



            • 3. Re: Making records invisible, in all cases.

                   Yes, you have multiple options. The simplest may be to make all "inactive" records those that can be read but not edited unless you are an administrator with a different privilege set.

                   You may want to put those "four" fields in a related table so that they are not locked when the rest of the record needs to be locked. But that depends a lot on details I'm not privy to just by reading your posts here in the forum.

              • 4. Re: Making records invisible, in all cases.

                     O.K. Phil, you helped me out a lot.

                     In a mean while I have been looking at the security. It would have been nice if the "record restriction" (e.g. read only) was connected with a lay out  instead of a user. But, as far as I can see now that is not the case. (The reason for such a connection would be that both, the "active" and "non active" employees are handled by the same person). If I make record restrictions to the table (In this case the table "Employees") there is a second person needed (Administrator) to put them back from "non active" to "active".

                     Perhaps is the solution in this case, that the person who is responsible, get two accounts. (Although it is done on a single user Mac specially for HRM and this perspose).

                     From the 4 fields that can be changed in case of an "inactive" employee, 3 of them are not so important. The most important button is the button to put them back from "inactive" to "active". (And if possible by the same person). 

                     Again, I like the record restrictions, and it would even be better if the restrictions also could be connected with a layout instead and/or a user.

                     Filemaker would be the best product for me, if they also had queries. I hope they will build that in some day. 





                • 5. Re: Making records invisible, in all cases.

                       You aren't dealing with single users on the Manage | Security dialog. you are dealing with groups of users--all the users assigned a particular privilege set.

                       And there's an option there that can be used to make a layout "view only" for users with a particular privilege set. And while not simple, the "lock expression" can include a calculation that checks the value of a variable set to the value of the current layout to determine whether or not the record is locked.

                       Another option is to use scripts to do an "end run" around the security settings.

                       To edit the one field or the small set of four fields, you could have a button that copies the current values to global fields in a small dialog or popover that opens to show the data in the global fields. The user can then edit the data in the fields and when they click a "save" button, a script set to "run with full access privileges" can use set field to copy the data back to the fields of the "locked" record.

                       In the case of your status field, you button can run a script with full access privileges to toggle the value between "active" and "inactive".

                       And another option is to refer to the value of a global field in your lock expression. If the global field is not in the same table, you can modify the value to temporarily unlock your records and then re-set the field to it's original value to lock them again.


                            Filemaker would be the best product for me, if they also had queries.

                       Well they do have queries to some degree. Take a look at the ExecuteSQL function, there's a surprisingly large number of things that you can do with it.

                  • 6. Re: Making records invisible, in all cases.

                         Dear Phil,

                         I am familiar with SQL functions, but I don't know how to get them executed in the own tables of Filemaker. And, does this mean I can use SQL functions to make restrictions to the table in a lay out? (So build a lay out based on the SQL restrictions and the SQL output? And get only the "active" employees?) (I used SQL for an external ODBC database but not for internal FM Tables). The power of e.g. Access is that is very easy to make a query (also with join functions) from an existing table in Access. And then build a lay out based on the output of this query. (All excluded records are not visible in the lay out, also not with the find function).

                         If I use a SQL function, FM always asks me for the external Database.



                    • 7. Re: Making records invisible, in all cases.

                           You are looking at the script step rather then the calculation function--a function first added with Filemaker 12 and not found in earlier versions.

                           It can't be used to define a record set directly for a layout like you can in Access and other applications, but it can produce a return separated list of ID's that can serve as the match field for a portal, and for many other uses. It's a new feature and could use a query builder much like Access uses to make it easier to create and test the results of the funciton.

                           And yes, there are times where I'd very much like to base a layout on a query rather than a table occurrence, but since that's not an option, the other methods that I listed can be used to produce what you need.

                      • 9. Re: Making records invisible, in all cases.

                             Hai Phil,

                             Sorry for my late response, but I am living in the Netherlands, so we have a time lap. I went to bed yesterday.

                             You helped me out in a great way, and gave me the best options I can choose from. I have to figure out what the best option is and get along with that.

                             And indeed, I have also used the SQL calculated function for FM. It is a pity we can't use it for lay outs. Perhaps FM will find a solution for this kind of problems some day.

                             Thank you for your effort,

                             With kind regards,

                             Hans Lijnbach 

                        • 10. Re: Making records invisible, in all cases.

                               You are welcome to post that as a feature request: http://www.filemaker.com/company/contact/feature_request.html

                               I'd like to see that as an option as well as be able to use a query as the data source for a "combo box" type value list as has been possible in Access for many years.

                          • 11. Re: Making records invisible, in all cases.

                                 Dear Phil,

                                 I have posted it on "feature requests"

                                 Thanks again Phil,

                                 Hans Lijnbach