Filter Collection adds value in Datetime column

jpcrepeau

Member
Hi,

I am importing multiple items from a CSV file into a collection. I have defined the fields to be able to set 2 fields as DateTime to be able to do calculations.

I have a field called ETA Date that is a DateTime field. not all the items in the collection have a value for this field.

I do a filter collection on another column of the collection. When I opened the new filtered collection, the ETA Date field that were blank values now show 0001-01-01 05:00 AM

When I trying to write this collection to Excel, it is telling me the field value is not valid. I cannot figure out how to get BP to stop putting a value in there and I also cannot figure out how to blank out the values that are 0001* (I tried a calculation field but it will not let me put a blank value or a space since it considers it putting text in a DateTime field).

any of you can help me? I attached screenshots if it can help

thank you in advance!
 

Attachments

  • RPA Filter 01.JPG
    64.7 KB · Views: 73
  • RPA Filter 02.JPG
    36.7 KB · Views: 58
  • RPA Filter 03.JPG
    35.9 KB · Views: 53
  • RPA Filter 04.JPG
    85 KB · Views: 58

sivagelli

Well-Known Member
hello,

I am not finding the DateTime Field showing 001-01-01 when tried replicating Filter Collection in my machine with BP v5.

If possible place dummy values in to other fields except DateTime and share the CSV file.
 
Hi,

I am importing multiple items from a CSV file into a collection. I have defined the fields to be able to set 2 fields as DateTime to be able to do calculations.

I have a field called ETA Date that is a DateTime field. not all the items in the collection have a value for this field.

I do a filter collection on another column of the collection. When I opened the new filtered collection, the ETA Date field that were blank values now show 0001-01-01 05:00 AM

When I trying to write this collection to Excel, it is telling me the field value is not valid. I cannot figure out how to get BP to stop putting a value in there and I also cannot figure out how to blank out the values that are 0001* (I tried a calculation field but it will not let me put a blank value or a space since it considers it putting text in a DateTime field).

any of you can help me? I attached screenshots if it can help

thank you in advance!
I have found setting collection fields as DateTimes very difficult to handle and always store them as text now. Would it be possible to store the values as text up until the point you need to do a datetime calculation and then convert them using ToDateTime at only that point?

I have ran into problems such as time zone discrepancies between how Blue Prism interprets datetimes and how custom code stages interpret them - e.g. a collection action creates a 1 hour time difference each time it is called.
 

jpcrepeau

Member
hello,

I am not finding the DateTime Field showing 001-01-01 when tried replicating Filter Collection in my machine with BP v5.

If possible place dummy values in to other fields except DateTime and share the CSV file.

Hi!

I realize now that I was so deep into the coding that I totally forgot to try with another source for the collection! Unfortunately I cannot share the file, but I will try with another source and test.

thank you very much for testing on your infra. I am on 5.0.30.

J-P
 

jpcrepeau

Member
I just double checked and there are spaces in the empty values in the CSV files. So the cell is not blank, it has " " in it.

thank you all for your help! :)
 
Top