Replace All Blanks in a collection with a specific Value

[vinodchinthakin]

Hi All.
I would like to replace all Blanks in a collection with a specific value. Do we have any solution for that.
I have tried the action fill blanks but it fill blanks with Zero's.

------------------------------
vinod chinthakindi
------------------------------

[PvD_SE]

Hi V,

This is what we have:
...in our BP supplied object list.

The object '...- Extended' contains these:
...actions.

We're at 6.5.1 today. Dunno what version this object was added so I'm not gonna send it. If missing in your installation, check with BP.

------------------------------
Happy coding!
Paul
Sweden
------------------------------

[vinodchinthakin]

Hi Devneet,

 In my case the code is able to replace blanks with Zero but not for other values for Number column type for both first and second requirement. In my case its  auto populating the table/column of number type with the value "0"(irrespective of input new_value).  I still cant able to figure it out the issue. May be its an issue from my end. ( Note: I have restarted my Object and BP tool also)

Thanks for detailed explanation on default values for nulls/blanks for different data types.

------------------------------
vinod chinthakindi
------------------------------

[vinodchinthakin]

Hi Paul,
Thanks for sharing the screenshots.
From above both Screenshots, I am able to understand its a customized VBO, may be someone within your organization/team has developed those VBO's. Because I can see few actions from screenshot are not available in default VBO's of BP.  OR may be I am missing an updated version of collection VBO.!!!

------------------------------
vinod chinthakindi
------------------------------

[devneetmohanty07]

Hi Vinod,

Apologies as there has been a mistake at my end. The code that I have shared you actually never goes within the If condition I observed after few error and trials. I was not able to recognize probably because of the Try and Catch block which was already present in the original code which I should have removed prior to testing this. 

So ideally what was happening is when the code ran, it will initialize all numeric cells with 0 since it's a default value which I explained you earlier. So here actually, we would need to compare the value against 0 instead of String.Empty if the data type of the column is a Number and rest can work for the Text type of columns. Hence, I have completely modified the code now which you can also give a test with other values apart from 0.

Since I was testing with 0 so I also got confused and thought that the code was working even when there was an error which I could not see because of the Try and Catch block which was already implemented.

for each dr as datarow in blanks.rows
   for each c as datacolumn in blanks.columns
      if c.DataType = GetType(Decimal) then
         if isdbnull(dr(c.columnname)) or dr(c.columnname) = 0 then
             dr(c.columnname) = Value
         end if
       else if c.DataType = GetType(Boolean) then
         if isdbnull(dr(c.columnname)) or dr(c.columnname) = False then
             dr(c.columnname) = Value
         end if
      else
         if isdbnull(dr(c.columnname)) or CStr(dr(c.columnname)) = String.Empty then
            dr(c.columnname) = Value
         end if
      end if
   next
next
no_blanks = blanks

------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant
Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

[vinodchinthakin]

Hi Devneet

No worries, Even I make a lot of mistakes in my coding. Now your code works perfect !.
I have modified your code as per my second requirement for specified column and it also works perfect!!! .
Here am sharing the code, might be useful to someone. Just I have replaced c.columnname with Field_Name

for each dr as datarow in blanks.rows
for each c as datacolumn in blanks.columns
if c.DataType = GetType(Decimal) then
if isdbnull(dr(Field_Name)) or dr(Field_Name) = 0 then
dr(Field_Name) = New_Value
end if
else if c.DataType = GetType(Boolean) then
if isdbnull(dr(Field_Name)) or dr(Field_Name) = False then
dr(Field_Name) = New_Value
end if
else
if isdbnull(dr(Field_Name)) or CStr(dr(Field_Name)) = String.Empty then
dr(Field_Name) = New_Value
end if
end if
next
next
no_blanks = blanks

One thing I would like to know what If condition can be provided if the data type is date/time/date time.?
------------------------------
vinod chinthakindi
------------------------------

[devneetmohanty07]

Awesome Vinod, glad that it got solved and thanks to your for sharing the updated solution, just to add it's better to have all the columns as text only while using the second action as anyhow the default values will get populated for other columns if they are not of text type which might not be always an ideal scenario.

Also, do remember to mark the answer as the "Best Answer" if it resolves the query so that others can track the answer easily.

------------------------------
----------------------------------

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant
Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------