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:
    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?

    • Didier_Schoonjans
    • 1 yr ago
    • Reported - view

    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

    • Fred
    • 1 yr ago
    • Reported - view
    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.

    1. Open the DB
    2. Select the DB name from the left side
    3. Click on the Export data button
    4. Select Ninox as the option
    5. Type an appropriate name and save

    Then make a new response then click on the paper clip icon above the Reply button.

      • Didier_Schoonjans
      • 1 yr ago
      • Reported - view

      Fred 

      Hello Fred,
      Thank you for your reply,
      I use a "starter" version for 1 user via Browser.
      I contacted Ninox support, to see with them what they can do to help me create a copy of my DB.
      I will come back to you as soon as I have an answer from them.
      Have a good day
      Didier

      • Didier_Schoonjans
      • 1 yr ago
      • Reported - view

      Fred 

      NINOX support tells me that I can simply invite you to my database.
      For this I need your e-mail address which must be linked to a NINOX subscription.
      If you want to give it to me outside of this thread here is mine.
      dschoonjans@soundprocess.eu

    • Fred
    • 1 yr ago
    • Reported - view

    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.

    • Didier_Schoonjans
    • 1 yr ago
    • Reported - view

    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).

    • Didier_Schoonjans
    • 1 yr ago
    • Reported - view

    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 :)

    • Fred
    • 1 yr ago
    • Reported - view

    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.

    • Didier_Schoonjans
    • 1 yr ago
    • Reported - view

    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

    • Fred
    • 1 yr ago
    • Reported - view
    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.

      • Didier_Schoonjans
      • 1 yr ago
      • Reported - view

      Fred 

      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.Stock

      I 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
    • 1 yr ago
    • Reported - view

    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

    • Didier_Schoonjans
    • 1 yr ago
    • Reported - view

    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 family

     

    Now 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.

    • Didier_Schoonjans
    • 1 yr ago
    • Reported - view

    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.Stock

    I 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.

    • Fred
    • 1 yr ago
    • Reported - view
    Didier Schoonjans said:
    So the theory would simply => Stock = Stock + Différence

     Ninox 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
      • 1 yr ago
      • Reported - view

      Fred yes I understand the theory, but I do not see how to apply this in my case.

    • Fred
    • 1 yr ago
    • Reported - view
    Didier Schoonjans said:
    yes I understand the theory, but I do not see how to apply this in my case

     I 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
      • 1 yr ago
      • Reported - view

      Fred Good morning,
      I would like the stock to live, keeping the history of all movements. Exactly like purchase orders and sales invoices.
      So a solution with two different stocks is not suitable.
      A bit on the same principle, I tried to create an additional column in the inventory and then to base my stock calculation formula (in the "Items" table) on this column, but that doesn't work either.
      It's really complicated because as soon as we use a formula, we inevitably base ourselves on the stock and therefore when we modify the result, Ninox automatically recalculates the stock and therefore we are necessarily in a loop.
      I wonder if we will not be forced to change our approach.

    • Fred
    • 1 yr ago
    • Reported - view
    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
      • 1 yr ago
      • Reported - view

      Fred 

      I don't quite understand what you are explaining?

      The stock of an item changes, depending on:
        - purchases
        - Sales
        - inventories (compulsory at least once a year)
        - fabrications (that's for later)

      It is necessary that the system keep a chronological trace for each case (purchases, sales, inventory and manufacturing)

    • Fred
    • 1 yr ago
    • Reported - view
    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
      • 1 yr ago
      • Reported - view

      Fred 

      Finally, it is what already exists. I have :
        - A table for purchases (with a sub-table that stores the data)
        - A table for sales (with a sub-table that stores the data)
        - A table for the inventories (with a sub-table which stores the data)
        - An item table
        - A customer table
        - A supplier table.
      It all works well

      The "Purchases" and "Sales" tables work very well and keep the history of purchases/sales.
      The 'Inventories' table is based on the same principles and works well, the problem is finding the trick to value the stock (and that's only where I'm stuck at the moment).

    • 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 

      To explain everything to you in detail.
      The stock lives with the movements of purchases and sales, hence the fact that the "Stock" field is necessarily a formula.
      To file its tax return, a company is obliged to make an inventory of all of these items at least once a year. This must be validated and approved by company auditors. Once validated, the inventory corrects the stock. And we start again on a new year.

      Then, I still have to take care of a new table "Nomenclatures" (Bils of material). Who will also have to manage the stock. But hey, everything in its time.

Login to reply

Content aside

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