topic RE: Replace All Blanks in a collection with a specific Value in Product Forum

Replace All Blanks in a collection with a specific Value

vinodchinthakin — Mon, 17 Jan 2022 06:31:00 GMT

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
------------------------------

RE: Replace All Blanks in a collection with a specific Value

PvD_SE — Mon, 17 Jan 2022 07:28:00 GMT

Hi,

If it was a small collection, I would just loop through it and set the required value where needed. On larger collections this might be a more time consuming activity.

Perhaps action 'Update Collection' on BP object 'Collection Manipulation - Extended' will provide a solution to a larger collection. Unfortunately, but all too common on BP objects [!], the documentation leaves to be desired (#BPFIXTHIS). But some experimentation will probably reveal its functionality.

Check if any of the above works for you. If not, I'm sure a code stage will magically present itself later today. 🙂

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

RE: Replace All Blanks in a collection with a specific Value

devneetmohanty07 — Mon, 17 Jan 2022 07:44:00 GMT

Hi Vinod,

If you have a small collection, then loop within that and update the column value. Otherwise if you want to achieve the same via code stage see the 'Fill Blanks' action in the Collection Manipulation VBO where you will find the section of code written as:

Try extending the action by duplicating it and you can pass a input parameter called as Value of type text and modify the highlighted section as:

if isdbnull(dr(c.columnname)) Or CStr(dr(c.columnname)) Is String.Empty then

dr(c.columnname) = Value

This should be able to help you out. I have tested the same with both text and number type of column and it is working.

------------------------------
----------------------------------
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

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

RE: Replace All Blanks in a collection with a specific Value

vinodchinthakin — Mon, 17 Jan 2022 12:44:00 GMT

Hi Devneet.
I have already tried the same as you mentioned by tweaking the code before posting my thread, but it didn't worked.
I have missed OR condition, after including OR condition in code it works perfect. Thanks for providing the code 🙂

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

RE: Replace All Blanks in a collection with a specific Value

vinodchinthakin — Mon, 17 Jan 2022 12:51:00 GMT

Hi Paul. I couldn't find any VBO naming Collection Manipulation - Extended on DX. Can you share the link of the VBO.

Thanks

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

RE: Replace All Blanks in a collection with a specific Value

PvD_SE — Mon, 17 Jan 2022 13:23:00 GMT

Hi V,

The action indicated by me should be within your default installation of BP, not in DX.

But, as I expected, an excellent solution in the form of a Code stage was provided at short notice.

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

RE: Replace All Blanks in a collection with a specific Value

vinodchinthakin — Mon, 17 Jan 2022 13:37:00 GMT

Hi Paul, Action mentioned by you is not available in my VBO. Can you share the VBO or release file as an attachment. I would like to see if my VBO has any missing actions

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

RE: Replace All Blanks in a collection with a specific Value

devneetmohanty07 — Mon, 17 Jan 2022 14:41:00 GMT

Sure Vinod, it is always a pleasure to help you out 🙂

Also, the IsDbNull() function strangely does not pick the null value for me as well hence, came up with that approach.

If your query got resolved, then do mark the answer as the "Best Answer" so that it can be tracked easily by everyone for future reference.

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

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

RE: Replace All Blanks in a collection with a specific Value

vinodchinthakin — Tue, 18 Jan 2022 06:09:00 GMT

Hi Devneet.
I have tested the same code multiple times with both text and number type of column and it is working only for text type of column. Can you verify once again from your end.
one more thing, Can you provide the modified code where same logic can be applied only for a specified column instead of for a complete collection.

Thanks...

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

RE: Replace All Blanks in a collection with a specific Value

devneetmohanty07 — Tue, 18 Jan 2022 09:03:00 GMT

Hi Vinod,

I tested the same on a collection having both a Text and Number column as well as with a collection having only Number column or Text column. In all these cases it is working. Please find the below screenshot for your reference:

Inputs:

Outputs:

Code:

try
for each dr as datarow in blanks.rows
for each c as datacolumn in blanks.columns
if isdbnull(dr(c.columnname)) Or CStr(dr(c.columnname)) Is String.Empty then
dr(c.columnname) = Value
end if
next
next
no_blanks = blanks
catch e as exception

end try

Parameters:

Cross check once from your end if you are using the same code and parameters along with the type of inputs.

For the second requirement that you have asked, there are few things to keep in mind before I proceed with the solution. There is a concept of default values in VB .NET and C# when you specifically talk about Data Columns which is like if you are generating a table with a column type as "Int32" or "Decimal" or any equivalent numeric class type, it won't allow the data to be filled with Null values and instead will auto populate the table with the default value that in this case would be "0". Similarly, for Boolean class types, the default value would be "False". This is being implemented so as to maintain the integrity of the data types and not have any run time error because of that. Logically as well if you see, blank or null is not an allowed numeric type data so it is should not allowed and that is exactly what is being implemented by auto populating an equivalent of blank which is 0 in terms of number however, from a business point of view "0" always won't signify lack of a data but might be a number having a real life value.

Hence my suggestion would be to treat all you data table columns as Text data type only and use this action in order to get a proper result, otherwise you will find the default values getting auto populated. In the above use case, this would not have been an issue since we were replacing all the blank values but here as soon as you replace some selected fields (blank fields) in a particular column, that moment the other columns would get auto populated with default values which may not be something you would want.

For the second solution add an additional input parameter to the above use case called "Field Name" and modify the code to:

try
for each dr as datarow in blanks.rows
if CStr(dr(Field_Name)) Is String.Empty then
dr(Field_Name) = Value
end if
next
no_blanks = blanks
catch e as exception

end try

Try this and let me know if it solves your purpose.

------------------------------
----------------------------------
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

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

RE: Replace All Blanks in a collection with a specific Value

PvD_SE — Tue, 18 Jan 2022 11:10:00 GMT

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
------------------------------

RE: Replace All Blanks in a collection with a specific Value

vinodchinthakin — Tue, 18 Jan 2022 11:28:00 GMT

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
------------------------------

RE: Replace All Blanks in a collection with a specific Value

vinodchinthakin — Tue, 18 Jan 2022 11:39:00 GMT

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
------------------------------

RE: Replace All Blanks in a collection with a specific Value

devneetmohanty07 — Tue, 18 Jan 2022 12:15:00 GMT

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

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

RE: Replace All Blanks in a collection with a specific Value

vinodchinthakin — Tue, 18 Jan 2022 13:53:00 GMT

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
------------------------------

RE: Replace All Blanks in a collection with a specific Value

devneetmohanty07 — Tue, 18 Jan 2022 14:35:00 GMT

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

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