0

Selecting Product Items based on Product

Hello

I have a Reference field where I select the main Product from Products table. Product Items is a child table of Products. I have added a button (Update Records) and a new column (Product Code) in the Product Items table.

What I want to achieve:

On the click of button, I want to copy all product codes from Products table into respective child Product Items.

Reason:

On another form (QuoteForm) I want to filter the child Product Items in a DCF element based on the Product chosen in the reference field on QuoteForm. I have been advised by the veterans in Ninox and Ninox technical support that it is not a good idea to filter records using reference in a DCF element as it creates a chaos during data de fragmentation in import of the database to iCloud (specifically) from Ninox cloud.

So I have decided to use SELECT statement in DCF element on QuoteForm to Filter the corresponding child records from the Product Items table based on the chosen product in the reference field (on QuoteForm) from the Products table.

Code:

let allProducts := (select Products);
for i in allProducts do
    let allProductItems := (select 'Product Items')[this.Product.'Product Name' = i.'Product Name'];
    for j in allProductItems do
        j.('Product Code' := i.'Product Code')
    end
end

I know this is not the most optimized code 'cuz when I ran it, it took forever to copy the Product Code in 600 product Items. I had to force stop and found there are still 400 more to go. So basically this code was running very slow.

Is there a better code to achieve this simple task?

After copying the ProductCode in Product Items, I will go to QuoteForm and put a SELECT query to match all the Product Items with the Product selected in the reference field using the Product Code field from both the tables and display those Product Items in the DCF (trying to avoid using reference in the DCF).

13 replies

null
    • John_Halls
    • 2 mths ago
    • Reported - view

    Hi

    Your DCF used product.'Product Items' so why aren't you using that in your code?

    let allProducts := (select Products);
for i in allProducts do
   let allProductItems := i.'Product Items';
   for j in allProductItems do
      j.('Product Code' := i.'Product Code')
   end
end

    You could also try wrapping it all in a do as server command as this will still take a while.

    Also, as an aside looking at your original code when using a select statement

    (select 'Product Items' where this.Product.'Product Name' = i.'Product Name')

    will run much faster as it is scoped as part of the select command inside the brackets, where

    (select 'Product Items')[this.Product.'Product Name' = i.'Product Name']

    is scoped after the select command has found all your product items.

    Regards John

    • John_Halls
    • 2 mths ago
    • Reported - view

    Just realised, you could swap it all round and have

    let allProductItems := (select 'Products Items');
for i in allProductItems do
   i.('Product Code' := Products.'Product Code')
end

    Or make 'Product Items'.'Product Code' a formula field that is

    Products.'Product Code'

    Then there's no need for a script

    Regards John

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

       Thank you John. The formula element thing worked like charm. I completely missed the formula element. Also on the side I was always of the opinion that if we use a formula field in any situation, it doesn't work in the background. It only works on the current record when it is accessed.

      But I tried the formula in this one and checked the data in the table form rather than on the Form and it filled all the records automatically in the formula field.

      Best part I just picked up the 'Product Code' from the reference field (Product). Correct there was no need for the script. So one problem solved. Just one point in my mind. Isn't the formula field just like DCF. If it is then that means at the time off defragmentation and uploading the database to iCloud it will scramble the product codes in the Product Items.

      But the second problem still remains.. Write the most optimised code to back fill all the relevant Product Items in the DCF using a SELECT statement based on the Product chosen in the reference field. I want to write a bullet proof code that works in all situations (no matter where we upload our data to --  local hard drive or iCloud or Ninox Cloud).

      What can be the most optimised and fast code (using SELECT) to backfill in the DCF with relevant based on the Product selected in the reference field?

      Please help !!

      • Alain_Fontaine
      • 2 mths ago
      • Reported - view

       if this kind if "defragmentation" really happens, we can hope that the "Reference" fields are updated accordingly, otherwise the whole database would loose its head. If so, the solution could be to store the identity of the selected record in a "Reference" field, created for this purpose.

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

       So what you are suggesting is that we store the ID of every record in a PrimaryKey field on the creation of a new record so that we can use it later. Well we can reference it later in other fields, but the DCF is still going to point to the ID field and not to our PrimaryKey field, so the problem will still remain.

      • Alain_Fontaine
      • 2 mths ago
      • Reported - view

       No, that’s not the idea. The actual idea is to use the DCF only as a UI element to select the Product Item, but to store the identity of the selected item in a reference field. This reference field is thus the only long term storage of that identity. This is not easy to explain in words, so I did build a small example DB to demonstrate it.

      Test DCF to Reference.ninox
      • John_Halls
      • 2 mths ago
      • Reported - view

        I have a feeling you already have this, and if you have keep this going. Also to update the out-of-sync DCF use code like

      for a in (select Table) do
   a.DCF := a.ReferenceField
end

      Test it first on a backup though!

      Regards John

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

       I checked the sample DB and actually liked the way you have stored the value of selected Product Item ( 'Item Name' ) in the reference field. I am using the SELECT statement though to filter the Product Items in the DCF.

      Just asking out of curiosity, which one is better, fast and more optimised

      1. SELECT statement to filter the records everytime whenever the product is selected

      OR

      2. Storing the reference from the DCF in the reference field

      • John_Halls
      • 2 mths ago
      • Reported - view

       Reference field every time. Use select statements as sparingly as you can, although any decent sized system will end up having some.

      Regards John

      • Alain_Fontaine
      • 2 mths ago
      • Reported - view

       Indeed. The "select" statement is very powerful, but it is by far the most inefficient tool in Ninox.

      Going on step further, once you have a "Reference" field, you can get rid of the DCF entirely. How? By using the "Constraints" option of the field. Demonstration here:

      Test Product Item.ninox
    • Fred
    • 2 mths ago
    • Reported - view
     said:
    I have been advised by the veterans in Ninox and Ninox technical support that it is not a good idea to filter records using reference in a DCF element as it creates a chaos during data de fragmentation

    I don't think anyone is saying using reference fields in dynamic fields is the cause of the issue.

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

       You remember I told you that the support has strictly told me that we should not use DCF in a situation where there is a possibility of deleting records in the future 'cuz the records gets scrambled up during defragmentation and database upload to iCloud. This is what happened with me when I copied the office database from Ninox cloud to my personal iCloud.

      Ninox support confirmed to me that when we download the data from Ninox cloud and upload it either back to some other Ninox cloud account or local hard drive, data remains intact (defragmentation does not scramble the data), it only affects in case we upload that database to iCloud. I don't understand why this happens in case of iCloud.

      So I decided not to use reference in ( Product.'Product Items' ) in DCF and rather use SELECT statement so that in the case of defragmentation (no matter what platform the database is uploaded to) the DCF  will always select Fresh data based on SELECT statement.

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

     I used the SELECT statement in the DCF to filter 'Product Items' and I think it went pretty well. Now the DCF shows all relevant filtered 'Product Items' based on the chosen Product in the reference field.

    I have written this code:

    let productCode := upper(trim(Product.'Product Code'));
select 'Product Items' where upper(trim('Product Code')) = productCode

    But originally I wrote this code:

    Do as server
     let productCode := upper(trim(Product.'Product Code'));
     select 'Product Items' where upper(trim('Product Code')) = productCode;
end

    and everytime I would scroll up or down in the Table View, it was refreshing the data in 'Item Name' column (that represents the DCF on the Form). It was damn slow and scary. It was like every single record that came in focus on the table view was filtering the 'Item Name' column and refreshing the data on the browser or the Mac app.

    So I removed the "Do as server" line and now it is not refreshing the data in 'Item Name' column in Table View when I scroll up or down.

    Can you please throw some light what was happening here? What does this do as server thing do? How did the data become stable after removing the do as server line?

    On the contrary when I was writing the code to copy the 'Product Code' from the Products (papa) table to the corresponding items in the 'Product Items' (child) table, using this Do as server line helped in copying the data faster. Without this line, it took 10 mins to copy 'Product Code' in 600 items and I had to cancel the operation thinking that the app stopped responding.

    So when to use "Do as server" and when not to use ?

     

Login to reply

Content aside

  • 2 mths agoLast active
  • 13Replies
  • 86Views
  • 4 Following