Solved

Copying Date Fields

  • 29 November 2021
  • 6 replies
  • 80 views

Userlevel 3
Badge

When you have an inquiry, you can copy-paste the data from the fields in that inquiry into a screen.

I use Purchase data to build Sales Orders and I need to copy-paste a date. If EVERY digit is filled in, this works like a dream. If not, this goes very poorly. Can we force zero’s on dates so that copy-paste works as intended and doesn’t just give out bad data?

 

Example of a BAD date: 1/3/2022

Example of a GOOD date: 01/03/2022

 

Here is the issue:

 

SOURCE INQUIRY (copying ETA field)

 

DESTINATION FIELD Due Date, after copy-paste (without exiting the field):

 

If you click or tab out of the field:

 

So now instead of copying the correct date, it slapped in the day of “today”, but on the proper month, but then also applied the wrong year. Obviously, people who are constantly copy-pasting are going to miss this, and it creates a large burden of work on our end. Can we get dates to work like they should please? Hopefully, I’m just missing a setting somewhere :D

icon

Best answer by markusray17 30 November 2021, 19:10

View original

6 replies

Userlevel 7
Badge +12

Hi @Michael Hansen  In Acumatica, when you copy and paste the dates, and if we have ZERO before the other number like 01/03/2022 system will ignore and will show 1/3/2022 and if we have other than zeros like 11/10/2022 and then it will as it is and I don’t see any issues with this behavior.

 

I’m missing anything here?

Userlevel 3
Badge

I copied 1/3/2022 from an inquiry into a date field on a screen, it output as 1/29/2021. That’s the issue in a nutshell.

Here’s my limited knowledge of data parsing:

IF I had copied 01/03/2022 into the date field, it would have worked. The issue (I think) is that the system is taking in date values improperly.

The date we’re copying into Acumatica is 1/3/2022.

Here’s how I see this:

I’m assuming this gets read in like a compiler where you have a “string” of data to read in.

I copy the date into Acumatica and this creates a string of data “132022” (it appears to ignore the slashes). If I put my cursor at the front of the date and hit PASTE the first digit is now set as “1”, then the second digit is set as “3” in the second slot of the MONTH. But a month can’t have the value “13”. Again, the system is ignoring the slashes you as a user thought would break up your numbers. The system stops processing at this point and rolls back the last bad command (creating month 13). Now the date is set as 1_\__\____. The system will now auto-fill the rest of the date where the day = today() and the year = ThisYear(). My output is thus: 1\29\2021 (since today is the 29th in 2021). 

 

You can test this claim reasonably well, by carefully placing your cursor between the 1st and 2nd digit in the month segment of the date we’re pasting this info into. The system outputs the following when you hit paste: 1/3/2021. Again, this happens because the data string starts with a “1” and is assigned to the 2nd digit slot for month. Perfect, we wanted January. Now the system is parsing the 2nd digit in the date input string (which is a “3”) as the 1st digit in the DAY segment. So far the system has built _1\3_\____. This is looking good, but since the 2nd “day slot” is open, I think it is now attempting to write in my 3rd digit (the “2” of “2022”), but this breaks the day formats, since no month can have 32 days. When this happens, the system seems to stop processing the “bad date” and assigns all the valid values it did have, just as as above. As soon as it sees DAY = 32, it undoes the addition of the “2” to the day and sees the date as: _1\3_\____. Dates must have a year, so it auto-fills the year as 2021 a.k.a ThisYear(), so my output is: 1\3\2021 (since this year is 2021).

 

Again, all of these errors are avoided if you copy-paste 01/03/2022. Try both dates, should be easily reproduceable. I need a way to force those leading zero’s in Acumatica.

Userlevel 6
Badge +3

You can modify the date attribute on the field to format it with 0s 

[PXDBDate(DisplayMask = "MM/dd/yyyy")]

I don’t believe that will work for GIs though. As there is no format function for GIs your only option would be to manually dissect the date and then re-construct it as a string with the 0’s inserted as needed, its an ugly solution but should work. 

In the below snippet you would just replace SOOrder.OrderDate with whatever data field you are formatting. You will also want to set a caption for the column, the default generated one is not user friendly.

Concat(
IIf( Len( CStr( Month( [SOOrder.OrderDate] ) ) ) > 1, CStr( Month( [SOOrder.OrderDate] ) ), Concat('0', CStr( Month( [SOOrder.OrderDate] ) ))),
'/',
IIf( Len( CStr( Day( [SOOrder.OrderDate] ) ) ) > 1, CStr( Day( [SOOrder.OrderDate] ) ), Concat('0', CStr( Day( [SOOrder.OrderDate] ) ))),
'/',
CStr( Year( [SOOrder.OrderDate] ) ),
)

 

Userlevel 3
Badge

That’s a solid band-aid for this bullet hole @markusray17. Be a lot of using that code though in my GI’s. Hopefully they push a fix soon, GI’s are the best source for easily conveying discrete data sources and clunking them up with multiple date conversions will be nasty to say the least.

Userlevel 6
Badge +3

The other(also riskier) option would be to use javascript to modify the date as it is being copied(see example script below).

    <script>
function detectDate(text) {
var dateRegex = /^\d{1,2}\/\d{1,2}\/\d{4}$/;
return dateRegex.test(text);
}

function formatDate(date) {
var options = { year: "numeric", month: "2-digit", day: "2-digit" };

return date.toLocaleDateString(undefined, options);
}

document.addEventListener('copy', (event) => {
if (!event.target || !event.target.value) return;

var text = event.target.value;

if (!detectDate(text)) return;

var ms = Date.parse(text);

if (isNaN(ms)) return;

event.clipboardData.setData('text/plain', formatDate(new Date(ms)));

event.preventDefault();
});

</script>

You would need to add the script to the GenericInquiry aspx page and then add it to a customization project. To do that you would have to modify the files.list file in a local instance to allow you to add GenericInquiry/GenericInquiry.aspx as a custom file in a customization project. And then you can publish the customization on your live instance. 

This would apply to all GIs but the huge drawback is if you unpublish that customization generic inquiries will not work as it will remove that aspx page, it doesn’t fall back to the original file so its a bit risky. 

Userlevel 3
Badge

I like this, but we flex too much to ensure this won’t cause downstream havoc. Solid 2nd answer though for those4 who wish to be a bit more robust. I’m holding out for a fix from the Acumatica devs (barring a few key GI’s who need the first answer).

 

EDIT: The 2nd answer has another implication. Using answer #1, you will have converted a date to a string, therefore all the date filters in the GI are missing and you only get string filters. Answer #2 avoids this error. If you can’t/don’t want answer #2. Create a filter on the GI header using parameters over the dates, and then just ignore the fact the column itself is now a string.

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2022  Acumatica, Inc. All rights reserved