while loop stops before reaching end
Hi everyone,
I created a button in one table which is to export some values to another table creating new records there. I am using a while loop to create the records. But even a simplified version of the loop breaks before reaching the end.
I want to create records for each day between two dates (start, ending). The code looks like this:
let start := date(2022, 1, 1);
let ending := start + 100;
let d := start;
while d <= ending do
let newRec := (create DateSeries);
newRec.(Date := d);
d := d + 1
end
I would expect the button to create 100 records. But only around 10 to 28 records are created (varies).
I am running the desktop app (v3.6.6) on an M1 Mac (macOS 12.4). Does anyone have an idea what is wrong here?
Thanks a lot!
-
Hi
I tested your code and it worked fine for me. How odd.
Regards John
-
John Halls Hi John,
thank you for looking into it. If it stopped at the same position every time I push the button, I could understand it. But if you tell me that it works for you, I believe it is some kind of bug related to the system I am running.
Regards, Alex
-
-
I too created the DateSeries Table
Copied the above script into a button on one of my other forms.
and pressed go!
It created the table no problem - id rec from 1 - 101 (i'm guessing the while does a loop at 0 or does all 100 and then commits suicide on run 101 ? or was start given the initial value of 1 then you added 100 to it - )
Your script looks good to me!
ps i'm on cloud version !
-
Mel Charles Thank you for the feedback! My result looks like this (27 records only).
But if I create a new table, insert the button there, it works as expected (101 records). Only In my existing table I get the weird results.
Since the script is ok, it must be some correlation behind the scenes. I also notice that it takes much longer for the script to execute. If I run the script from another table, it is almost instantly done. Run from the initial table it takes around 3 seconds to finish.
-
-
Hmmm a bit lost how to advice you further other maybe you ask support to do a screen share and take a look !
-
I have had another go at this and to be honest I cannot get it to fail.
I am still interested why the code run created 101 lines.So if i have it right the first line already gave the variable start the value of 1- then then you added 100 to it. thus giving 101- am i right? (I don't really use while loops!)
What happens if you lower the rate to say 50 records - does it still only create 10 to 28 records
-
Or if it is not too sensitive - how able you load a copy of your db/table here and we can try that db locally and still see if it screws up!
-
Mel Charles I uploaded the db/table here. If I click on the 'TestButton' in the 'Main' table, only <30 (of 100) entries are created in the 'Test' table.
If I delete all the function fields in the table, everything works well. But I don't see any correlations between those function fields and the button.
-
-
There are a fair number of select statements in the function fields and I would think they are the primary reason for your button failing.
Regards John
-
Just tested your db and its working fine for me both for test and main records.
-
Well, I've given up on ninox but still reading the forum. I tested the database and the 1st time it gave me 28 records, the second time it gave me 31 records. I'll pay more attention when I get some time. I too running the desktop app (v3.6.6) on an M1 Mac (macOS 12.4). I hope it helps... -
Also tested your imported DB. Like John's test it worked fine.
However - When I tested your script on your opening thread (i copied it) it created the records in the blink of an eye. Yet using your DB it touch a fair bit longer to do so.
So i'm inclined to agree with John in that maybe all those select statements are causing too many loops and not giving you enough memory headroom...
in your new table you created do you still have all those select statements?
-
Mel Charles Yes, if I leave all the fields including the select statements, it gives me only around 28 records. After removing the fields it works as expected.
What I don't get is what all those select statements shall have to do with the execution of the button script. I don't see any correlation between them. This is why I believe the Ninox code is somehow flawed. On the hardware side there shouldn't be any problem. I got an M1 Ultra Mac with 64 GB of RAM.
I have no idea how to circumvent the problem other than limit my tables and scripts. The other thing is that I don't get any error message. So I can never tell if a script executes correctly or if certain values Ninox returns are correct. I don't know if I can trust the output. This concerns me more than the original issue itself.
-
-
Nick - If not using Ninox anymore what have you migrated onto?
-
Mel Charles gone back to Filemaker.
-
This is definitely a weird one. I have the Mac app on my old (2015), but still functional MacBook Pro. I get the same results as schwiegel and Nick when I use the while loop, but it works correctly when I use a for loop. This is the code I used...
let startDate := date(2022, 1, 1); for i in range(0, 99) do let newRec := (create Test); newRec.(Date := startDate + i) endIf I execute the code from the Main table it takes a long time to complete like schwiegel mentions and if I execute it from the Test table it completes almost instantly. I agree that executing the code from the Main table shouldn't be affected by the 162
select statements since it is only writing records for the Test table. It's behaving like every formula is being refreshed during each iteration of the loop 
-
@sean Have to admit I do like Filemaker but my single copy is far too old now. and I have no desire to take on a min 5 licence or pay the extortionate fee for a single use copy
-
Mel Charles Sean I have a few FileMaker clients and also use it, together with Ninox, for my own business. Love ‘em both.
-
-
I use the Mac app (v3.6.6) and the same thing happens for me. Main only creates 12 records while Test creates all 100. If I change the ending date to less than 12 then it creates 12. If I do 13 it only creates 12.
I would say to email Ninox support (support@ninox.com) to ask them why the while is acting funny.
schwiegel , as many have mention, too many select statements is not a good thing to do, regardless of this issue. What I have learned is to make one select statement into a variable and then use that in my scripts.
I see that you do a select Main, a lot. So in each formula field you could create a variable at the top for the select Main and then replace all other instances of the select with the variable.
Mel Charles , I think it creates 101 records, when working, is because the while command is based on the start date plus 100, so that should create 101 records (the start date is Jan 1 and 100 days from that would be 101 days) if I'm doing my math correctly.
-
Fred I mailed Ninox to address this issue.
You're right. I need to optimize my code. Thank you for the suggestions!
-
-
schwiegel It will be very interesting to see what Ninox support say in their response....
