6 Replies Latest reply on Jul 21, 2015 6:59 AM by Fred(CH)

    Change Order Design

    BarbaraCooney

      I would like to hear how you've structured your data model to support change orders. That is, preventing edit to the original order and its items, but allowing for edits to quantity or cancellation/addition of new items to the order. I have some ideas, but would rather hear suggestions first. tia, Barbara

        • 1. Re: Change Order Design
          mikebeargie

          I treat change orders as new orders entirely. since order lines would be what affects my inventory and billing at a summary level, everything balances in the end and locking can be handled "for sure" this way.

           

          You could perhaps come up with your own custom client-facing order numbering system that allows you to have Order #1000 and #1000-1, but keep your primary keys hidden as values 1000 and 1001 on the backside for your relationships.

          • 2. Re: Change Order Design
            BarbaraCooney

            Hi Mike, thank you for offering advice. I didn't want to cloud the issue with my idea of design. I was thinking a new table change_order (or perhaps use my existing order table and add a type?) that had the fk_order_id and would use the same order_item table. Each order_item would have an fk_order_id and fk_change_order_id. All keys are UUID. Yes, I have an auto-enter serial for client-facing.

             

            So, hmm, if a qty were to decrease for an item, you'd cancel the order_item on the original order and have client create a new order with the corrected amount?

            Edit: You're not saying that at all are you? Just create a new change order and if I were to decrease an item, this order_item would have a negative quantity. Correct?

            • 3. Re: Change Order Design
              electon

              Do the orders involve handling of inventory?

              It heavily depends on what the scenario, business type / model is.

               

              Anyway, once an order is past a certain status. No modification allowed.

              Create a change order, either empty or dupe the original so you can add / delete / change quantities.

              Yo'd probably need a sum total of change orders on your original order to see the balance.

               

              Put a reference to the original order, maybe in a field called original_order_nr or reference.

              I'd also use a field

              order_type:

              Order

              Change Order

              • 4. Re: Change Order Design
                Fred(CH)

                When a enduser want to change a record later in a process, even if difficult to program, i first will try to see if it is possible safely.

                 

                I see two main caveat to allowing changes on a confirmed order :

                1. History loss of a particular business
                2. Corruption of the coherence with other records and process (for instance in a fabrication process)

                 

                If possible, i would after each change, generate automatically a PDF to store the business's history on a related and timestamped table and thus, solving #1.

                Additionally, i would try to create a script that update all related records accordingly if possible, in particular if allowed by accounting.

                 

                If not possible for whatever reason, i would do as electonsaid :

                "once an order is past a certain status. No modification allowed."


                And then, as Mikesaid :

                "I treat change orders as new orders entirely. "

                • 5. Re: Change Order Design
                  BarbaraCooney

                  The part that is the most difficult for me is that each order_line relates to:

                  • one or many po_line_item
                  • one or many invoice_line_items
                  • one or many shipment_items


                  I can use the presence of these relationships to determine what changes are allowed. There is no "inventory" per se, but invoicing is only allowed for items received (order_items with a related shipment_item - "you have it to sell.")

                  • 6. Re: Change Order Design
                    Fred(CH)

                    Dont know if it helps, but you can use a customized privilege set to prevent edition and deletion of a record when data is found on a related table.

                     

                    This will only work for these privilege set and not if you access with a full access account. But since best practices are to not use full access account for endusing, i find this solution strong, clean and easy.

                     

                    Tell me for more detail if interested.