8 Replies Latest reply on Aug 20, 2014 9:53 AM by philmodjunk

    Transactions with portal records

    Porpoise

      Title

      Transactions with portal records

      Post

           Hi,

           A very useful feature of Filemaker as I understand it, is that you can make lots of changes to a layout record and to related portal records, and then decide whether to commit or to revert these changes, so you get an all or nothing transaction. Of course this extends to creating a new layout record and making changes to it.
           However, it seems that it isn't possible to create a new record and create a new portal record within one single transaction. When trying to create the new portal record, I get an error 510 ("Related value is empty or unavailable.") There seems no good reason for this unfortunate limitation. The field in the layout record that defines the relationship with the portal table has a value already. Am I overlooking something?

            

        • 1. Re: Transactions with portal records
          philmodjunk

               The first thing that you are overlooking is that you haven't told us exactly how you are attempting to create a new related record. I would guess from context that you are doing this in a script? Yet in many cases, creating a new portal record does not require any script at all if "allow creation of records via this relationship" is enabled for the portal's table occurrence in the relationship on which you have based this setup.

               The next thing that you have overlooked is describing that relationship. There are different auto-enter options for the layout table's primary key--the usual match field to the portal table in this situation and the options you select might affect whether this works or not.

               So I suggest you use Post a new Answer to respond with a more detailed description of what you have and what you have attempted.

               PS. Unless steps are taken to prevent it, it's very easy to commit records on a FileMaker Layout and this will keep you from reverting records as you describe here.

          • 2. Re: Transactions with portal records
            Porpoise

                 Hi Phil,

                 Sorry. Yes, I was using a script. I wanted to make sure the layout record is never created without at least one portal record.

                 The layout table's primary key is the match field to the portal table, and is auto-entered by Get ( UUID ).

                 The script basically does just:
                 New Record
                 Goto Object ["Portal"]
                 Goto Portal Row [Last]
                 Set Field [PortalRecord::PortalField;"Something"]

                 When adding a Commit Record after the New Record, the error goes away. But I found out the root of the problem is my paranoia. :-)
                 The foreign key of the portal table is required to be member of a value list that consists of all primary keys of the layout table. When I remove that validation requirement the error goes away too. So it is possible to create the layout record and portal record within a single transaction.

                 While I believe it is incorrect that the value list doesn't reflect the unfinished transaction, I have to admit it is quite an obscure bug.
                 If you agree, I'll file a bug report.

                 Is there another way to validate the foreign key of the portal table? Or am I worrying too much?

            • 3. Re: Transactions with portal records
              philmodjunk
                   

                        I wanted to make sure the layout record is never created without at least one portal record.

                   This is rarely necessary as you can create as many related records as you need at the time that you need them. And you can create new portal recors just by entering data into the "add row" of a portal when "allow creation..." is enabled.

                   

                        While I believe it is incorrect that the value list doesn't reflect the unfinished transaction, I have to admit it is quite an obscure bug.
                        If you agree, I'll file a bug report.

                   It's not a bug, it's pretty much expected behavior. Until you commit the record, FileMaker can't and shouldn't update the index on the field that serves as the source of values for the value list.

                   

                        Is there another way to validate the foreign key of the portal table? Or am I worrying too much?

                   I don't see any value to that validation requirement. FileMaker is generating the value for you and even if this validation worked, you can still get records in the portal table that don't have a matching record in the parent table simply by either deleting the parent record or by changing it's primary key. I'd just enable the "delete" option for the portal table so that deleting a parent automatically deletes all related portal records and then set the "do not modify" option on the primary key field of the parent.

                   Using scripts that interact with a portal are more "brittle" than scripts that achieve the same results without interacting with the portal. That's my term for a script that is easily broken by making minor changes to your database--especially layout design changes.

                   Here's one alternative:

                   Set Variable [$ID ; LayoutTable::PrimaryKey ]
                   Freeze Window
                   Go to Layout ["PortalTable" (PortalTable)]
                   New Record/Request
                   Set Field [PortalTable::ForeignKey ; $ID
                   Go to Layout [original layout]

                   The main potential drawback to this script is that it can trip a number of script triggers on either of the two layouts involved, but there are ways to keep such scripts, if any exist, from interfering.

                   But as I said at the start, there's no real need to add this blank new record. With "Allow creation..." enabled, it'll look like this takes place automatically each time you create a new parent record.as the portal will always have one blank row in the portal for adding new portal records.

              • 4. Re: Transactions with portal records
                Porpoise

                     The portal records contain the users that have access to this record. That is the reason I require at least one portal record to be present. So I just changed my script from requiring multiple layouts in order to make creating a new record with the matching portal record an all or nothing transaction. In this way I get the correct match field for free. And I did like not having to disable all kind of triggers when switching layouts. The code got a lot simpler. Guess I'll have to rethink this.

                     I don't delete any records. They are just flagged with a z_Deleted field, and ordinary users don't have access to those records. And primary keys never get changed. I had hoped validating the match field could trip any stupid mistakes I make.

                     Oops. I guess the access rules won't see any changes made during a transaction (before the commit) either. I'll have to watch out for that one too. I hope I won't need to many scripts that run with full access rights.

                • 5. Re: Transactions with portal records
                  philmodjunk
                       

                            The portal records contain the users that have access to this record. That is the reason I require at least one portal record to be present.

                       Sorry but there's not enough detail in that to for me to be sure that I understand what you are describing. Still don't see any reason for always creating a blank portal record every time that you make a parent record. User and/or account names can be auto-entered into fields at the time the record is created without needing any script.

                       

                            I had hoped validating the match field could trip any stupid mistakes I make.

                       Yes, but with proper interface design, such "stupid mistakes" should not be possible. Manually entering the value of a primary key into a foreign key field, for example, should not be permitted. (That's where value lists and search scripts etc can be used to find and connect a given child record to a selected parent record.)

                  • 6. Re: Transactions with portal records
                    Porpoise

                         In many examples a field where the user name is entered is used for access control to that record. In my database such a record can have several users with access rights, so they are stored in a related (portal) table.

                         Table Clients (kp_client, .... )
                         Table Users (kp_user, kf_client, user name)
                         Relation: kp_client = kf_client

                         Users get access to Clients when
                         ( Clients::z_Deleted = 0) and ( ValueCount ( FilterValues ( List ( Users::user name ) ; Get ( UserName ) ) ) > 0 )

                         So the portal record that I want to create in the transaction isn't blank. The user that makes the Client record is the first to get access to that record.


                         The stupid mistakes are not those of the users, so the problem is not in the interface design. I meant stupid mistakes while scripting. I did use the term paranoia, didn't I? ;-)

                    • 7. Re: Transactions with portal records
                      Porpoise

                           In SQL during a pending transaction, the database is required to respond as if the pending transaction has already been committed. Filemaker does not update the value lists, but it does update the record count to include a new, but uncommitted record. So at least the behavior of Filemaker is inconsistent.

                           Another inconsistency is that when creating a new record where the primary key is auto entered, the user does not need to have modify rights for the primary key. That is fine, because one could argue that although the user initiates the change, it is the system itself that gives the primary key it's value. Or perhaps it isn't a real modification, but the record is created with that primary key. Anyhow, it is nice to be able to restrict the user's rights to modify the primary key.
                           But when creating a new portal record, the foreign key will be filled in by the system too. Still, now the user needs to have modify rights for the foreign key. I would prefer that the user does not need modify rights for the foreign key.

                           The error message is misleading. "This action cannot be performed because this field is not modifiable." Normally the portal record is created by editing the last row of the portal. One is led to believe that 'this field' is the field the user was editing, while it is the foreign key field (that often is invisible) that had to be modifiable. I'm sure seasoned Filemaker developers will shrug about this, because they know all these small intricacies. But it makes live quite miserable for beginners like me.

                      • 8. Re: Transactions with portal records
                        philmodjunk

                             It's only inconsistent when compared to the behavior of the other product. Value lists that draw values from tables get their values from field indexes that will not update until new/changed data is committed.

                             That second paragraph does not show any inconsistencies to me. A primary key and a foreign key are not the same thing. You frequently, and this is not unique to FileMaker, need to be able to modify a foreign key field by selecting a value for the parent record from a value list. But your interface design can easily limit a user's access to any field in the table for which you do not wan the user to be able to directly modify.

                             

                                  Normally the portal record is created by editing the last row of the portal.

                             Only if "allow creation..." is enabled in the relationship. Many developers choose not to enable this option--especially when they want the newest record to appear at the top of the portal to save users the trouble of scrolling the portal.

                             "This field is not modifiable" IS misleading in this context as the error message does not tell you which field is not modifiable and it should. You are welcome to post this feedback as a bug report in Report an Issue or as a feature request at http://www.filemaker.com/company/contact/feature_request.html

                             Posting there insures that someone who actually works for FileMaker will see your feedback on this issue.