0

Script for Inventory

Hello everyone,

For information, I only speak French. So the following is translated by "google translate".

I start with NINOX.

I am busy developing a table allowing me to carry out inventories. I've almost finished everything, but for now, I'm stuck.

To begin with :
I need that when I press a "start inventory" button that ninox shows me all the items.
I have already planned everything, but I finally lack a script that launches the "automatic" selection of all the articles on the "Item Code". And there, I don't know how to write it.

Next :
I would like to be able to include a condition filter, which would allow the system to select only items belonging to a specific product family.
There also I planned a field allowing to select the various families. Missing more than the script.

Could someone help me?

Thanks in advance and have a nice day everyone

 

Didier

46 replies

null
    • Fred
    • 1 yr ago
    • Reported - view
    Didier Schoonjans said:
    the problem is finding the trick to value the stock (and that's only where I'm stuck at the moment)

    I would then ask why Stock is a formula field? Shouldn’t Stock be a number that you enter?

    They way you have it now is missing a step. You create your purchases and sales then figure out stock. What I think may work better is when you purchase an item to sell, it will make a new record or update the record of the item in your (new) catalog table not your article table.

    Why separate article and catalog? A catalog item could be made of up 2 or more articles. You could discontinue an item in catalog but don’t want to delete it from article. You can set discount info in catalog that has nothing to do with the item.

      • Didier_Schoonjans
      • 1 yr ago
      • Reported - view

      Fred 

      GREAT, I put my finger on the problem.

      In reality, I put the theory to the test and I found a detail that makes all the difference.

      When carrying out an inventory, things must be done in order. That's to say :
           1) we draw a list of parts with a column indicating the stock. BUT ON A FIXED DATE.
           2) then we physically count the pieces
           3) we validate the inventory and the stock moves

      So I added a "Frozen Stock" number column in the view of my "Inventories" table.
      I have introduced 4 types of possible stock correction:
           - the stock remains the same
           - the physical stock is smaller than the theory
           - the physical stock is larger than the theory
           - the physical stock is at zero

      For the example I manually introduced in the "Frozen Stock" column the value of the stock at the time of the inventory.
      I then adapted the script found in the "Stock" formula field of the "Items" table to take into account the calculation of inventories and the fact that the stock only changes as soon as an inventory has reached its level 3 status.
      We note that when an "Inventory" goes from level 2 to 3 status that the stock corrects itself correctly.

      So now all is good.

      The only thing left to do is for the "Frozen Stock" column to become a formula that extracts and FREEzes the stock.

      And then I don't know how to do it.

    • Fred
    • 1 yr ago
    • Reported - view
      • Didier_Schoonjans
      • 1 yr ago
      • Reported - view

      Fred 

      For the moment, if I create an Inventory on February 12, with for example an article which has 10 pieces in stock. In the "Inventory" table, the "stock" column will display this item with 10 pieces (this is normal).
      If on February 13, I sell a piece, my stock therefore drops by one piece, i.e. 9 pieces in stock. If I go back to see in the inventories that I had created on February 12, I would see that the article is no longer at 10 but at 9 (the number 10 is therefore not fixed).
      And that is not correct, because an inventory shows the state of the stock precisely at a given moment.

      In summary, we would need a script that will read the stock and display the corresponding number in the "Frozen Stock" column. In this way, not only does the "Frozen Stock" column present a number which has nothing to do with the "Stock" script (therefore no more loops) but also which shows the state of the stock on a specific date ( that listed on the inventory).

      • Didier_Schoonjans
      • 1 yr ago
      • Reported - view

      Fred 

       

    • Fred
    • 1 yr ago
    • Reported - view

    May I recommend a different way of approaching it.

    As you stated, an inventory needs to a snapshot of your stock at a particular date. Which means you need all the data in that table to be isolated from the rest of your records. So my thought is to copy over all relevant data into the Inventory table then you have a point in time picture of your stock.

    So you would create in Listes a ‘pre-Inventory Stock’ number field that would be updated by your button ‘Commencer l'inventaire’. Then you could create a ‘post-Inventory Stock” formula field that would take the pre field and add the Difference field.

    You would update the Commencer l'inventaire button to add line 6:

    for loop1 in xArt do
                        let newRec := (create Listes);
                        newRec.(
                            Inventaires := t;
                            Articles := loop1;
                            'pre-InventoryStock' := Stock
                        )
                    end
    
      • Didier_Schoonjans
      • 1 yr ago
      • Reported - view

      Fred good evening, I don't understand exactly what you explained (it's certainly because of google translate)
      On the other hand, do not hesitate to set up your solution in the SoundProcess(Testing) database.
      If you agree

    • Fred
    • 1 yr ago
    • Reported - view
    Didier Schoonjans said:
    On the other hand, do not hesitate to set up your solution in the SoundProcess(Testing) database.

     Ok, I've modified the SoundProcess (NINOX TEST) DB.

    I've added the following fields to Listes:

    préInventaire - number field

    inventaireFinal - formula field

    I have added a new record in Inventaires under my name. :)

    Check the code for the button Commencer l'inventaire.

    Check the records in the view element for the new record to see how the new fields work.

      • Didier_Schoonjans
      • 1 yr ago
      • Reported - view

      Fred Good morning.
      Thank you for your intervention, but the stock of items "Articles" does not change once the inventory has been validated

    • Fred
    • 1 yr ago
    • Reported - view
    Didier Schoonjans said:
    Thank you for your intervention, but the stock of items "Articles" does not change once the inventory has been validated

     You have to decide how you want Articles.Stock to be set. It is now a formula that uses the number field in two other tables. So we can’t change it directly and you can’t change the numbers in the other two fields.

    May I recommend you change Articles.Stock to a number field that is set  by changes in Achats and Ventes. So you enter in a record to Commandes Fournisseurs and then a record in Achats. Modify the Enregistrer button to take the current value in Articles.Stock, add the value in Quantité then copy the new value back to Articles.Stock,

    Then you would do the opposite for Enregistrer button for Ventes.

      • Didier_Schoonjans
      • 1 yr ago
      • Reported - view

      Fred I found a solution that doesn't change anything to my buy and sell tables.
      Everything works fine, stock corrects without error.
      I go through an intermediate column "Frozen Stock" that I have to fill in manually by copying it to the "Stock" column and there everything works fine (but unfortunately, I have to do it manually).
      Currently, I have added a new "Test Formula" column in which I would like a formula that would automatically do what I do manually in the "Frozen Stock" column, in short a copy. I tried different solution (but without success so far).
      I implemented this solution in the SoundProcess database not the test one.

      • Business Analyst
      • Terry_Hopper
      • 1 yr ago
      • Reported - view

      Hi Didier Schoonjans . Reading through this forum thread, it sounds to me like you need to create a new table for stock adjustments as a result of each stocktake (l'inventaire).

      You will then have a table for:

      • Sales
      • Purchases
      • Adjustments to Stock

      Then when you complete a stocktake (l'inventaire), you have a button to create a new row for  any differences between counted stock and recorded stock. This row will be created in your stock adjustments table. Your formula for current stock should then include Sales, Purchases, and Adjustments. There will then be no circular reference, and it will keep an accurate record of when adjustments happened.

      I hope this translates to French ok. Ask if you need me to explain it better

    • Fred
    • 1 yr ago
    • Reported - view

    where is the code that sets the Status in Inventaires to 3? I can’t find it.

      • Fred
      • 1 yr ago
      • Reported - view

      Never mind. My mistake. I fixed it.

    • Fred
    • 1 yr ago
    • Reported - view

    Ok, I've modified the (NINOX TEST) db with the following:

    Article table:

    Stock_current - number field

    Listes table:

    préInventaire - number field

    inventaireFinal - formula field with

    'préInventaire' + 'Différence'
    

    Status field - added new Trigger after update:

    if Status = 3 then
        for loop1 in 'Listing (ajout)' do
            if loop1.'Quantité compté' != null then
                loop1.Articles.(Stock_current := loop1.inventaireFinal)
            end
        end
    end
    

    This code checks if Status equals 3 (line 1) then it will loop through (line 2) all items in the 'Listing (ajout)' reference field. Then it checks to see if an inventory has been done on the item by checking if Quantité compté is not null (line 3). I'm am assuming that if it is null then you haven't verified it. If you did verify it you would put 0 for none found. Then it will copy over the data from inventaireFinal to Stock_current.

     

    Achats table:

    Modified Enregistrer button with:

    let st := Articles.Stock_current;
    let newStock := st + 'Quantité';
    Articles.(Stock_current := newStock);
    closeRecord()
    

    Line 1: takes the current quantity from the Stock_current field and puts it in a variable.

    Line 2: takes the variable in line 1 and adds to it the value in the Quantité field.

    Line 3: then sets the value in Stock_current to be the new value from line 2.

    Line 4: closes the record

    So now when you buy a new item from your suppliers it will modify the quantity in Articles.

    • Fred
    • 1 yr ago
    • Reported - view

    So try out the following work flow:

    1) create a new purchase from a supplier and add a few items that currently don't have any stock.

    2) you should see the Stock_current field get updated.

    3) then create an inventory with the new updated items and add a quantity to 'Quantité compté'. Then close the inventory and you will see the updated Stock_current.

    • Fred
    • 1 yr ago
    • Reported - view

    Looking at the flow again. I changed:

    Removed the code to add the data from Achats Enregistrer button.

    Created a new code for Commandes Fournisseurs.Status Achat trigger after update so when you select Réceptionné for the Status it will then update all the related items in Achats. This makes more sense as this is when you verify that you have received the items.

    if 'Status Achat' = 4 then
        for loop1 in Achats do
            let st := loop1.Articles.Stock_current;
            let newQnty := st + loop1.'Quantité';
            loop1.Articles.(Stock_current := newQnty)
        end
    end
    
      • Didier_Schoonjans
      • 1 yr ago
      • Reported - view

      Fred 

      Hello Fred,
      First of all, thank you for your work and your patience.

      For information, I'm not much available today and I will be away for the next few days. I return on Monday February 20.

      I looked despite this (but not yet in detail) your work.
      Visually, the "Inventories" Table is the same (small changes to facilitate development work).
      The "Purchases" Table has not visually changed either. You changed the calculation formula there (I don't have a problem with that). As long as it's still good and it takes into account the commands already introduced in the system (but I think so). If not, I can delete them and then re-enter them.

      On the other hand, the "Items" Table now displays 3 stock fields and that is wrong. Only one is needed which represents the stock in real time and which should be called, ultimately simply, "Stock".
      But I think the first two stock fields are no longer useful and could just be erased.

      But overall it looks pretty good to me.
      I try to validate everything today or next Monday at the latest.

      For info:
        1) As soon as the "inventories" part is finished, I have to embark on a "Bills of material" management cycle. I think that stock management will still have to change so maybe already take this into account now.
        2) If it's simpler, I had planned a "Parameters" table, can't we plan to place the stock management there; purchases, sales, inventories, manufacturing (this is only an idea)

      I'll get back to you as soon as possible and thank you again for your work.

      • Didier_Schoonjans
      • 1 yr ago
      • Reported - view

      Fred 

      • Didier_Schoonjans
      • 1 yr ago
      • Reported - view

      Fred 

      Hello Fred,

      Here I have freed up time to look in detail at the changes you have made.
      Sorry, but that's not right.
      In the "Items" table there can only be one "Stock" field, not three. In short, the "Stock" field should be replaced by "Real Stock", but in this case, nothing works anymore.
      At the inventory level, the "préInventaire" column, which must show the stock just before the inventory, should be completed automatically and this is not the case (this is precisely my problem).

      Ultimately, you have implemented another solution/method but which gives roughly the same result as where I had arrived except that you have three "Stock" fields in the "Articles" table.
      You have the same problem as me, where the "préInventaire" column does not fill automatically. And what happens when a little later we make a new inventory.

      In my solution, I only have one "Stock" field which updates well with the different multiple movements of purchases, sales and inventories.
      I just have the "Stock Figé" column which is basically your "préInventaire" column which is completed manually (like yours).
      In my solution, I added a column "Formule Test" which should be the reflection of the column "Stock Figé" where I experiment different solutions for the automated and the frozen at the time of the creation of the inventory (status 2).

      The solution would be to read the stock values when the inventory is at status=2 of each item (that's what I'm doing now), to place them in memory. Then close the recording. And finally to display the values present in the record in the "Formule Test" column but without these values changing when the status=3

      And then it's all right

    • Fred
    • 1 yr ago
    • Reported - view
    Didier Schoonjans said:
    In the "Items" table there can only be one "Stock" field, not three. In short, the "Stock" field should be replaced by "Real Stock",

     Yes that is true. I was not deleting anything you created just adding.

     

    Didier Schoonjans said:
    At the inventory level, the "préInventaire" column, which must show the stock just before the inventory, should be completed automatically

     It works for me. Look at

    CAPA-0001.00-400-A-MKP

    in the Listing (vue) table. You will see that the préInventaire is copied from the Stock_current field.

      • Didier_Schoonjans
      • 1 yr ago
      • Reported - view

      Fred Unfortunately, I will not be available for the next few days. I'll be back this Monday, and look at all that as soon as I get back.

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 46Replies
  • 496Views
  • 3 Following