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
-
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.
-
thank you !!
Works!!!!I want to iterate over 15,000 times ... is it possible?
-
How long could it cost to do it?
-
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.
-
In the Mac application it just took 15 minutes. Is it because of the code? Will I need to debug it?
-
Difficult to say without seeing the database. I was able to process 4 times the number of your records in less than a minute.
-
I have seen differences between the Mac App and the Web Client.. The later being slower.
-
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!!
-
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 2select
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 theselect
statements and use those values instead ofselect
statements.
Content aside
- 4 yrs agoLast active
- 9Replies
- 1002Views