0

Iteration

Good Morning:

I have a problem performing calculation.

I need to make a database with the stock of my references. These are located in two warehouses. If I use functions everything works fine but slow since it performs the calculations continuously and slows down the operation of the database.
For this reason I have thought of creating a button to make the calculation save it in numeric variables.

I have a table where all items are registered, with or without stock in one or both warehouses. This table is called 'Catálogo'.

In another table I am accumulating records with the date, the article, the warehouse and the stock, only if there is stock. This table is denoted 'Stock diario'

What I would like is for the button program to search for the last stock of each item in both warehouses and save it in a numeric field within the item tab in the 'Catálogo' table.

 

For this I have written the following code that does not work:
 

the reason to limit the catalog to laboratory number 1 is to limit the number of iterations and obtain information on its operation or not more quickly.

let myCat := (select 'Catálogo' where Laboratorio = 1);
let myRF := last(select 'Stock diario');
let myF := myRF.Fecha;
Fecha := myF;

for i in myCat do
let myRegCastelar := last(select 'Stock diario' where Fecha = myF and 'Almacén' = 1 and 'Catálogo' = myCat);
let myNC := cnt(myRegCastelar);
if myNC = 1 then
myCat.('Actuales Castelar' := myRegCastelar.Actuales)
else
myCat.('Actuales Castelar' := 0)
end;
let myRegCastelar := last(select 'Stock diario' where 'Catálogo'.'Nr.' = myCat and Fecha = myF and 'Almacén' = 2);
let myNC := cnt(myRegCastelar);
if myNC = 1 then
myCat.('Actuales Castelar' := myRegCastelar.Actuales)
else
myCat.('Actuales Castelar' := 0)
end;
myCat.('Actuales número' := myCat.'Actuales Castelar' + myCat.'Actuales Castellón')
end
 

 

Any help for iteration?
 

9 replies

null
    • Sean
    • 3 yrs ago
    • Reported - view

    As far as I know, you can't modify an array, myCat, the way you are trying to. I would leave out myCat and use this as the first line of the for-loop...

     

    for i in (select 'Catálogo' where Laboratorio = 1) do

     

    ...and replace myCat with i inside the for-loop.

    • Antonio
    • 3 yrs ago
    • Reported - view

    thank you !!

     
    Works!!!!

    I want to iterate over 15,000 times ... is it possible?
     

    • Antonio
    • 3 yrs ago
    • Reported - view

    How long could it cost to do it?
     

    • Sean
    • 3 yrs ago
    • Reported - view

    In the Mac app, less than a minute. In the browser version, I don't know if it will allow you to loop through that many records at once, but I would expect at least 20 minutes.

    • Antonio
    • 3 yrs ago
    • Reported - view

     
    In the Mac application it just took 15 minutes. Is it because of the code? Will I need to debug it?
     

    • Sean
    • 3 yrs ago
    • Reported - view

    Difficult to say without seeing the database. I was able to process 4 times the number of your records in less than a minute.

     

    https://ninox.com/en/forum/use-cases-5abd0b9c4da2d77b6ebfa395/duplicate-marking-utility-for-large-datasets-5e2d30194e9f3771e468dd52

    • Mconneen
    • 3 yrs ago
    • Reported - view

    I have seen differences between the Mac App and the Web Client.. The later being slower. 

    • Antonio
    • 3 yrs ago
    • Reported - view

    Good afternoon:

    the last times I have done the calculation is about half an hour ... I use the App for Mac.

    Any ideas?

    Thank you!!

    • Sean
    • 3 yrs ago
    • Reported - view

    Hi Antonio,

    I have found in my own experience that the performance starts to slow down when iterating through a table with 5,000 records and 1 select statement per iteration. You have 2 select statements per iteration. Based on the code you have posted and my incomplete understanding of your database structure and code, I would create a separate table to write static values to that are equivalent to the result of the select statements and use those values instead of select statements.

Content aside

  • 3 yrs agoLast active
  • 9Replies
  • 989Views