How to match with date fields?

[Mike-Cleaver-LB]

Hi team, I am trying to add DOB as a date field and use it for matching.
I have set up a custom DOB field on the user object, and the query references it (several times, the client wants a 3 of 5 match)
but the error is "Invalid bind expression type of String for column of type Date" It is passing the date yyyy-mm-dd into the query successfully by my understanding, but presumably it is in the wrong format? I don't see anyway to set the format on a date input

I know how to do it with some apex, but I don't have that option here. Any ideas would be welcome!

The log message is as follows

Form Values: {Birthdate=1987-12-10, Email=[mike.cleaver+dobtest@.....[trimmed].... password=HIDDEN}
There was a problem executing the specific query in the Custom Query property. Query used:

SELECT id FROM Account WHERE (firstname = :firstName AND lastname = :lastName AND personemail = :email) or...[trimmed]... or(firstname = :firstName AND lastname = :lastName and PersonBirthdate = :Birthdate) or ...[trimmed]... or (personemail = :email AND phone = :MobilePhone and PersonBirthdate = :Birthdate).

Error: Invalid bind expression type of String for column of type Date

[jlowe-SFDC]

Hi @Mike-Cleaver-LB - This should work fine. I'm using a date match for my client. How is the date field configured? It is a date on the user object and in the form?

My example so you can compare configuration:

Log record:
Searching for record with query:SELECT Id,AccountId FROM Case WHERE Crime_Ref_Number__c = :Crime_Ref_Number__c AND Contact_Date_of_Birth__c = :Contact_Date_of_Birth__c AND (ContactEmail = :Email OR Mobile_Phone_Unformatted__c = :Mobile_Phone_Unformatted__c) LIMIT 1.

Submitted Values: {Contact_Date_of_Birth__c=2001-01-01, Crime_Ref_Number__c=21240000219, Email=[email protected], FirstName=JACK, LastName=TEST, Mobile_Phone_Unformatted__c=07448411354, confirmPassword=HIDDEN, password=HIDDEN}

Form setup:

Field in SF:

Let me know and might be able to identify if there is an issue to fix.

[Mike-Cleaver-LB]

So the value I'm comparing to in the record is a date

it's called personBirtdate on the account object (we're using person accounts)

it's called birthdate on the user record

is how the metadata is setup for this field.

I might just temporarily to one check on date only and see if that behaves differently

[Mike-Cleaver-LB]

I corrected my metadata to be the user api name for birthdate and the error is similar

...
Username valid.
An error occurred whilst trying to update the User Object:Illegal assignment from String to Date
Form Inputs: {Birthdate__c=1987-12-10,

[jlowe-SFDC]

Hi @Mike-Cleaver-LB - I believe I've spotted the issue. I was able to reproduce it in my customer org based on what you provided above. I'll fix this in v1.9 which is due for release soon.

In the code, where it maps the form fields to the user, the component takes everything as a string and doesn't convert to the correct data type for the field.

Currently, where the component queries for an existing Person Account/Business Account or creates a new Person Account/Business Account the data type conversion works fine as long as the custom field doesn't exist on the user as well. As soon as it does, it goes bang!

There is a call to a function called "convertFormDataToCorrectDataType" which does the conversion, which is not called when checking existence for fields on the User object. So I believe the fix is as follows:

Is there a possibility you could push the code from the feature/passwordless branch in a sandbox and test out your query against the new version before I release it please? The fix seems to work on my end.

Alternatively, you can get at a beta version to install it here:https://login.salesforce.com/packaging/installPackage.apexp?p0=04tWU0000003g5x - however this does come with a caveat that once in beta, you will break the upgrade path until you manually uninstall and re-install a major release version again.

[Mike-Cleaver-LB]

I can do both, I will use the code version as that will let me have both installed. I should be able to get back to you today

[Mike-Cleaver-LB]

I had a look at the changes, it seems that you're using convertFormDataToCorrectDataType to parse the data, but it doesn't seem to handle strings? it should also be a 'switch on' rather than cascading if statements, it will only be one, no need to keep matching on the remaining ifs when you have parsed the value already?

Schema.DisplayType dataType = objectMetadata.get(key).getDescribe().getType();
becomes

string dataType = SiteUtilities.convertToString( objectMetadata.get(key).getDescribe().getType() );

switch on (datatype){
when 'DATE' {
}
when....{
}
when else {
// handle all different basic strings, like multiselect or whatever here
}

as I read it, basic strings won't get added to the list anymore?

[Mike-Cleaver-LB]

I tried creating a PR for this, I dunno if it worked, i got an error about the CLA which I signed already. Are you able to see it from your side? (PR 41)

[jlowe-SFDC]

Hi @Mike-Cleaver-LB - thanks for the PR. I can see it this side and that it failed, don't worry though. Think I can merge it anyway. Really appreciate your efforts and review. As a side note, in the convert function it still retained the string value in the list so I didn't need to specifically call it out in the logic but changing to a switch makes complete sense. I can see the other changes too. Can you drop me a line with your email so we can connect and discuss these before I release them as I'm getting some deployment issues to my org with these changes...

force-app/main/default/classes/SiteLoginController.cls Field must be an enum reference (58:54)
force-app/main/default/classes/SiteLoginController.cls Field must be an enum reference (61:54)
force-app/main/default/classes/SiteLoginController.cls null occurs as more than one when branch for this switch statement (57:21)

See here, as I don't think using constants is supported in a switch...
https://ideas.salesforce.com/s/idea/a0B8W00000GdWgHUAV/add-support-for-final-variables-in-switch-statement-when-clauses

[Mike-Cleaver-LB]

Oh damn, old habits sorry, I used to use Java before moving to SF a few years ago. Ok, that's disappointing, I will update my code and send it through.

And i see that you're using the passed in map to do the transforming. I didn't realise that, again from learned history I don't tend to manipulate the values I am going through. But you're right, we only need to modify the values we are actually changing. I can update my code to reflect that.

PR updated/sent and Linkdin request sent!

[jlowe-SFDC]

@Mike-Cleaver-LB - further review led to some tweaks based on your comments.

You make a good point about writing back to the same map with transformed values. It was a little confusing to understand so I've switched that to return a new map with the converted values called convertedInputs. I then loop over convertedInputs to add to the relevant object i.e. User, Person Account or Contact. There was a 2nd check to checkFieldExists() on the passed in formInputs to convertFormDataToCorrectDataType() function, and then it was checked again before mapping to the object against the same metadata so doing it this way removes the need for that second check as well.

I also removed your log line from the default, as we now do need to map the string value to the new map I didn't think we needed it. On a side note, couple of things to note:

• Because you were calling SiteUtilities.createLogEntry(), you would have ended up with multiple log records being created each time a self registration occurs. One for the actual registration, and then one each time there was a field that was a string that didn't need converting. To that end, the way this is implemented is that I build up SiteUtilities.messages with all the messages that I want to write, join them with new lines when all processing is done, and then call createLogEntry only once at either an exception, or at the final successful result with all the messages added to that point.
• In the log, you also tried to set the type to "Debug" which isn't a valid value in the picklist for the log object, so that would have failed. To make it easier, I've added 2 constants to the SiteUtilities called INFORMATION_LOG_TYPE and ERROR_LOG_TYPE which are the only 2 existing types at this stage. If you need more in the future, you'd have to add to the LogType picklist too.

Finally, when you reverted the enums change - there was further references that weren't reverted so I've converted those back to their original values too.

See: 613e5eb for the full changes I made. I'm going to release this later today or tomorrow once I've done some final testing.

[jlowe-SFDC]

Ok... this has been a lot more complex to change that first hoped. The change to convertFormDataToCorrectDataType to a new map, and the removal of the checkFieldExists() against each object caused lots of extra issues.

If there is no checkFieldExists() in convertFormDataToCorrectDataType() then this method fails with a null pointer exception because it tries to process the password field against the user metadata and doesn't find it. This field doesn't actually exist on a user. Its a hidden system field not returned in getDescribe() queries. So, I've put that check back in place.

With the non-existent fields now not in the new convertedInputs map, this causes issues further on as well. The fact that fields that come in from the form are possibly referenced in the Custom Query parameter causes the getObject() method to fail. The convertedInputs is first created to create an instance of a user in memory and I was trying to re-use it later in the code so I wasn't passing different references of fields submitted to different methods causing further confusion.

Therefore - I am reverting all changes in this area!

[jlowe-SFDC]

Fixed and released.

[Mike-Cleaver-LB]

Thanks! Sorry I was AWOL!