5 Replies Latest reply on Feb 27, 2015 11:18 AM by MarcinSobocinski

    Locking pulled related field (or how to make a copy of the record)?

    MarcinSobocinski

      Title

      Locking pulled related field (or how to make a copy of the record)?

      Post

      Hi,

      I have a small invoice management FM Pro system. One parameter that is use in invoices table is pulled from customers' table.

      Is it possible to lock (freeze) as historical that pulled parameter in invoice table to prevent overriding it by the new value entered in customers' table?

      I need to keep it as historical value in invoice table, but still have actual value in customers' table for current and future use (until again modified).

      Thank you in advance,

      Best regards,

      Marcin

       

       

        • 1. Re: Locking pulled related field (or how to make a copy of the record)?
          philmodjunk

          Think of how your invoice system manages any price changes. The same issue applies there as with this data from your customer table and the solution is the same in both cases.

          Using the customer example only, You can define corresponding fields in your invoice table to hold this data from the customer table. Assuming that you have a relationship linking the record in customers to your invoice record, either looked up value or calculation auto-enter field options can copy data from fields in the customer record into corresponding fields in the invoice record once you select a customer record and link it to your invoice record. Since this is a copy of the customer data, changing the original copy in the customer record will not change the copy of it made in the invoice record.

          I frequently point out to people setting up an invoice system that most businesses will want customer contact data to be copied to each invoice record so that there is no confusion over what data was used for billing and shipping at the time the order was processed. You need to be able to see the actual info in place even if the data was later changed in order to resolve any issues over a customer not being billed or not receiving a shipment.

          "I'm sorry sir, but that invoice still had your old office's shipping address as we did not receive your change of address notice until after that order was processed and shipped...."

          • 2. Re: Locking pulled related field (or how to make a copy of the record)?
            MarcinSobocinski

            But that's my point. I have one calculation auto-enter field that uses data from my customers' table and it is not a copy of data. I don't know how to acutally make a copy. Once I change data in my customers' table the invoice is affected. Could you please advice how to make such copy of the data from customers' table?

             

             

            • 3. Re: Locking pulled related field (or how to make a copy of the record)?
              philmodjunk

              If it's an auto-enter field, it IS a copy of the data and changes in the customer table will not change the data shown in the invoice.

              If, on the other hand, it's an unstored calculation field referencing this data, it will update when the referenced customer info is changed.

              • 4. Re: Locking pulled related field (or how to make a copy of the record)?
                MarcinSobocinski

                PhilModJunk,

                 

                 

                Forgive me my lack of knowledge...I am very beginner :)...

                So, if I use "Number" data field and then use Auto-enter/ Calculation value I got no data in my invoice table using the very same formula that with Calculation field :/

                I am not able to spot the problem... 

                The very fomula (basicly that is a simple multiplication of two values from customers' table - which is in turn linked to invoice table via customer_id key) works great when using Calculation field and does not work when used as Auto-enter/Calculation value :( 

                Could you please advice how to deal with it?

                Thanks in advance.

                Best regards,

                Marcin

                 

                 

                • 5. Re: Locking pulled related field (or how to make a copy of the record)?
                  MarcinSobocinski

                  PhilModJunk,

                  I got it! I had to recalculate my invoices after changing the field type. Now it works exactly as you said! Great :) Thanks a lot!

                  I will use the same mechanism for some other fields basing on what you said in your first post.

                  Best regards,

                  Marcin