Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2012
    Location
    Huntsville, AL
    Posts
    379
    Provided Answers: 2

    Unanswered: Reinitializing a field value en masse

    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?
    Daryl G

  2. #2
    Join Date
    Jul 2012
    Location
    Ireland
    Posts
    815
    Provided Answers: 17
    Personally, I wouldn't use a “For Each... Next' loop” at all for this. I'd use a simply Query to initialise the value.

    For example, write a query that does this. If field “count/frequency ” is not = to 0, then field “count/frequency ” = 0.

    So that by default will reset one record to 0.

    You can set the query to do this for every record greater than 0 by going to the Actions Tab of the query window and ticking the box “execute a script for each record”.

    Regards
    James

  3. #3
    Join Date
    Sep 2011
    Location
    Australia
    Posts
    264
    Provided Answers: 2
    Hello
    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.
    Regards
    David `

  4. #4
    Join Date
    Jul 2012
    Location
    Ireland
    Posts
    815
    Provided Answers: 17
    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.
    Funny enough it's something that can be done in the blink of an eye. For a script like this, Brilliant Database will process about 20,000 records per 1 second on a 2GHz Dual Core machine.

    Only reason I know this off the top of my head is because I have a few databases already going this.

    Regards
    James

  5. #5
    Join Date
    Dec 2012
    Location
    Huntsville, AL
    Posts
    379
    Provided Answers: 2
    James,
    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!

    David,
    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.
    The
    Daryl G

  6. #6
    Join Date
    Jul 2012
    Location
    Ireland
    Posts
    815
    Provided Answers: 17
    On a second note: has anyone ever had any success with using the 'Go To... Label' to exit a loop?
    Yes, but I don't remember having any problems with it.
    Can you post an example of something that doesn't work ?

    Regards
    James

  7. #7
    Join Date
    Dec 2012
    Location
    Huntsville, AL
    Posts
    379
    Provided Answers: 2
    James,
    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.
    Attached Thumbnails Attached Thumbnails go to label.jpg  
    Daryl G

  8. #8
    Join Date
    Dec 2012
    Location
    Huntsville, AL
    Posts
    379
    Provided Answers: 2
    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?
    Daryl G

  9. #9
    Join Date
    Jul 2012
    Location
    Ireland
    Posts
    815
    Provided Answers: 17
    Sorry for the delay in getting back to you.

    It appears that once a 'For Each' loop is started, it cannot be stopped early regardless of the conditions.
    Interesting....
    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.

    Regards
    James

  10. #10
    Join Date
    Dec 2012
    Location
    Huntsville, AL
    Posts
    379
    Provided Answers: 2
    James,
    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???
    Daryl G

  11. #11
    Join Date
    Jul 2012
    Location
    Ireland
    Posts
    815
    Provided Answers: 17
    I tried that with exactly the setting you stated and it still goes to the other sheet.
    That shouldn't happened. I suspect the "Execute Query" function is not the cause of this. When you are exciting the query, are you running other scripts at the same time?

    If so, can you post a screen shot of your code so I can have a look?

    Regards
    James

  12. #12
    Join Date
    Dec 2012
    Location
    Huntsville, AL
    Posts
    379
    Provided Answers: 2
    James,
    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.
    Attached Thumbnails Attached Thumbnails query issue.jpg  
    Daryl G

  13. #13
    Join Date
    Jul 2012
    Location
    Ireland
    Posts
    815
    Provided Answers: 17
    Ahh... good man. I suspect you found a bug and the way around it.

    Small list is set by default, but regardless of what it's set to, the options you made sure that were not ticked should have ensured it never jumped to the folder you were running the query on.

    Glad to hear you have it working as you need now.

    Regards
    James

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •