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:
so my date format is dd/mm/yyyy and the same is in Excel.
Here comes the problems that are all linked:
custom reporting shows date of custom fields with a different date format;
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;
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.
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.
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.
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.
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.
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.
We are also facing the same issue with batch update particularly with date field not only for custom even voucher issue date.
I was thinking about the same as mentioned by @Tut
Whereas later statement clarifies its not about that.
As the matter has already been taken up, we will appreciate your assistance and guidance to manage in between.
In addition, we recently witness that data have been entered in custom field and saved. Later, details were extracted through batch update for some reports and analysis and noticed some ambiguity in flow of information for validation a live system was referred again and surprising no details were view beside it shows empty. Just to revalidate, the exercise was done again unfortunately results of both exercises were same. I hope there is no reliance issue in data integrality.
