I have 2 sheets in excel.
In the first sheet,there is a Sales order and attribute name & value
In the second sheet, there are multiple attributes name & value but only one attributes is matching with the attributes of 1st sheet. That is row number 3.
"Override is datetable of 1st Sheet and GCSR1 is datatable of 2nd sheet.
In the filter wizard I keep only Type column with “GCSR” and in the write range activity BOT will write after filter in the 3rd sheet.
it will be like this:
In the Filter wizard I have written like this:
Note: After run in Debug mode it is giving me all Green Tick but data not filtering and not written in Sheet 3.
Instead of using the write range activity in the for each row. Switch that to an Append Range. This will add the new values to the end of the 3rd sheet. The write range activity is rewriting over the previous data each time the loop is executed.
Perhaps it might be best to start with some light debugging. Could you add breakpoints to your filter data table, and then another one on your write range? Then run in debug and, using the watch panel, see what GCSR4 is as it goes through your workflow, specifically at these spots. You may also want to watch both of your conditional values (row.item(“column3”).tostring). I’m slightly curious if those are simply false and going to else, thus no data in dt.
So, when it breaks at the filter activity, you can see the correct values for GCSR4? What about when it reaches the append range? If you break there, does GCSR4 still have the same values?
I have declared GCSR4 as a output filter in Filter Wizard as you can see.
I have added Write Range Activity now and able to see only Header no more filtered data.
The issue is at Filter data table inside your 2nd for loop. The thing is you’re trying to filter the datatable each and every time when the if condition is getting satisfied. and Each time when the IF condition is getting satisfied you’re filtering the fresh GCR1 table and storing it from scratch into GCR4 because of of which are getting nothing.
Solution : instead of filtering the GCR1 you create a datatable on top of 1st for loop with the schema you want and then use ADD DATA ROW activity to the created datatable inside the IF block.
Refer the below picture for the error pointing.
When debugging, do you ever see it actually execute the “then” in both of those “ifs”? Or does it go to the else? Its possible that is constantly false, and therefor no data is being filtered and added to the GCSR4 DT. Could you verify that it actually does show true for both of those ifs?
We can try to debug this and figure out what needs to be done, if you’d like. To start, I notice that you are using a nested for each loop. Both loops are using “row” as the variable to iterate through. Lets try renaming one of those to currentRow. That will allow us to more easily see what may be happening with each loop. I’ll attach a screenshot to illustrate. Also, if you can add a few breakpoints, as shown in screenshot, and debug. Once in debug, open “Watch” panel and add “row.item(“Column3”).ToString” and “currentRow.item(“Column3”).ToString”. Once you hit the breakpoint on the if, see what the values are for each of those in the watch panel. If those values never match, its likely not comparing what you intend.
Alright! If row is iterating through your Override DT, can we double check that dt to make sure the right column is being referenced and all data exists? You can simply add Override to that watch list. Then, once it gets to the same break point, click on the magnifying glass icon next to override. This should show you the Datatable and all rows in it. Are the headers as you expect? Is the data all present as expected?
Both the date table giving me the correct data. Below is from Override dt:
Below is from GCSR1 dt.
Now, I guess both the below expression are not giving me correct result. row.Item(“Column3”).ToString = row.Item(“Column3”).ToString row.Item(“Column4”).ToString = row.Item(“Column4”).ToString
Okay. So from what I can see, Column3 has no value in it. If you look at the output for the OverrideDT, you can see that Sales_Document has a value of “607975297”, Sales_Document_Item has a value of “110”, Name has a value of “Disposition Level”, and Value has a value of “Tier 2”. This all makes sense from your prior screenshot. However, if you notice, Column1, Column2, and Column3 are all empty values (that is what the “,” is showing, between each , is separating a value for a column). Which value are you attempting to reference? “Disposition Level”?
Remember that one of your iterators is likely now currentRow. So, to test if that is going to be true, it should be currentRow.Item(“Column3”).ToString = row.Item(“Column3”).ToString and currentRow.Item(“Column4”).ToString = row.Item(“Column4”).ToString.
This still likely won’t work correctly as Disposition Level does not equal DISPOSITION_LEVEL and Tier 2 does not equal TIER 2 (this latter issue could be solved with something like tolower). However, this should help to narrow down the correct columns to reference… hopefully.