Salesforce Reader not accepting certain data as string metadata

Support/help with CloverETL (4.9) and CloverDX (5.0 or newer) implementation problems

Michaelj
Posts: 6
Joined: Tue Feb 06, 2018 10:32 pm

Salesforce Reader not accepting certain data as string metadata

Postby Michaelj » Thu Jul 09, 2020 1:58 pm

I am normally able to pull data from our Salesforce Org without issue, but I ran into some trouble that I was hoping you could help with.

When i pull data from the OpportunityFieldHistory object (and other history objects as well), I get errors when pulling in the "OldValue" field and "NewValue" fields if the values are dates or certain numbers.

Component [SalesforceReader:SALESFORCE_READER] finished with status ERROR. (Out0: 0 recs)
Error when executing query: SELECT Id, CreatedById, CreatedDate, Field, IsDeleted, format(NewValue) NV, OldValue FROM OpportunityFieldHistory
String field "OldValue" can not be set to value Fri Dec 14 19:00:00 EST 2012 in field 7 ("OldValue"); value: 'Fri Dec 14 19:00:00 EST 2012'


For those who aren't aware, the history objects in Salesforce record all the changes made to the standard object records. The history object only has a few fields, ID (the Id of this history object record), OpportunityID (The ID of the opportunity record that was updated), Field (The field that was updated), OldValue (whatever was previously in this field), NewValue (Whatever is now in this field), CreatedbyId, CreatedDate.

The problem is that the data in the Old and New Value fields can be any type of data: String, Date, DateTime, Boolean, Decimal, etc...

When I set the OldVale and NewValue fields as string, it is able to pull in the data until it hits a record where the field that was updated was a date field, or certain number fields.

I kind of got around this but filtering out certain fields using the following query but that meant I was still missing a lot of data I wanted:

Code: Select all

SELECT Id, CreatedById, CreatedDate, Field, IsDeleted, NewValue, OldValue FROM OpportunityFieldHistory WHERE Field NOT IN ( 'CloseDate', 'EAS_PAID_DATE__c', 'Pay_Date__c','Status_Date__c','EnrollmentPeriodBegin__c', 'EnrollmentPeriodEnd__c', 'Latest_Renewal_Effective_Date__c' , 'Last_Paid_Invoice_Date__c','EffectiveDate__c','Date_entered__c','Gemini_Bonus_Evaluation_Date__c','Gemini_Bonus_Paid_Date__c','Upcoming_Renewal_Date__c','Referral_Paid_Date__c','Transfer_Date__c','Expected_Next_Orion_Referral_Pay_Date__c','Gemini_End_Date__c','Gemini_Effective_Date__c','Agent_Gemini_Approved_Date__c','Acct_Rcd_last_commish_date__c')


I tried changing the old and new value fields using Format() on the query that only pulled in those date fields and it allowed the query to run without erroring out like before, but now I didn't get any data for dt1 and dt2 no matter what I did to the metatdata type and lable. The rest of the fields did have data using the below query, it was just dt and dt2 that were null. I also tried using the OldValue, and NewValue lable but it didn't change anything.

Code: Select all

SELECT id, CreatedById, CreatedDate, Field , IsDeleted, FORMAT(NewValue) dt, FORMAT(OldValue) dt2
FROM OpportunityFieldHistory WHERE Field IN ( 'CloseDate', 'EAS_PAID_DATE__c', 'Pay_Date__c','Status_Date__c','EnrollmentPeriodBegin__c', 'EnrollmentPeriodEnd__c', 'Latest_Renewal_Effective_Date__c' , 'Last_Paid_Invoice_Date__c','EffectiveDate__c','Date_entered__c','Gemini_Bonus_Evaluation_Date__c','Gemini_Bonus_Paid_Date__c','Upcoming_Renewal_Date__c','Referral_Paid_Date__c','Transfer_Date__c','Expected_Next_Orion_Referral_Pay_Date__c','Gemini_End_Date__c','Gemini_Effective_Date__c','Agent_Gemini_Approved_Date__c','Acct_Rcd_last_commish_date__c')


Please let me know what other information you might need to help me with this.

thanks,
Mike

jandikovae
Posts: 64
Joined: Fri Nov 04, 2016 8:51 am

Re: Salesforce Reader not accepting certain data as string metadata

Postby jandikovae » Wed Jul 15, 2020 12:56 pm

Hi Mike,

This History Field function in Salesforce is quite special because the OldValue and NewValue fields are "anyType" field type. The anyType field type in Salesforce is dynamic and returns string, date, number, or boolean data depending on the kind of field involved.

The CloverDX doesn't have this dynamic metadata field therefore it interprets this field as a string and currently it works without issue only with string values.

As it appeared that the SalesforceReader is not converting other values in anyType correctly, we have logged a ticket to our development tracking system. You can review its progress on the following link:

https://bug.cloverdx.com/browse/CLO-19401

Thanks for understanding and please let us know if you have any follow-up question or comment. Eva
---
Eva Jandikova
CloverCARE Support
CloverDX

Visit us online at http://www.cloverdx.com


cron