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
-
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 said:
The only thing left to do is for the "Frozen Stock" column to become a formula that extracts and FREEzes the stock.What do you mean by “FREEzes the stock”?
-
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).
-
Fred
-
-
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 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 said:
Thank you for your intervention, but the stock of items "Articles" does not change once the inventory has been validatedYou 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.
-
where is the code that sets the Status in Inventaires to 3? I can’t find it.
-
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 endThis 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.
-
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.
-
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-
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.
-
Fred
-
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
-
-
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 automaticallyIt 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.
Content aside
- Status Answered
- 1 yr agoLast active
- 46Replies
- 440Views
-
3
Following