6 Replies Latest reply on Apr 28, 2016 8:01 AM by alecgregory

    Selector Connector: Deleting Records

    alecgregory

      Problem

      I work on a FileMaker vertical market solution (or product). We're creating a new version of the product which uses Selector Connector.

       

      I have recently realized that while it's fine to create records in tables connected to Selector when on a layout based on Selector, deleting records via a portal row on the Selector layout is problematic. This is because deleting a portal row always locks the Selector record, even though all fields in the Selector table are global. And, of course, while the single Selector record is locked no other user can perform a delete action via a portal as the implicit locking of the record does not take place.

       

      To fix this, my initial thought was that I'd need to take a more traditional approach to transactions based on the Transactions module on Modular FileMaker. However, I have since thought of an alternative approach that I'd like to invite comments on. I am calling it Selector Transactions for convenience.

      Selector Transactions

      This approach allows the creation of additional (i.e. beyond the single record that we always need) records in the Selector table when we are working with FileMaker transactions. Just so we are clear on terms, by transactions I mean scripted changes to multiple records that need to take place atomically. That is, either all the changes happen or none of them. A simple example would be the creation of a new image record for a contact. Images are stored in their own table but there can only be one image for each contact. In this transaction we need to add a new image record and then delete the existing image record. If either the adding of the new image record of the deleting of the existing image record fails then we do not want the other action to proceed but to return the system to the state it was in before the transaction begun.

       

      The process for running a Selector Transaction is as follows:

      1. Go to Selector layout (Always in a new modal window)
      2. Create a new record, do not commit it
      3. Perform transaction actions, for example creating a record in one table and deleting a record via a portal in another record
      4. Commit the record to confirm the changes
      5. a) If the commit is successful, delete the Selector record as long as there is more than one record in the Selector table
        b) If the commit is unsuccessful, revert the Selector record, which will have the effect of deleting it.

       

      The creating of a new record in the Selector table means that we no longer have issues with record locking. Other users can use the Selector as normal. Also, because the new record spends most of the transaction uncommitted and is deleted as soon as it's committed, the Selector record count would very rarely increase above 1.

      Points for information

      • As part of the installation process for each customer who receives our product, we ensure that there is a single record in the Selector (and Connector) table. Therefore we can safely assume that there will be one record in the Selector table when the system is used for the first time.
      • For reasons I have not had time to fully explore, the Refresh xJoin script takes a very long time to run, around 0.8 - 1.1 seconds. I assume this is because of the large number of table occurrences in the solution. Therefore, almost all record creation is done via layouts based on the Selector to avoid the need to run the Refresh xJoin script and allow for much quicker record creation. I know this is not the standard Selector Connector way of doing things.

      Points for discussion

      • In the rare case where the Selector record created for the transaction was successfully committed but then not successfully deleted, there would be an additional record in the Selector table. Does anyone think that this would cause problems? Note that all the fields in the Selector table are global, so I don't think this would break any relationships. Of course it's easy to delete these stray records eventually so they don't mount up, but would they cause any harm in the meantime?
      • Any other comments?
      • Any clarifications required?

       

      I can't stop myself trying to tempt toddgeist to respond to this, but I'm keen to hear from anyone using Selector Connector if they have tried this approach, see any issues with it or have alternative approaches I could try.

        • 1. Re: Selector Connector: Deleting Records
          toddgeist

          Hey there :-)

           

          I think you are doing something differently then I do. 

           

          I just went back and checked my original Sample file and I was able to delete a portal row, leaving the record uncommitted, the switch to another computer and perform the exact same operation on a different record.  So I don't think that deleting a record through a Portal MUST lock either SELECTOR or CONNECTOR. You maybe doing something differently that causes that. 

           

          BUT secondly.

           

          Although I have found Transactions with Selector Connector to be great in a pinch, I have moved backed to the older style of the Transactions Module you cited above for the majority of the Transaction Scripting I do.  I find it to be more maintainable in complex systems.  I'll have more to say about this soon, but here is the gist of it

           

          I use SELECTOR CONNECTOR exclusively for the UI file, Transactions go in a API file and data goes in Data file.

           

          Todd

          • 2. Re: Selector Connector: Deleting Records
            alecgregory

            Hi Todd,

             

            Many thanks for the quick response. I think the key difference in what I'm doing is that I am deleting a portal from a layout based on the Selector table. In your original demo file there are no layouts with portals based on that table. You can get away with creating or changing records on a layout based on the Selector table without locking the Selector record as long as the fields themselves aren't on the layout. But you need a portal on the layout for deleting and that's where the record gets locked.

             

            Thanks for the heads up on your current thinking on transactions. I am thinking along the same lines. In certain circumstances it makes sense to run a (small) transaction through Selector Connector, but for anything heavy duty it makes more sense to have a dedicated transaction handling pattern. It's interesting that you are moving towards an API file for transactions, I look forward to seeing more detail on this and I may explore it myself. I can imagine a lot of potential benefits.

             

            Cheers,

             

            Alec

            • 3. Re: Selector Connector: Deleting Records
              toddgeist

              Hi Alec,

               

              Thats right. A key to SELECTOR CONNECTOR is NO layouts on either SELECTOR or CONNECTOR.  Anything you do on those layouts will lock the records.

               

              These deleter Portal are a problem since that means you need to have these deleter portals scattered through out your system. This is one of the reasons why I am shifting back to the older transactions model.

               

              Todd

              • 4. Re: Selector Connector: Deleting Records
                alecgregory

                Hi Todd,

                 

                I agree. I was turned off by needing to have deleter portals all over various layouts. It's much neater to do things in one place. That one place for me was a layout based on the Selector table.

                 

                I do want to address the comment that anything done on a Selector layout will lock the Selector record. I have not found this to be the case. You can create and modify records without issue if you are on a layout based on the Selector table, as long as you don't have any local fields on the layout. It's only when you delete records through a portal that you have the issue with record locking preventing other users creating and modifying records through the selector.

                 

                Alec

                • 5. Re: Selector Connector: Deleting Records
                  toddgeist

                  True,  But I prefer rules that are absolute whenever possible. These are things you can test for.  You can use analysis tool to make sure NO scripts every touch those layouts etc.

                   

                  And you don't need to use SELECTOR or CONNECTOR. You could use any other table where the user could create or own a record.

                   

                  Code should do one thing if possible. SELECTOR selects, CONNECTOR connects. Period. The end. :-)

                   

                  There is another problem that will cause you to have to switch layouts. If you try to start a transaction on a layout that has no records found (or in the table ) it won't work. You need a record for the user to own.  And if you can't have one on the Table your on, you have to move.

                   

                  I used a SESSION based layout. Session is a table where the user owns a record.  Actually its not the user, its any database connection, but same idea.  So SELECTOR selects, CONNECTOR connects, and SESSION has its one job too.  It provides an isolated record that the current FileMaker session can own.

                   

                  So your gonna need a place to move off to, regardless of the deleter portal issue.  If you don't want those deleter portals scattered around,  put them on that layout.  Leave SELECTOR out of it.  That would be my approach anyway.

                   

                  All of this is in the original sample file.

                   

                  Todd

                  • 6. Re: Selector Connector: Deleting Records
                    alecgregory

                    Thanks Todd, I'm see where you're coming from. Ideally I would free the Selector from transactions. Unfortunately, doing that is a bit of an architectural headache due to the number of TOs involved and performance issues. I currently have around 70 app TOs (selected_Contact, selected_Inventory and so on) linked to the right of the selector. I could add a Session table as you suggest and link that to the Connector as shown in your sample file. Conceptually that would be ideal. I could be sure that every user session (or PSoS session) would have their own record to handle transactions and create, edit and delete records all on the session layout using the appropriate [selected_AppTableName] TOs.

                     

                    The issue is that, because the Session table would be connected to the [selected_TableName] tables via an xJoin relationship I would need to run the Refresh xJoin script between each record creation and that script takes about 80ms to run on a blank layout. So creating 1000 records adds around 80 seconds of overhead, which is not practical.

                     

                    The alternative to that is to have the Session table look very much like the Selector table and directly link to it's own set of app TOs. This would both free the Selector to do what it should be doing and allow the links between the Session TO and the app TOs to be direct, negating the need to run the Refresh xJoin script. This would solve the record locking and performance issues, but it requires another 70 TOs and relationships. At the moment I'm edging towards this approach, even though it will require reworking a fair amount of existing scripts.

                     

                    Anyway, I've found this a very useful discussion. Thanks for taking the time to engage.