0

Storing array in a table column

Practice DBase.ninox

Hello friends,

I am new to Ninox and learning to get acquainted to it.

I have a Parent/Child tables setup (Products and 'Product Details'). In the Products table I have T-shirt types (Round Neck for Men, Boat neck for Men, Boat neck for Women etc.) and in the 'Product Details' table I have stored details about each product in different columns like ('Product Code', 'Unit Price', Size and 'Available Colors'). Data in the 'Available Colors' field is stored using a Multi Choice Combo Box on the form. I don't exactly know how this data is stored internally inside Ninox, whether as text separated by spaces or as an array, but I can see all the available colors per product in the table view as text separated with spaces.

Question: If I were to use the Colors data (from 'Product Details' table) in a script, how would I be able to extract the available colors per product in an array so that I can process that colors data as required inside a script?

The Products and 'Product Details' tables are related in a 1:M relationship (Parent/Child) as the data inside the child table ('Product Details') is only possible if there is a corresponding T-shirt in the parent table (Products).

Now let's come to the 'Invoice Items' table. I want to be able to:

1.Backfill the Product Names from the Products table in a single dynamic choice field on the 'Invoice Items' form.

2. Now based on the selected product from the combo list on the 'Invoice Items' form, it should automatically  backfill all the available Colors from the 'Product Details' table (per chosen product) in a single dynamic choice field so that I can select one color per line item on the 'Invoice Items' form while creating an invoice.

There is a M:M relationship between the Invoices table and the Products table through the 'Invoice Items' table.

8 replies

null
    • Fred
    • 5 mths ago
    • Reported - view

    Welcome to Ninox.

    Since you used the word 'array' in your question, I am guessing you have some DB/coding background.

    You are correct, Ninox handles multi choice fields in arrays if you use the numbers(), note the plural form, command. If you create a new formula field in Product Details and put in :

    debugValueInfo(numbers('Available Colors'))

    You will see the choice number of your selections. Mine says:

    number([2,5,6])

    The square brackets tell you that Ninox has put the values of the selections into an array and that array is an array of numbers.

    Those numbers are the number that Ninox gave each choice as you added them to your multi choice field.

    To access items in an array you can use the for loop, or item(), or split(), or contains().

    I hope that helps for now. When I get another moment I'll try to answer the 2nd part of your question.

      • Database App Developer
      • vermau81
      • 5 mths ago
      • Reported - view

       Thank you sooo much. Yes I have been a software developer in the past. I have worked on some critical ERP software of an enterprise scale and developer their Finance and Inventory module (in Visual Basic 6 and Oracle 8i), but that's a thing of the past.

      Ninox is new and I am still in the learning phase. I am finding Ninox very interesting as well.

      I am actually making a small Invoicing solution (just practice). That is where I need to show (in the 'Invoice Items' subtable) the list of available colours (per selected product) in a single choice field, so that I select one color (from that combo box) that we are selling on that invoice.

      Thanks you,

      Vermaji

      • Fred
      • 5 mths ago
      • Reported - view

       Ninox uses tables and corresponding records as the base for their dynamic fields. Since your color field is a multi choice field then you can’t make a dynamic field from a multi choice field. If you make a color table then another M:M table between color and product detail then you can make a dynamic choice field based on color.

    • Fred
    • 5 mths ago
    • Reported - view

    Some thoughts:

    In your invoice item table you may want to make all fields either text or number fields, or fields that are not linked to other records. You would copy the appropriate data over. This way your invoices will always be a historical record of what was shipped. If you link to other records, if you change the other record then your invoices changes. Also it would allow you to delete items that you no longer carry without affecting your past invoices.

    Which means how do you create or modify your invoices? Look into dashboards. You can do a search for "Ninox dashboards" and there will be a few videos to watch.

      • Database App Developer
      • vermau81
      • 5 mths ago
      • Reported - view

       Thank you for the pronto reply.

      The first thing that will get chosen on the 'Invoice Items' form is the product that comes from the Products table. I want to make this field as a Single choice Combo (so that I can change the product if selected a wrong product by mistake). Now based on the product that I have chosen in the other fields will show the respective data from the 'Product Details' table (Private Key and Foreign Key concept).

      'Unit Price' is a formula field so that it gets filled automatically from the 'Product Details' table.

      Amount and 'Total Amount' fields are formula fields 'cuz they need to be calculated.

      Quantity, Discount and GST are simple number fields.

      The only problem seems to be with the 'Unit Price' field where data gets fetched from the 'Product Details' table. If I make the 'Unit Price' fields as number field, will I be able to populate it based on a formula?

      I will definitely look into the dashboards for the understanding as well. Can you please clarify where do I need to sear for the term "Ninox Dashboards"?

      Thank you,

      Vermaji

      ---------------------------------

      • Fred
      • 5 mths ago
      • Reported - view

       Thankfully Ninox did away with obvious keys. We don’t have to setup fields that we call a key to link records. We just create reference fields and Ninox handles the linkage. Once a record is selected, then we can just use the reference field name to access all the data from the other table. Very easy, Very clean. 

      Reference fields are always better to use than selects, because only linked records are referred.

      Once you setup your dashboard then you can copy any data you want into any field you want. As you probably know, you want to keep those users away from the raw data. :) I only have myself to worry about but I try to keep myself from hurting myself.

       

       said:
      'Unit Price' is a formula field so that it gets filled automatically from the 'Product Details' table.

      Keep in mind, with this setup if you change your ‘Unit Price’ then all of your past invoices will update as well, thus destroying the ability to go back and see what you charged back then.

    • Fred
    • 5 mths ago
    • Reported - view

    Have you looked at Ninox’s Invoice template for some ideas?

      • Database App Developer
      • vermau81
      • 5 mths ago
      • Reported - view

       I have just downloaded the Invoices template and checked it through. It is kind of bit basic and my requirements are a bit advanced. I completely understand what you are trying to advise about the 'Unit Price' formula field.

      One thing that comes to my mind is that I can write a script that gets triggered on the selection of the product and fills in the corresponding 'Unit Price' number field from the 'Product Details' table.

      This will not change the 'Unit Price' of the records in the future, since the script will only execute for new records.

Login to reply

Content aside

  • 5 mths agoLast active
  • 8Replies
  • 52Views
  • 2 Following