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
-
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 endYou 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
-
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') endOr make 'Product Items'.'Product Code' a formula field that is
Products.'Product Code'Then there's no need for a script
Regards John
-
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 fragmentationI don't think anyone is saying using reference fields in dynamic fields is the cause of the issue.
-
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')) = productCodeBut originally I wrote this code:
Do as server let productCode := upper(trim(Product.'Product Code')); select 'Product Items' where upper(trim('Product Code')) = productCode; endand 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 ?
Content aside
- 11 days agoLast active
- 13Replies
- 66Views
-
4
Following