Is there a more efficient method to clear a field on a form rather than using a 'For Each... Next' loop? I have a folder that has approximately 5,000 records. In this form, a field holds the count/frequency that a common name is encountered. At the beginning of a function, the very first step is to clear out the old values and return the field to a value of zero and then the new count/frequency is calculated. Is there any method that I can use to reinitialize that value in all 5,000 records at once rather than have the program cycle through 5,000 times to do this?
Does this mean your need to check each record for matching names in the whole folder (5000) and set a numerical field to the current matching count status for each record?
Do you need to clear the field instead of just over writing with new value? If this check was done on each manual data entry the process with a query would likely be quite quick but an import might require a looping query which would most likely take some time.
Just some thoughts.
Earlier in my Brilliant Database journey (just last month), I had a major aversion to using queries within Brilliant Database for anything. However, after you analyzed one of my projects and identified what was wrong, I have been attempting to re-educate myself with the use of queries. I did not even consider reinitializing the values with queries... which makes perfect sense and should be considerably quicker than using a 'For Each... Next' loop over the 5,000 records. I will do that today!
It is not feasible to do this on each manual entry since it is a dynamic function and the user can easily change the entire search parameters so the clearing of the fields has to be done whenever the process is started. Clearing the field is required because the scripting is examining for values to show in a Many-to-Many Relational field.
On a second note: has anyone ever had any success with using the 'Go To... Label' to exit a loop? This function simply did not work in v9.42 and I was really hoping that this had been corrected in v10.5. I submitted the bug last year and received a response from Tom at BD as he was able to replicate the issue.
I was using the 'Go To... Label' command within a 'For Each... Next' loop. As the script was being executed, if the values with line 22 were equal I wanted to exit out of the inner loop (lines 20 - 25) and go to the next outer loop. I originally place the 'Go To' portion inside the 'If' procedure (just below line 23) and the label just after line 17. I then discovered it does not matter where the 'Go To' is placed within a 'For Each... Next' loop because these commands are totally ignored. My project is wasting a lot of time trying to find a match when a match has already been made. It appears that once a 'For Each' loop is started, it cannot be stopped early regardless of the conditions.
I am now revisiting my original question on this post (my apologies for going off base). I still need to reinitialize a field value for all records within a folder. I can easily set up a query to do this as James recommended and when I tested it everything looked good. However, when I include this into my scripting (using the 'Execute Query' action) the project goes to this other folder (the folder that contains the field that I am making all to be zero) rather than staying on the folder that holds the scripting. Then, lots of errors appear. I do not need to go to this other folder, I just need to change a certain field in all of those records to zero - this is how it is done in my crude 'For Each... Next' statement... but when using the query it wrecks havoc to my project. I tried adding one additional line of scripting to send the program back to the folder that holds the script ('Select Folder') and although that works, it looks very rough. Is there a way to run the query from one folder and have the program stay on that originating folder?
It appears that once a 'For Each' loop is started, it cannot be stopped early regardless of the conditions.
I must take a look at that.
However, when I include this into my scripting (using the 'Execute Query' action) the project goes to this other folder (the folder that contains the field that I am making all to be zero) rather than staying on the folder that holds the scripting.
In the Query Editor on the Appearance Tab, make sure the tick box “show query results in” is NOT ticked. Also on the Actions tab of the Query Editor make sure “Automatically select the first result from the query” is NOT ticked.
The Query should run silently in the background regardless of what record type you have selected. It should not transport you to some other record / folder.
I tried that with exactly the setting you stated and it still goes to the other sheet. This is odd because I am now using queries for Recordsets and they perform properly; however, this one (Execute Query...) after it runs goes back and stays on the folder where the query runs and not the run that initiated the process. It would be grand if it did not because the method you are suggesting is more efficient. I am pondering as to why it is doing this because when I used my earlier scripting (For Each.... Next) to change those fields, it did this seamlessly with never bouncing to the other folder???
I made an interesting discovery this morning concerning this issue with queries. I went back and revisited this problem again and tried one more thing. Surprisingly, this seemingly insignificant change worked but I really do not understand why. Just like you suggested, I made sure that ‘Show query results in:’ was not checked in the ‘Appearance’ tab; however, this time I also changed the default value of ‘Main Table’ to ‘Small List’ (see screen shot below) and it then behaved exactly as desired. I am really, really curious as to why this minor change worked since the ‘Show query results in:’ was unchecked and this appears to be tied to adjacent dropdown list of ‘Main Table, Small List’… and I would assume that this would have no impact on the behavior of this??? I have never made a change like this to any of the other queries I have used.
As a test, I changed it back to 'Main Table' while the 'Show query results in:' remained unchecked and, again, it do not work properly.
I am new to the usage of queries because, mistakenly, I did not entirely believe the results that I was seeing early on in my Brilliant Database journey. Now, I am starting to feel much better about understanding, using, and trusting these results. I was trying to do everything with scripting and, although this works in many cases, it had its limitations as my database grew in size and complexity.