Re: Problems with how dates are stored in epoch format during form submissions

Victor_Herrero
Level 5

Problems with how dates are stored in epoch format during form submissions

We have been occasionally seeing some bizarre occurrences of people submitting our forms and their date of birth somehow getting confused, then rejected by Salesforce during sync. Here is an example: 

Form Submission original data, notice the value "-61990768800" which i take it is epoch format. 

Victor_Herrero_0-1614073833550.png

 

When the lead tried to sync, it failed with this error: "{FIELD_INTEGRITY_EXCEPTION: Date of Birth: invalid date: Thu Aug 06 00:00:00 GMT 5}"

Victor_Herrero_2-1614073970801.png

 

The only way I have found to fix this sync error is to delete the date (which displays correctly in Marketo's interface as 4th of August 2005, or at least this is a DoB that makes sense to our business) and use the date picker to put it in again. 

Victor_Herrero_3-1614074098000.png

 

While doing that, notice what the old stored value was: "0005-08-04"

Victor_Herrero_4-1614074148311.png

 

When I input the value again using the date picker, this is what it looks like: "2005-08-04"

Victor_Herrero_5-1614074251938.png

 

It seems like the year gets stored incorrectly, but somehow displays well on the interface. 

Funnily enough, that epoch value (if we are assuming correctly), translated to a date of birth that made no sense to us, since this is not our target audience: 

Victor_Herrero_6-1614074357672.png

 

We haven't been able to figure out how this fails and why. Any thoughts? 

 

 

 

 

8 REPLIES 8
SanfordWhiteman
Level 10 - Community Moderator

Re: Problems with how dates are stored in epoch format during form submissions

Need to the see the page that form is on. Epoch time is not normal; it should be an ISO 8601 string.
SanfordWhiteman
Level 10 - Community Moderator

Re: Problems with how dates are stored in epoch format during form submissions

@Victor_Herrero do you have a sample page for me to look at?

Victor_Herrero
Level 5

Re: Problems with how dates are stored in epoch format during form submissions

Thanks @SanfordWhiteman , 

 

Sure, this seems to be the url the person came through:

 

https://www.culturalcare.com.co/au-pair-best-agency

 

Let me know if the url parameters matter, they include utm fields and gclid, and I can add them. 

SanfordWhiteman
Level 10 - Community Moderator

Re: Problems with how dates are stored in epoch format during form submissions

Hmm. I inspected your DateofBirth validation code and while it isn't exactly the way I'd do it, it doesn't set any new values, so it wouldn't be responsible for changing the value — remember, it's always a String on the wire — from the date-like String ISO 8601 "yyyy-MM-dd" to an epoch-like String "-63998350".

Do you have a guaranteed way I can reproduce the problem?
Victor_Herrero
Level 5

Re: Problems with how dates are stored in epoch format during form submissions

Hi @SanfordWhiteman , 

Thanks for looking into it. I'm afraid I have been unable to reproduce this issue or find a common origin so far. 

What I can say for sure is that our "Fill out form" activity details always show the date of birth in epoch, even if stored as a date-like string afterwards. Is this not the case for everyone? 

I even made sure to check leads created from a Marketo landing page (since most of our forms are embedded). 

 

I can try to keep digging into it myself and once I see a pattern or a way to reproduce it, come back to this thread. I was hoping this was some common problem that people already had figured out, but maybe it's a new one. 

Victor_Herrero
Level 5

Re: Problems with how dates are stored in epoch format during form submissions

Also, I think we use some sort of library for the date picker on the date field... Could that be what converts the date into epoch? 

But I would imagine you would have noticed something like that. 

Victor_Herrero
Level 5

Re: Problems with how dates are stored in epoch format during form submissions

A fresh case came in throughout the weekend and before I made any changes to it I decided to check some things. 

This is the record page: 

Victor_Herrero_0-1615197676367.png

Date clearly says Jun 6 2002

 

Form submission activity says: 

Victor_Herrero_1-1615197839231.png

Salesforce says: 

Victor_Herrero_2-1615197901570.png

Postman if you ask Marketo says: 

Victor_Herrero_3-1615197991714.png

This last one I did not see coming :S

Through API I get null, the interface says one thing, Salesforce validation says another, activity submission another totally different... how is this possible? What can be trusted? I know Marketo has some sort of cache system and you can't trust 100% of what the interface shows or the results of a smart list, but API queries are also not to be trusted?  

 

I tried submitting a test lead from the same page, with same parameters (except GCLID ) and got a normal result. 

 

In the past we have had some weird DoB validation issues with a subset of specific iOS technology and date pickers / date format. 

I wonder if maybe this could also be related to the specific device. Is there a way to know the person's device? 

I seem to remember that the user agent string could not be trusted entirely: 


Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.150 Safari/537.36

SanfordWhiteman
Level 10 - Community Moderator

Re: Problems with how dates are stored in epoch format during form submissions


Through API I get null, the interface says one thing, Salesforce validation says another, activity submission another totally different... how is this possible? What can be trusted? I know Marketo has some sort of cache system and you can't trust 100% of what the interface shows or the results of a smart list, but API queries are also not to be trusted?  


I don't think the SL cache is coming into play at all here. And the API call is reading committed data from the database.

 

What happens if you deliberately pass that (negative) epoch time? Set the field as Text on the form instead of Date, so you don't get filtered through the datepicker & input validation.