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:
I have already planned everything, but I finally lack a script that launches the "automatic" selection of all the articles on the "Item Code".What do you mean by “automatic”?
In your first picture, what is the code for the two, what I assume are, dynamic choice fields?
Maybe you can post a sample DB?
-
Hello Fred,
In my "Inventories" table I use a "List" sub-table.
I have planned two possibilities to carry out the inventories.A first "Manual" where the user selects item after item in order to gradually fill his inventory (in this case, everything is finalized and is OK)
A second "Automatic" where the user goes through a second filter in which he chooses if he wants all the articles or if he only wants a part of them selected according to the family of articles.
Currently I have already created the dynamic family selection field. But I don't know how to make the system fetch the articles, sort them and then fill my "list" table
I want to give you a copy of my DB, but how do I do it?
I start under NINOX
Thanks in advance
Didier
-
Didier Schoonjans said:
I want to give you a copy of my DB, but how do I do it?If you are using the App with a local/iCloud DB then click on the upper right of the DB icon and select Save archive as..
If you are using a browser then you have to have the Professional level to save a manual backup.
If you have a cloud based DB, you can use the App to export the whole DB as a ninox file.
- Open the DB
- Select the DB name from the left side
- Click on the Export data button
- Select Ninox as the option
- Type an appropriate name and save
Then make a new response then click on the paper clip icon above the Reply button.
-
1) How do you want Families Produit to be filtered by Type de selection? Families Produit is a dynamic multi choice (dMC) field pointing to Families_Produit table. The Families_Produit table is a child table of Parametres. Neither table has a field that has data that matches the choices in Type de selection.
-
In the "articles" table, I classify the different "article" by families.
The purpose of the "Product_Families" table is to allow the use of a filter during inventories to select:
- either all the articles
- or only those belonging to a particular product family.In the "Inventories" table I use a multiple-choice table a bit out of obligation, because I found a script allowing me to choose such and such a family that I adapted. It works exactly as I wanted, but I don't like the multiple choice soltion, I would have preferred a classic dynamic field (like in the "Articles" table).
-
hello fred
I found to replace the "dynamic multiple field" by a "single choice dynamic field" and adapt my formula.
However, I still haven't figured out how to fill in the table.
it going :) -
Take a look at the changes I made in the DB.
I made a new button and put this for the onClick:
let t := this; let xSel := record(Familles_Produit,number('Familles Produit')); if 'Familles Produit' != null then do as server let xArt := (select Articles where 'Familles Produit' = t.'Familles Produit'); for loop1 in xArt do let newRec := (create Listes); newRec.( Inventaires := t; Articles := loop1 ) end end else alert("Please select a family") end
Line 1 gets the data of the current record in the Inventaires table.
Line 2 is probably not needed.
Lines 3-16 is where the code first checks that the 'Familles Produit' field is not empty. If this is true then it will, on the server (line 4),
1) select all records (line 5) in the Articles table where 'Familles Produit' equals the 'Familles Produit' in the current record.
2) line 6, starts a for loop that takes the results from line 5 and for each instance it will create a record in Listes (line 7) and then link the two reference fields to the appropriate tables (lines 9 and 10).
3) if line 3 returns a false then it gives an alert.
I also removed the Display field only, if: for Listing (ajout), so you can see the added records to the Listes table.
-
Thank you very much Fred.
What a nice surprise to connect to my DataBase this morning and find a red button in my Inventory table.
You did what I originally wanted to do. Thank you so much.Clarification.
Initially I thought to launch an inventory of all items.
Then I said to myself that having the possibility of making a partial inventory by type of "Product Family" would be a plus. Hence the fact of having added the filter.What you have done is the possibility of making inventories by families (which seemed to me to be the most complicated).
Now I guess it is enough to replace the message [ alert("Please select a family") ] by a loop that displays all the articles?Thanks again
-
Didier Schoonjans said:
Now I guess it is enough to replace the message [ alert("Please select a family") ] by a loop that displays all the articles?You can do that. Do you need help with the loop? You can check out my response in a post for ideas.
-
Hello Fred,
I'll try to manage on my own (it's the only way to learn), if I don't, I'll come back to you.
And thank you again for your help.Didier
-
Hello Fred,
Well, I think I managed to figure it out. Anyway, everything is working fine.Do not hesitate to criticize me if it is possible to make the code simpler
I adapted your code to get by.
Part.1 (lines 1 to 3) Selection of "Manual" or "Automatic" modes
Part.2 (lines 4 to 16) Your code to display articles by "Product Family"
Part.3 (lines 17 to 22) Display of the warning message that no "Product families" have been selected
Part.4 (lines 23 to 34) Display of all articles
Part.5 (line 35) condition if you change your mind and finally want to select a familyNow I have two more things to do:
1) It is to add a new filter with the possibility of selecting or not the articles "not managed in stock"
2) ensure that once the inventory has reached status 3 (closed inventory) that the stock of items changes. -
In my article file I have a box that allows the management of the "stock" (formula).
The value of this box depends on the number of purchases and sales.
From this side everything works perfectly.My inventory cycle is now finalized (or almost :) ) and works well.
I have three columns:
1 = "Stock" which Indicates the stock value of the item (see PrintScreen below) which changes according to the different purchases and sales.
2 = "Quantité compté" which indicates the value physically counted during the inventory
3 = "Différence" which is a formula indicating the value that must be added to the "stock" to correct it
Formula => 'Quantité compté' - Articles.StockI made three examples:
A = an identical value (therefore no correction)
B = a smaller value (so the system adds a negative number)
C = a larger value (so the system adds a positive number)So the theory would simply => Stock = Stock + Différence
The problem is that if I modify the stock calculation formula (in the "Articles" table) the system blocks.
Normal because it calculates the stock using the stock so it goes around in circles.
The system should take the value "Différence" and not the formula included in "différence". And then I don't know how to do it.
-
Didier Schoonjans said:
So the theory would simply => Stock = Stock + DifférenceNinox is correct that you can't use a circular reference. Stock is a formula. Différence is based on Stock so you can't use Différence in Stock. You will have to create a new formula that takes Stock + Différence, so it is no longer a circular reference.
-
Didier Schoonjans said:
yes I understand the theory, but I do not see how to apply this in my caseI created a new field called 'Real Stock' in the Articles table and put this in the formula:
Stock + first(Inventaires.'Différence')
But then that brings up the issue of which Inventaires will you want to be linked to the Real Stock formula? I choose first, but I guess last would be better as that would be the most recent inventory.
-
Didier Schoonjans said:
I would like the stock to live, keeping the history of all movements. Exactly like purchase orders and sales invoices.I was thinking that stock shouldn't live in Articles but in Inventaires. Articles is just a description of the item. The number of items is not really related to the item itself but to Inventaires.
-
Didier Schoonjans said:
t is necessary that the system keep a chronological trace for each case (purchases, sales, inventory and manufacturing)If this is necessary then you need to create tables that store the appropriate data. For example you could do (these are just quick thoughts):
Article table - tracks name of item, descriptions, but not sale price
Catalog table - which is what you use to put items from Article in your invoices, this is where you store pricing,
Catalog items table - sub table of Catalog where you can add multiple Articles to make up an item in your Catalog
Invoice table - where you create invoices based on items in Catalog
Inventory table - could be a sub table of Catalog or stand alone,
-
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.
Content aside
- Status Answered
- 1 yr agoLast active
- 46Replies
- 440Views
-
3
Following