0

freeze data in a field

Hi Folks, I'm sure this has been asked about before but none of the solutions are working for me.

Simply put, I have a formula field that is calculating based on 2 number fields on separate tables.

The calculation is fine but I want to preserve the answer and not have it affected when one of those number values is changed on a future record.

A trigger after update at the table level is copying the result from the formula to a number field which is set to only be writable if null but the formula output is of course, not affected by this lock out.

The simplest way I can describe this, I want Ninox to be a printing calculator and no matter what I change in the calculation, the previous print out can not be altered.

Any advice?

Many thanks 

5 replies

null
    • Fred
    • 7 days ago
    • Reported - view
     said:
    I want Ninox to be a printing calculator

     I'm not sure what that means. Are you creating a new record in a table every time you change values? If not then where do you want to store the data with every change?

    What is your work flow? Once we know that then we can figure out ways to make sure things don't get overwritten.

      • Rob
      • 6 days ago
      • Reported - view

      Hi Fred, As always, it makes sense in my head but I can't type it out clearly :-)

      The workflow is as follows:

      Table 1 contains a list of costs which are updated semi-regularly (product, cost, date updated)

      Table 2 contains orders with the quantities of the products listed and a formula to multiply quantity of products by the last added cost record in table 1 for that item.

      This all works fine for me except that all my old records in Table 2 are updated every time a new cost record is created in table 1.

      I need to refer to the old records on occasion and there may be active orders on table 2 that had been agreed at a previous cost but not released to our customers until after a new cost record is created in table 1.

      Basically, once I create record in table 2, the calculation needs to run once and stop there.

      I have tried to use a table level trigger after update to replicate the formula value into a number field which has restrictions on writable but this does not interrupt the behaviour of a formula.

      My bad example of a printing calculator is just that I would have the calculation run, printed out and the next calculation could not go back and change this.

      Hope this makes a bit more sense and as always, thank you for your time and guidance.

      • Fred
      • 6 days ago
      • Reported - view

       have you tried a field level trigger? so when you select the product it copies the name, productID, and cost of the product into static fields. then you could have a button that you can click to update individual or all products.

      • Rob
      • 5 days ago
      • Reported - view

       I haven't tried that yet but sounds like a good option. I was trying to avoid additional input from me as the process of selecting a product and adding a quantity gets repeated a lot when I am creating the records on table 2 but this might be the answer. 

      Thanks again for your suggestion and time, much appreciated 

    • Rob
    • 6 days ago
    • Reported - view

    Sorry Fred, I didn't answer your first question:

    Yes, I am creating a new record in table 1 every time the cost changes.

    Many thanks 

Login to reply

Content aside

  • Status Answered
  • 5 days agoLast active
  • 5Replies
  • 39Views
  • 2 Following