6 Replies Latest reply on Oct 14, 2014 2:25 PM by fliss

    advice on efficient method to allow editing of related records

    fliss

      I am not a trained programmer, so wanted to get advice on the following issue.

       

      I have records related through 4 parent/child relationships, with the great grand parent being an invoice table. My records pass through various 'statuses' with relevant restrictions for each status.

       

      I need to allow editing at the various statuses (even after a record is otherwise labelled as 'closed'), and want to be sure that none of the related records are being operated on during the editing process. (Its possible the record could be committed during an editing process, and thus become editable by others)

       

      I was originally thinking to append the word 'editing' to the name for each status, and then restrict access to related records based on those values. But this method seemed to become cumbersome.

       

      Now I am thinking that having a separate field (UnderEdit), which is populated when the record is edited, is a simpler way to go.

      Still I would need to set up this field in each related table, as edits can be made to any table's record??

       

      I hope I have not been too generic in my description, and that someone can decipher my question enough to provide some advice on the 'proper' way to do this.

       

      Much appreciation

      Felicity

        • 1. Re: advice on efficient method to allow editing of related records
          keywords

          It's not completely clear to me, sorry. You mention 4 parent/child relationships, and also mention great grandparent. So I have three questions:

           

          1.     Does this mean the relationships are all in a chain: child –> parent –>  gp –> ggp –> gggp? In other words, 4 relationships and 5 levels?

          2.     And do you mean that any one of the five records must remain editable at any time?

          3.     And is you main question:  how, when someone is editing one of the records, can you lock the records at the other 4 levels?

          • 2. Re: advice on efficient method to allow editing of related records
            fliss

            hi thanks for helping me.  Sorry its unclear - I was wanting advice on the best way to proceed

             

            1. Does this mean the relationships are all in a chain: child –> parent –>  gp –> ggp –> gggp? In other words, 4 relationships and 5 levels?

             

            The area of concern re editing records can be simplified like this: 

            child (package items) –> parent (packages) –>  gp (orders) –> ggp (invoices),

            with another table (customers) connected to orders,

            and another (invoicees) [to whom the invoice is sent] connected to invoices.

            (plus numerous interrelationships)

             

            The process of creating the order, and then the packages on it and items on the order takes some time, and whilst it is still 'open' some information can change;

            customer may change contact info, customer may change package, discounts might be given, .....

            Also even after order is closed and even possibly after its invoiced some things can change - may need to add a PO number, may need to adjust pricing, .....

             

            We also have mechanisms which limit users, which prevent editing at certain stages, .....

             

            So the question is how best to allow editing when its necessary whilst at the same time disallowing any other editing of any of the related records.

            I think the records need to be marked in some way that disallows editing scripts to run whilst it is so marked. But with the complexity of multiple connected tables I wondered if there is a standard way of handling this requirement.

             

            2.     And do you mean that any one of the five records must remain editable at any time?

            There comes a time, after the package is sent, when editing package items, packages, or orders is no longer permissable.

            Invoices remain editable until end of month closing.


            3.     And is you main question:  how, when someone is editing one of the records, can you lock the records at the other 4 levels?

            No my main question is, is there a standard way that editing requirements as described above are usually met.

             

            I hope this helps explain my need. And again thank you sooo much.

             

            Felicity

            • 3. Re: advice on efficient method to allow editing of related records
              keywords

              All the relationships you describe are not really a chain, even though in some respects they are interlinked. Isounds as if you may need to go back to mapping out your E->R diagram in order to get all of this clear in your mind. Here are some questions that need to be addressed:

               

              1.     Could customers have many invoices?

              2.     Is each invoice for just one customer?

              3.     Could an invoice be for more than one package?

              4.     Must an invoice be for one or more packages, or could it be for a single item?

              5.     Can items be invoices separately or must they be part of a package?

              6.     Must an invoice have a purchase order, or can one be issued for "over the counter/phone/email" sales?

              7.     Is the chain you describe, "child (package items) –> parent (packages) –>  gp (orders) –> ggp (invoices),", in fact an essential order of processing, or just the most common?

              8.     Are there other eventualities you must account for?

               

              Your answers to these sorts of questions will establish the business rules that your solution must address. You may already have this well and truly covered and if so, just file this post under "Not Needed Thanks".

               

              As to your record editing question.  Do you mean that:

               

              •     while one user is in the act of editing a record (eg. the number of a particular item that is ordered) you don't want another user to simultaneously edit a related record (eg. applying a discount to the package that the item is part of)?

               

              Or

               

              until an invoice is finalised and sent you only want certain authorised users to be allowed to edit any of the records related to the invoice?

              • 4. Re: advice on efficient method to allow editing of related records
                Garryt

                Although I am not quite clear about your configuration, One thing you could do, is to store your changes in globals, retrieve the original data from the fields you are trying to change in each table in global fields, set the new values in the appropriate records, if you get an error setting any data, you can back out of the transaction and reset the original data from the globals.

                 

                This is not faultless and it will depend on what you are trying to achieve, but it is one way of changing data in multiple tables.

                 

                You could also lock the record during edit, by setting the used ID into a field in the reocrd and set the security so that only the user related to that user ID can make a change. Once you have completed your amends, you then clear that "editing" field of the user ID, leaving the record to be modified by another user.

                • 5. Re: advice on efficient method to allow editing of related records
                  fliss

                  GarryT, thanks sooo much for taking the time to help me. The concept of locking a record by setting a user ID is not one I had thought of, but useful I think.

                  • 6. Re: advice on efficient method to allow editing of related records
                    fliss

                    hi keywords,

                     

                    Thank sooo much for taking the time to help me. You are right, I have business rules and ER diagram already covered.  I guess I am not good at describing what I need. The FM database has been in use for 15 years and is now quite complex.

                     

                    There is no one particular question to ask - I was wondering if there is a standardized way that editing of records as described by me above is done. My question is probably too broad.