Continue counting from last item after break activity

Help
1

Hello,

I have a workflow that will loop through each excel row and match with web input fields. After certain activities i want to break from the for each loop and continue from the last excel row to ensure i am not doing it again. Unsure how to do it.
For example , there are 20 lines of data in excel files. Say i break from the for each/while loop after 1 row. i want to continue from second line. How do i continue from 2. ?

See here for my workflow LoopLoop.xaml (29.6 KB)

@ClaytonM Could you please check for me as well?

Thanks !

1 Like
2

Hi @whitestar,

You can create a column in the excel and mark that with some flag, like Completed inside the loop. When you are starting again you can check that flag and start if it is not has the value as completed.

3

that’s what i am planning to do . but it would create too much for each in my workflow. and will be hard to break the sequence :(. Also there’s a slight problem with my current workflow. my for each item is not working properly. on every 2nd condition it will go back to first columns bu then on 3rd time it will get the data.

4

Hello @whitestar

I see you have some problems with your workflow. So I will try to help explain them in a simple manner. :wink:

Best Practices (do as you develop and don’t leave until later):

— Rename all Sequence containers and activities to something unique, while also leaving the name of the activity. For example, For each acct in Account array or Assign Account array. By renaming these activities, you are able to navigate your code and locate exceptions much quicker.

— Be more consistent on your variable naming. If your standard is to use the data type prefix, do this for all variables and consistently use the ‘_’ or not. Also, maintain this standard consistently for arguments but include the direction prefix along with the data type prefix.
(I’m not a fan of the data type as a prefix, and think it’s out-dated and a 101 practice from 20+ years ago. So I don’t typically encourage the data type prefix, and much prefer grouping variables by something that describes their use, followed by the noun of what it’s used for and the verb of what it is doing or storing. However, whichever plan you have for variable standard, just be consistent.)

— Avoid using the index to reference a column in a row when your column names are reliable. For example, row("Name").ToString to reference the name of the row item.

— Annotate all variables and arguments so you can understand how the variables will be used.

— Annotate all For each blocks and If blocks so you can understand why you are looping or why you have an If condition and for what purpose

— Set the TimeoutMS property for all activities that use an element/selector. If the element “should” already be loaded then use a TimeoutMS of 0; if the element needs loading time, then use a TimeoutMS of a few seconds depending on expected loading time. Since the Attach Window should be the first thing it is waiting for, you should be able to use a 0 for the timeout of each element inside, unless they follow a button which causes a new form/page to have to load. Then, the next element would need a timeout, while the others following could use a 0.

— Your Attach Window selector is too generic and will attach to any IE window open. You should check this to ensure it is unique enough to only attach to the specific window associated with the web application. In addition, all elements inside should leave off the first line of the selector when they share that line with the Attach Window container, in order to use a partial selector. I am mentioning this because you are using the full selector for one of your elements I noticed, which means if you need to change the first line of the selector, it requires multiple edits rather than one.

— Remove all outer Sequences or Bodies that have only 1 embedded activity. For example, you have a For each inside a Sequence and that is the only activity, and in another place you have a Sequence inside another Sequence. Only times to use outer Sequences is when you have either A) more than one activity inside a block or B) a desire to group tasks together for better organization - Note: remember to rename the Sequence or Body blocks to identify what that sequence of actions is doing. EDIT: Adding a tip that you can use to fix this: select inside activity, ctrl+x to cut, then select Sequence or Body, delete, select inside block and ctrl+v to paste activity back in; this is most user-friendly way to maintain this standard I think.

— Avoid using the ‘idx’ property in selectors, unless there’s no way to get around it or if the selector is still unique anyway. For example, you have this in the Type Into using the index of an array, and you might be able to directly use the value in the array in an aaname property if available.

— Keep the logic organized in simple steps: 1) get entire data set, 2) filter data set to items to be processed, 3) Loop over filtered items, 4) Loop over columns only if necessary, 5) validate value of item and change value or throw BER if needed, 6) input item into application. So in most cases, you would only have 1 loop; I think you are over-complicating it.

— Each task that gets performed in an application should be implemented as a separate workflow and deployed into a Library. The Library would just perform the action for one item. This way, you can call this action for each individual item. Since, it will be a centralized Library with version control, you will be able to integrate it into any future projects. This should be done from the start of designing the pieces for a project, and I cannot stress this enough :grinning:

Problems to solve:

— Your Send Hotkey is wrong. using [k(down)] will not work for this as that is a string expression for the keystroke, but Send Hotkey does not receive expressions. You can only select the down key by clicking on the dropdown, and if you would like multiples, then copy and paste the activity or run it through a loop. Or you can use a single Type Into to perform the multiple key presses with the expression you had used. Note: you could probably leave the selector blank for this as long as your Attach Window is unique enough to allow the keystroke to only occur within the intended application.

— You are looping over the data set twice, and I think that is what you are trying to solve by adding a break. You will want to instead only loop the data set once. Unfortunately, the Filter Data Table activity outputs a new Data Table, therefore you lose the entire data set. If you would like to loop over the entire data set but also filter it, then you will need to use a .net solution that outputs to an Array of DataRows. For example, Assign ExcelDT.AsEnumerable.Where(Function(r) r("Name").ToString.Trim <> "" And r("Number").ToString.Trim <> "" And r("Reason").ToString.ToUpper.Contains("NO")).ToArray, and store that to a variable of type Array<Of DataRow>. You can also use a sql .Select() syntax, but it is not my preference.

I am not sure how your application works, so I can’t comment much more on the problems. It looks as though you are looping over a series of potential selectors containing “Surname#”, then typing in each selector containing the corresponding “Code#” with the letter ‘F’. There has to be a better way to do this… :thinking::grinning:

Without seeing more of the application or a more specifically defined problem, like what is it actually doing that is wrong. Please also make use of Write Line, Message Box, or Debug to see how activities and variables are used during the steps which are causing issues.

Regards.

4 Likes
5

thanks for all the tips and advised. we will try to do again and see how it goes.

6

It looks as though you are looping over a series of potential selectors containing “Surname#”, then typing in each selector containing the corresponding “Code#” with the letter ‘F’

Yes that’s what we are trying to do. Checking the whole selectors to find the match for 11 times then go to next page and do the checking again in next page if still not find the match data.

how to reset the counter after 11 times then repeat it 11 times again.

Thanks

7

Don’t use a counter if the items are in a list/array. If you have a list of 11 items, use a For each loop and it will only go through those 11 items. Then, use a Do While outside of it to click on the Next Page button.

But, if you must use a counter, you can reset it by using an IF activity with the condition like: counter > 11 (or >= depending on where your + 1 is located in the loop), then Assign it back to the start again.

1 Like
8

Don’t use a counter if the items are in a list/array. If you have a list of 11 items, use a For each loop and it will only go through those 11 items. Then, use a Do While outside of it to click on the Next Page button.

I am using for each to loop through the 11 items from the array list. here 's my step
For each array ( it will check for each 11 array list )
Get text from the list of selector
while data match
assign type into and click submit

then press Next if can’t find in first page

now i don’t know how to get back to For each from it.

Thanks You

9

From my understanding, you want to look for page, then type in the 11 items and submit. If the page wasn’t found, then click on Next, and try again.

So, there’s 2 ways really to do this: 1) use a Do While or 2) use a Retry Scope
The Do While will work but requires an exit path to get out of it, to avoid an infinite loop. My suggestion would be a Retry Scope, which allows you to use a Boolean as part of its way to retry or continue on.

For instance,

Retry Scope
    Element Exist activity // variable elementExists
    IF activity: condition: elementExists
        For each item in arrayVariable
            Type Into
            Click Submit button
   Else
       Click Next button
Condition section of Retry: Is True activity: condition: elementExists

and set the Retry delay to zero and retries to your desired number of potential Next page clicks. The “Is True” activity will be in the Workflow Manager Activity package if you don’t have that installed.

Essentially, (and as shown in the hover tooltip of the condition section) the Retry Scope will keep retrying if any of the steps Fail OR the condition section is Not True (for example if the element does not exist).

I hope that helps and makes sense.

Regards.

1 Like
10

Thanks @ClaytonM . I made it work with do while but as you suggest its definitely on infinite loop until the condition is meet. I am not familiar with the use of retry scope so i will give it a try to learn myself.

Could you tell me what it’s element exist activity for in retry scope ? it’s for the next button variables ?
I would like to keep trying until all the conditions are meet so number of retries would be unlimited.

Thanks for all the examples and suggestions.

11

It was to be used to look for a specific element on the page to go to the Type Into. If the element exists is false, then it would click the Next button and try again. This depends on what you are trying to do of course. If you want to look for all 11 items, then this would need to be different. In my example, I just used one Element Exists assuming that you could tell if you are on the right page by “one” of the elements.

Regards

12

@ClaytonM ,

Sorry about the next question. I want to update the related excel rows after “Submit” button. what’s the best way to do it.
After “Submit”
Read Excel file again
then assign dt ?
then write cell ?

Thanks you and time for your time

1 Like
13

Use Write cell to the respective row instead of reading/writing as datatable.

14

I used the write cell but somehow i can’t get out of the loop without using the break activity as it’s keep reading the each rows of the excel file to check all the “Incompleted” row to replace with “Complete”. That’s not what i wanted to do in my currne workflow but i will need to check each row in the later workflow.
here is what i do to update the colums

Read Range to excel file
then for each rows
Assign “completed” for status row in excel
then if "completed = “incompleted”

I will write “completed” to excel row but it’s checking all the “incompleted” rows to update the colums :frowning:

Thanks

15

May I know which framework you are using?

If you are working with Excel as the master data and okay to continue with REFramework, then try to use the following framework.

https://go.uipath.com/component/reframework-for-tabular-data

Using it inside the settransaction.xaml based on the status of the transaction you can write the cell, this will run based on the excel data. Until you run the process second time it wont check the same row again.

1 Like
16

This is a difficult question which I’m not even sure I have the right answer to just yet, because there are many things to consider, like will this be performed from multiple robots at the same time (or even potentially, or accidentally)? Do you want to create a new file or use potentially an existing one? Do you want to output all rows even if they have not been complete yet or only output the completed rows? Do you want formatting, header border/bold and filtering, column autosize, etc?

The simple answer would be, there are two easy options:
assuming your table is outputTable

  1. using Append Range
IF activity: condition: Not outputTable.Columns.Contains("Status")
    Add Data Column "Status"

Assign activity: row("Status") = submittedMessage

Retry Scope with retries at 100 and 00:00:01 delay
    IF activity: condition: File.Exists(outputFilePath)
        Workbook Append Range
    Else
        Workbook Write Range

This solution will only output one line at a time and no formatting when it creates a new file. It will create a new file if it doesn’t exist, or add to the existing file. This is an easy, reliable way to write result files that could be processed from multiple robots. It can be placed right after the “Submit” button

  1. use Write Range to output updated table
IF activity: condition: Not outputTable.Columns.Contains("Status")
    Add Data Column "Status"

Assign activity: row("Status") = submittedMessage

Retry Scope with retries at 100 and 00:00:01 delay
    Write Range outputTable or Excel Write Range outputTable

In this method, you would see all items which had not been performed yet. You could also do some formatting if you use the Excel Write Range. This is also assuming the Excel Write Range throws an error when the file is in use; in the past, this bugged out and saved the file to weird locations.

Again, reason I put Retry Scope in there, so there is a slight delay if file is being used by someone.

I don’t recommend using Write Cell since it requires a range, but you could use this inplace of the Excel Write Range, but not really needed since the Write Range will output the entire updated data back to the file.

EDIT: Also, I used an Assign activity in there, which allows you to update a column of a row inside a data table. Then, the table will have that row and status column updated.

I hope these tips helps.

Regards.

1 Like
17

hi @ClaytonM ,
Thanks a lot for detail explained. This will only be run on one robot. We will be using the same excel file as we used to grab the data to match it with web page data.

Do you want to output all rows even if they have not been complete yet or only output the completed rows?

I am not sure about this question but we only want to update the rows that have been selected to do the action on web page. And no, dont require any formatting/autosize . just want to replace “incomplete” to “completed” under “Status” excel colums.

I will try what you described and update you. Thanks again . It has been really helpful to me.

18

We are using reframework. not sure what you mean by master data but we are only using one Excel file to do all the data matching and updating of the rows.

19

@whitestar,

I have referred your input excel as the master data, what is your transaction item type in that REFramework.

Try to use the ReFramework for tabular data, it will have all the functionalities to handle data from excel.

20

HI @ClaytonM I tried those and still not working :frowning: . could you provide simple workflow example to see how to do that ? here 's what we are trying to do .
Say I have 10 rows that do the action out of 100 rows
After Submit Button

I want all those 10 rows status to “In progress” to “completed” leaving other 90 rows unchanged.

I am using multiple for each row to do the actions as well…
Thanks You