Script for Inventory
![](https://s3-us-west-2.amazonaws.com/media.forumbee.com/i/999df09f-5f8d-437c-bc65-967c3d974393/h/547.png)
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”?
-
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 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.
-
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
-
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
- 468Views
-
3
Following