Date Custom Fields in Custom Reports and Batch Operations

There is a bug related to dates that afflict many aspects of Manager, ie Custom Fields / Custom Report / Batch Create / Batch Update.

The premise is that my preferences manager are:

Screenshot_20200620-115054__01

so my date format is dd/mm/yyyy and the same is in Excel.

Here comes the problems that are all linked:

  1. custom reporting shows date of custom fields with a different date format;
  2. when I export a list for batch update I get two different date format, a correct one for transaction date and wrong ones for custom fields. When I import them in excel, it automatically converts them in the correct format but with errors when the day is <= 12 since it cannot distinguish which is the day and which is the month. When I import it back it completely messes up the dates;
  3. when I export a list for batch update I have also problems with text custom fields. I use a custom field to store suppliers’ invoice numberings that are all in different formats. For example I can have 10/2020. When pasted into Excel they are converted into a date “Dec/2020” and so when imported beck I get this text messed up.
  4. when I batch create some data I got the same prime as point 2 for dates in custom fields since it switches days and months if days are <=12.

I am putting this into bugs, but only for item #1. The others are behavior of Excel.

Once all custom.fields are formatted well there should not be any issue.

However, about point 3 you cannot say it’s an issue of Excel because it is a fundamental part of the process of butch update.

My statement was based on your description:

Dec/2020 appeared because of action by Excel. That is not a date format recognized by Manager, so it naturally did not work when pasted back in. But if Excel did not misinterpret or translate the date into the Mon/YYYY format, you would have a recognizable date to paste back. That is not a bug in Manager. It is the result of Excel’s programming and Excel’s attempt to interpret something it may not understand correctly.

You are right. I’m not saying in any way that it’s Manager fault. But, since you cannot use something different from Excel for batch create/update, you cannot say that is not a bug.

Maybe adding a ’ before every string when it’s a text can solve the issue. Maybe it cannot be solved. But it’s worth a further investigation by @lubos in order to make batch create and update fully functional.

Otherwise batch functions will mess up data.

1 Like

I think it is an issue of Excel preferences and regional differences in its versions.

Excel cell format allows settings the date to the format Manager recognises. The default date format in Excel can also be set but it normally maintains the date format imported data

Define that column in Excel as Text, not leaving default General, and Excel will not do any of his “automatic” transcription of data. I also have several fields like yours, and have been there where you are struggling now.
Also for 2 and 4. Define by hand, text or date/time field and format of column. Don’t leave as general or something that excel should guess and do automation.

1 Like

Yes but it doesn’t work with two different date’s formats as it happens with custom fields. Excel changes the second one.

And then, the real problem happens when you put the dates back with batch update.

Thanks. I’ll try it.

But it’s a solution only for point 3 under my point of view.

First of all because formally there should be only one date format: the one that I setup in Settings. This is clearly a bug as stated in point 1. What you do it’s a workaround.

But secondly and mainly… for dates what if you need to (re)calculate some them in a custom field in Excel with a particular formula? Or insert/update data in a new custom field’s column based on the date you have in another custom field? You need to set them as dates and consequently it will mess your dates when put back in Manager. Keep in mind that I’m talking mainly about batch operations where the main scope is to apply a massive update/creation of data reducing manual work.

Yap, maybe it is bug in custom fields. I have no such field with date values to test. But nevertheless I would never let Excel autodetect important fields. You may define that custom field as Date, with proper format in excel and do calculations/formulas. But if you say so, than it is a bug. Can you paste a short report lines 5-6 for testing, and original data screen?

In the initial implementation of custom fields, all custom fields were just text fields. Date and number types were kind of bolted on top of text fields. But internally all custom fields are still text fields. So these are not really bugs, just how the implementation works.

I have new implementation of custom fields in mind which will make date/number custom fields behave the same like similar built-in fields. This will resolve this issue and about 5 other topics in ideas category related to custom fields. So I think this is better suited to be in ideas category too.

2 Likes

Hope to see the full implementation of new custom fields very soon. I love the direction Manager is taking during the last few months even though I’m struggling with many problems.