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.
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:
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.
Please let me know what other information you might need to help me with this.
thanks,
Mike
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:
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.
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
-
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
Please sign in to leave a comment.
Comments 1