Unanswered: Exiting a 'For Each...' command when certain conditions are met
In the scripting menu, under the 'Add Cycle...', there is a command called 'Break'. What is the real purpose of this? Is it used for debugging or can it be incorporated into the actual scripting? I was looking for a command that I could use to exit out early of a 'For Each...' command (the program looks at each record and compares it to another variable and when a match was found, I want to get out of the cycle and go on with the rest of the scripting) - is that what the 'Break' command is supposed to do? There is absolutely no mention of this command in the user's guide.
I tried using this in my scripting and as long as multiple 'For Each...' commands are NOT used it seems to do okay; however, if nested 'For Each...' is in the scripting structure, it "breaks" the first time a match is found and goes to the end of the program - which is not the desired course of action. Given this example and the behavior I witnessed, I am inclined to think that this is not the correct application of the 'Break' command.
How does a person exit a 'For Each...' loop once some criteria is reached??? Secondly, what is the actual purpose of the 'Break' command???
Have not used "break" frequently and possibly not in a nested loop but your interpretation is the same as mine. I may be reading your post incorrectly but your words above --when "a match was found, I want to get out of the cycle" and " it "breaks" the first time a match is found and goes to the end of the program" -- seem appropriate action? Might be easier to see copy of your script to work out problem , assuming you have not found a bug.
I use 'break' in If-endif. Check something, if something wrong do not execute script, I use for this.
Exiting out of For..Next i am using; Goto->Label.
Again, you are spot on!!! I tried the Goto... Label actions and these worked flawlessly. The naming convention is what confused me because I would have never even thought to consider these options. However, after your suggestion, I read the section in the user's manual covering the usage of the Goto... Label, it made perfect sense. I then tried this in my scripting and it worked perfectly.
Sorry, I must update this as I was stepping line by line through the scripting this morning. The 'Go To... Label' does not work properly - in fact, it does nothing at all. The program does not even recognize these two commands. I have a 'For Each Record...' command and inside of this is an 'If' statement. The 'If' statement check to see if two values are equal, if not the next record is examined. When the values are the same, I used the 'Go To... ' statement. Just below the 'Next' command, I added the 'Label...'. This has absolutely no effect on the running of the program. Why is the program not exiting out of the 'For Each Record' command? Below is a partial and abbreviated version of my scripting:
[$name_new]=[$employee] (for Completed Courses)
[$all_employees]=Folder "Employee Lists" content (Employee List)
For each record from [$all_employees]
[$single_employee]=[employee_name] (for Employee List)
If Employee List And ("[single_employee]="[$name_new_value]") then
Clear Field (Employee List: completed_courses_MMR] Go To exit_now:
Next Item exit_now:
When stepping through the individual lines of scripting, the program recognizes when the conditions of the 'If' command are met but it continues looping through all of the other values in [$all_employees] totally ignoring the 'Go To...' and 'Label...' statements. I reviewed the instructions in the user's guide for these statements (pages 121 and 123) and it seems simple enough but it just does not do anything. Why???
Although the "goto" command should work I try to avoid it as I am sure years ago it was not encouraged in programming. I think you can run in to stack overflow problems in multipe loop situations. Mean while can only suggest you use the message box action placed in multiple positions (before ,after the goto etc) with your own message plus any variables to get an idea of what the program is seeing.
If get time will try building a test file to experiment as I would like to know my self if there are any problems with "break" or "goto".
Rather than a true 'Go To...' (as the name suggests), the behavior described in the user's manual seems to indicate that this command can be used like an 'Exit For' command that is used extensively in Visual Basic. This would work great in my application - I even tried to incorporate this VBScript into my coding but Brilliant Database did not like this and produced an error statement. Earlier, I did try the message box, that contained many variable inside, to see how the 'Go To...' script was actually behaving. The program did not even acknowledge the 'Go To...' and 'Label...' lines - it treated them just like a comment statement... it completed skipped over them.
There should be a relatively simple means to accomplish this because if the program is looking for a match in 5,000 records and if it finds this match on the 12th iteration of the 'For Each Record...', there is absolutely no need to examine the other 4,988 records. I think that the 'Go To...' command could do this, at least in theory (and based on the example in the manual), provided that it actually worked.
I went back and started a new database using the example in the user's guide as a design basis. If the 'Go To...' statement resides inside an 'If' statement, it CAN actually exit the 'If' statement; however, based on my own results, it CAN NOT exit a 'For Each Record...' command as these 'Go To...' statements are simply ignored.
I am curious if anyone here has used the 'Go To...' and 'Label...' commands inside a 'For Each Record...' statement AND has verified line-by-line in the debugger that it actually works?
Last edited by fireant911; 03-06-13 at 18:08.
Reason: updated test results
Just being experimenting with ideas on this and did have some problems but may have been my logic - brain a bit slow after full days work.
What I did try (and seems to be working with the break action) is to set a maths variable to 1 (true logic) just after the "if action" test for matching values and follow with else to set a notes field to some text in not matching. Now I added another if test to see if this maths variable is true and if so run "break". Then followed by next to loop back to next record. This may or may not work for you but it is not too hard to test.
Rough example script ----
[$all_set] = Current folder (Inventory Item)
[$true_var] = 0
For Each Record From [$all_set]
If Inventory Item And ([Item Name] Contains "new") then
[$true_var] = 1
Inventory Item: [Notes] = " Must be old model"
If [$true_var] = "1" then
Inventory Item: [Notes] = "not old"
Show Message (Missed break point- should not see this ??,Opps, Ok)
Thanks. I had tried something similar to that and it did work. The issue I was experiencing was that in my program I have two nested 'For Each Records...' (so that I can check a list of items against dynamic valuables), when it encounters the 'Break' command inside the inner 'For Each Record...' statement, it completely 'breaks' from the scripting. I only can get the first answer using the 'Break' method (i.e., it works GREAT if only one 'For Each Record...' exists but it fails when nested 'For Each Record...' are present). In reading and rereading the instructions for the 'Go To... Label...' commands it seems as if this should work... but it does not, at least when used with a 'For Each Record...' command. Why would it work for an 'If' statement but not for a 'For Each Record...' statement. I have tried every possible combination of this but it simply is not acknowledged by the program???
The program does work as it exists but it is doing a MASSIVE amount of unnecessary searching (98+%) because it cannot cease searching once a match is found. I did send an E-mail to technical support at Brilliant Database yesterday describing this odd behavior - I will post any response I get from them (if I get a response).
Hi Daryl G
Would it be possible to build a sample file with your script (commented before each line) and post to this forum?
The forum members may be able to come up with a solution or suggest a "alternative" to achieve the same result - often there is, although finding the fastest way is the challenge. Even a HTML script dump from BB may give better understanding of problem.
Here is a basic program demonstrating my problem. The attached file has three folders. Folder 1 is a list all employees (there are five employees in this example). Folder 2 lists all training that has been completed by the five employees (there are a total of 15 completed trainings). Folder 3 is all the different training courses (there are unique 10 courses). The purpose of this is to select an employee and click on the ‘find hours by employee’ to see how much training time each person has accomplished. Please understand that this example is used just to describe my problem – it is not real.
Stepping through the program line-by-line - Using ‘Daryl’ as an example and clicking the button shows that there are 15 completed trainings ([$all_completed] and this individual represents three of those [$filtered_by_selected_employee]. Next, all the training courses are assigned a variable of [$all_courses]. The program then uses a ‘For Each Record…’ to step through the ten courses to see if there is a match to the first course Daryl completed to each course as it cycles through. If a match is found, the program pulls the ‘hours required’ for the course (listed on the ‘training’ folder). This sequence is repeated until all three of Daryl’s completed trainings are finished and a message box appears with the total hours.
My problem is that once a match is found (lines 8, 9, and 10), I want to be able to exit of the inner loop and go to the next completed training for Daryl. As I understand the usage of the ‘Go To… Label…’ commands in the user’s guide, I should be able to put a ‘Go To…’ statement after the statement is true (right after line 9) and put the ‘Label…’ just outside the inner ‘Next Item’ (right after line 11). But this has ABSOLUTELY no effect on the program as it still continues to check for a match after a match has already been found. See attached screenshot for the locations where I used the 'Go To... Label...' commands. This is not a problem in a small program like this but it could present a problem if hundreds or thousands of courses do exist (a problem in regards to the calculation and running time of the program) since many unnecessary comparisons are being made.
How can I make the program go to the next completed training once it has found a match (rather than continuing to cycle through all the remaining courses)? Note that using the 'Break' command is NOT a viable option since it causes the program to finish immediately - this would work for a single 'For Next' statement but will not work where multiple, nested 'For Each...' commands are present.
I would certainly appreciate any suggestions on how to exit a loop when a match is found!!!
Do not know if this is any help but there is a "continue" statement in the cycle menu. Can not find any example of use in help file except that it used in loops and is a companion to "break" (Under whats new in Brilliant Database V9). Apparently used in java and unix looping scripts etc but mode in BB could be different - any members familiar with this command?
Just another idea instead of loops. Assuming I have a handle on your requirements. If your end goal is to total up hours each employee has invested in all courses completed ,might it be possible to include a look up field in your "completed activities form" for hours of course and then total this column for each employee ?
There would appear to be a one to many relationship for employees to completed activities and a one to one with completed activities and courses (for look up of hours). Very easy to sum hours in related records to employee.
Very rough example attached - may have errors.
Last edited by tamcind; 03-09-13 at 05:07.
Yes something wrong with Goto-Label. Sorry for my wrong.
But also something wrong with your way to solve your problem. You are trying to filter selected course but not using filtering command. You are cycling all records to find that record. I think you can filter that courses and take hour of filtered record. (If there is more than one course with same name in trainings folder, filtering not work. Is this likely?)
I think best solution is tamcind's suggestion.
(In my opinion you are staying stuck on an idea. You should try other ways always.)
The 'Continue' command, just like the 'Break' command, as I understand it, are used only when debugging and should not be used in the actual program.
In regards to my example... as I hoped to convey in my earlier post, it is strictly an example to demonstrate my problem. I realize that there are better ways to actually accomplish that task - the example is NOT a real task... I just wanted to show what it is doing and the fact that the 'Go To... Label...' does NOT work, well, at least not in this application.
I think, at least with my limited BD knowledge, that my real scripting must be handled in a similar fashion to that of which I posted (the actual scripting for that function is in excess of 100 lines long and has to retrieve information from several folders so the example was a baby-version of what the scripting does). I still need a method to stop execution of the 'For Each Record...' command once the 'If' statement has been satisfied. On average, it can be assumed that given the existing behavior of the scripting, that the program is doing at least 50% of wasted action.
If there is not a method to cease execution when the conditions are satisfied, I will accept this; however, I cannot imagine that the absence of such a command was an oversight - this is used extensively in Visual Basic (Exit For). The program still functions great it is just having to do too many unnecessary comparisons. I am just planning ahead so that when more people and more data are added to the database that things do not slow down during the execution of the scripts.
In looking at tamcind's example, that greatly simplifies things but, in my case, it will not work. Part of the functionality is to download/export archived files from EXCEL. There are 10 thousand+ of these right now and the list is growing. I needed to develop a method to insert these records to one central location and have the program disseminate these to the proper individual(s). Along with downloading, the scripting verifies the legitimacy of the employee and course. Going to each individual employee and updating their specific records is not an option because of the time required for this endeavor. When clicking the 'find hours by employee' button on tamcind's example, my system slows down considerably as it takes about six seconds for the program to arrive at the solution - in a larger database, this would not work because of the time required. My solution, as rough as it was, arrived at the answer almost immediately. I am also interested in why you included a 'backup' command - why do you recommend this?