inventorie "frozzen stock at date"

Hello everyone.

I only speak French, so this is a translation via GoogleTranslate.

I come back to you regarding the creation of an inventory.

I recreated a new question because the old one had become too big.

Here is a short summary.

I created a database with 4 tables:

   1 => "ARTICLES" (taking up the list of items)

   2 => - "COMMANDES FOURNISSEURS" (including all purchase orders)

           - a "ACHATS" sub-table (containing the data concerning the purchases)

   3 => - "COMMANDES CLIENT" (including all sales and invoices)

           - a "VENTES" sub-table (containing the data concerning sales)

   4 => - "INVENTAIRES" (including all "stock corrections and updates" inventories)

           - a "LISTES" sub-table (containing the data concerning the inventories)

Currently everything is working fine.
Purchases and sales adjusts the stock of items well and inventories also corrects the stock of items well.


In the "INVENTAIRES" table I have a view (coming from the "LISTS" sub-table) showing me the list of items whose stock has been corrected/updated.
To do this, I have 5 columns:
    1 => "STOCK" showing real-time stock of items.
             (This column is currently only used to illustrate the problem)
    2 => "STOCK FIGE" showing the stock but blocked at the time of the creation ofthe inventory.
    3 => "FORMULE TEST" it is precisely here that the problem lies
             (This column is currently only used to illustrate the problem)
    4 => "QUANTITE COMPTE" field allowing to manually enter the quantity actually counted for each item
    5 => "DIFFERENCE" showing the difference between stock and counted quantity
             (This column is used to correct the stock)

My problems are the following.
Currently, column "2" is just a "number field". I have to manually copy into this column the numbers that are in column "1".
What I'd like is for this column to auto-update, BUT the values in it to be "frozen" at the time the view is created.
To explain my problems, I created column "3" which should replace column "2". Which is a "formula field" where I'm testing scripts to automate column "2".


In order to illustrate the problem here is the complete process.

The "INVENTAIRES" table has several statuses representing the different stages of the process.
  1 => "Planifié" planned (step 1, selecting inventory information)
  2 => "En cours" In progress (step 2, display of inventories and introduction of values)
  3 => "Clôturé" Closed (final stage, this is when the stock corrects itself)


Step 2, everything is OK

Step 3, not OK

The "STOCK" column shows that the stock of items is corrected. So the stock movements are correct.
On the other hand, the column "FORMULE TEST" is also corrected, except this one should have kept the values of status 2 without this corrected.
I call this a frozen stock.

Here is the script I use in the "TEST FORMULA" column

Thank's in advanced

2 replies

    • Fred
    • 1 yr ago
    • Reported - view

    Take a look at the Ninox Test db and then at the Commencer l'inventaire button in the Inventaire table. It has the code to copy the current stock into your Inventaire record. Here is the important change, line 6:

    for loop1 in xArt do
        let newRec := (create Listes);
                    Inventaires := t;
                    Articles := loop1;
                    'préInventaire' := loop1.Stock_current

    You can just add a line to copy the data from Articles to your Liste record.

    You can’t use a formula field to set data in another field.

    If you want to test code that sets data in other fields you have to use a button or a trigger. I recommend a button for testing so you can make sure you know when it runs. A trigger adds a level of complexity that can make troubleshooting difficult.

      • Didier_Schoonjans
      • 1 yr ago
      • Reported - view


      Thanks Fred,
      In fact, it's all good now.
      In addition, I see that you had already given me this information in the old post. But I hadn't seen her. Everything was drowned in the mass of comments.
      My inventory cycle is now complete.

      Once again, a very big thank you for your precious help, your patience and your willingness to help.

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 2Replies
  • 53Views
  • 2 Following