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,

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

[devneetmohanty07]

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

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

[vinodchinthakin]

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

[vinodchinthakin]

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

[PvD_SE]

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

[vinodchinthakin]

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

[devneetmohanty07]

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

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

[vinodchinthakin]

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

[devneetmohanty07]

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

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