7 Replies Latest reply on Nov 14, 2011 3:05 PM by Ramki

    How to have only one record in a table?


      Hi All,


      Please help me.


      I have a preference info table, where user will specify their name and address and other one time entry info. I would like to have only one row in this table. There is a layout attached to this table which capture the information. I do not want user to add more than one row to this table. It is a kind of control table.




        • 1. Re: How to have only one record in a table?

          You could handle this by prompting the user until this one time info is entered via your startup script or when the access the area of the database you are requiring this control information.  Once the information is entered, you set a simple boolean flag in a number field such as flagNeedPrefs.  If you need the Preferences set, you go in, create a record and give them a layout to enter this information in a form view, once they set it the flag gets switched and it doesn't prompt them to enter them again.


          If this preference data is related back to another record, such as a Customer Table you can always create a calculation that counts the related records via that relationship and if its more than 1, it prompts the user and tells them a preference record has already been created for this relationship, please edit it here and take them to the record and your form view layout.

          • 2. Re: How to have only one record in a table?

            You can create a privilege set for the account to disallow creation of new records for the table.

            • 3. Re: How to have only one record in a table?



              Or do you mean 1 and only one record per user?


              Like a preference file. If this is the case then you need to script record creation. How I do this .....

              1. capture user ID, in my case Filemaker User account

              2. go to preference file and do a find for the user

                   a. if found take user to their preference file record

                   b. if no record is found create a new one and get the data from the user.

              3. upon closing the preference record I run my settings script to update the changes made.


              Hope this helps,


              1 of 1 people found this helpful
              • 4. Re: How to have only one record in a table?

                Hi Ramki,


                A neat way to handle this requirement is to capture the user's account name into a field and use it as a key field in a relationship to the PrefInfo table, matching to an account name field in the "foreign" table.


                One effective way to set up the key field would be to use an unstored calculation (text result) with the formula specified as Get(AccountName).


                If the layout you provide for users to edit their details is based on the table where the key field is located, and all the fields you place on it are sourced via the relationship that connects the key field to the PrefInfo table, users will only ever see the one related record displayed in the related fields (regardless of how many records may be present in the table the layout is based on).


                Moreover, if you turn on the option to "Allow creation of records in this table via this relationship", the first time users attempt to enter data in the fields you've provided, if a PrefInfo record (matching their account name) doesn't already exist, it will be created.


                As an additional precaution, as Wilton has suggested, it would be wise to add a constraint in Manage>Security (Privilege Sets tab) to limit users to editing only the record that matches their account name. But the relationship-based interface I've described can be used to handle the logistics very nicely.





                R J Cologon, Ph.D.

                FileMaker Certified Developer

                Author, FileMaker Pro 10 Bible

                NightWing Enterprises, Melbourne, Australia



                1 of 1 people found this helpful
                • 5. Re: How to have only one record in a table?

                  Hi DW,TIM & RCs,


                  I am doing a simple application for FMGO and for Desktop,  It is going to be single user application on FMGO.  I will be capturing details about the buyer  and info like Base currency for the apps etc.   I need to capture it only once, but should allow him to change his address and other details when need arise.   I got many replies that are very useful.   The Prefinfo tab will always visible, whenever the buyer click on it to change info.  But I would like to disable new record once the first record is created on the same for FMGO.   The same app, on the desktop version (I am trying single version suitable for both FMGO and FM on desktop), it will allow multiuser each having their own prefinfo.


                  Thanks for all your input.



                  • 6. Re: How to have only one record in a table?

                    Hi Ramki


                    If I understand your request correctly, you should set up the single preference record (with no data) before you send the file to your customer and then set the privilege set to 'Not allow creation of records' in this table.  I always worry about this record being inadvertantly deleted, even though you can also set the table to allow no records to be deleted.


                    Depending how paranoid I feel, I sometimes use the Closing Script to check for only 1 record in the table - deleting any extra ones with a 'Full Access Privileges' script.  You could also set the same script to add a record if it finds that the original has disappeared.  Then you've got both belt and braces!


                    I do hope that these ideas help you.


                    Best wishes - Alan Stirling, London UK

                    • 7. Re: How to have only one record in a table?

                      Hi Alan,


                      This is what am Planning:


                      On the Ipad or Iphone, the application behave in single user mode.  On desktop it will behave in multiuser mode.

                      the following is to be done.


                      On Ipad:

                      Check the table:prefinfo whether empty, if so, show prefinfo layout to capture details. (this will make an add request while showing the layout).


                      if table has 1 record (no add request or new record button as toolbar disabled on Ipad), check for mandatory fields are empty and force the user to fill them up.  the user cannot focus other tabs without filling the mandatory info.  May be I need to place one common function / script to check this on every layoutenter trigger.


                      Multi user approach would be different.


                      Comments are welcome from you all.  I would appreciate it.