Help get this topic noticed by sharing it on Twitter, Facebook, or email.

Error: Value does not exist or does not match filter criteria

I am getting an error while running a query. I wanted to know if this error is from sales force or from dbamp. I turned on the debugging logs in sales force, but nothing gets created. So I was wondering if the DBAmp was somehow returning this error and it never reaches the saleforce side:

OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE" returned message "Error 1 : Value does not exist or does not match filter criteria.".
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE" could not INSERT INTO table "[SALESFORCE]...[Interaction__c]".
1 person has
this question
+1
Reply
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated kidding, amused, unsure, silly

  • This is the exact query.

    INSERT INTO [SALESFORCE]...[Interaction__c]
    ([Interaction_Status__c], [Lead_Only__c], [MLSAppId__c], [Term__c], [Recruitment_Interest__c], [Contact__c], [Opportunity__c], [Percent_Complete__c], [ASU_Affiliate_Number__c], [Program_Code__c], [Ethnicity__c], [Mailing_City__c], [Work_Phone__c], [Home_Phone__c], [SubStage__c], [Mailing_Postal_Code__c], [Admit_Type__c], [Application_Fee__c], [Conduct_I9__c], [Conduct_Explanation__c], [Evaluated_Transcripts__c], [File_Complete__c], [Final_Transcript__c], [Misconduct__c], [Official_Transcripts__c], [Permission_Form__c], [Personal_Statement__c], [Writing_Sample__c], [Citzenship_Status__c], [Race__c], [Mailing_Street__c], [Other_Address_Street__c], [Citizenship__c], [Mailing_Country__c], [Other_Address_Country__c], [Country_of_Origin__c], [Birthdate__c], [Future_LSAT__c], [Application_Submitted_Date__c], [Gender__c], [Cumulative_GPA__c], [Highest_LSAT_Score__c], [Opportunity_Type__c], [Preferred_Method_of_Contact__c], [Mailing_State__c], [Other_Address_State__c], [LORs__c], [Deceased__c], [Do_Not_Call__c], [Do_Not_Contact__c], [Email_Opt_Out__c], [Ferpa__c], [Financial_Aid_Applicant__c], [First_Law_Degree__c], [Highest_Degree__c], [Special_Interest__c], [Resume__c], [First_Name__c], [Last_Name__c], [LSAC_Account_Number__c], [Middle_Name__c], [Mobile_Phone__c], [Additional_Campaign__c], [Additional_Campaign_Key__c], [Additional_Campaign_Member_Status__c], [Additional_Campaign_Reference_ID__c], [Application_Number__c], [ASU_Law_Affiliate_match_complete__c], [ASU_Law_Affiliate_match_topic__c], [ASU_Law_Affiliate_method_of_contact__c], [ASU_Law_Affiliate_Notes__c], [ASU_Law_Affiliate_Received_no_response__c], [Audit_Reason__c], [Campaign__c], [Campaign_Key__c], [Campaign_Member_Status__c], [Campaign_Reference_ID__c], [Career__c], [Character_Fitness_Statement__c], [Competing_Law_School__c], [Constituent_ID__c], [Contact_Account_ID__c], [Contact_Description__c], [Contact_Title__c], [English_Proficiency__c], [Interaction_Upsert_Key__c], [Lead_Source__c], [Match_to_ASU_Law_Affiliate__c], [Opportunity_Close_Date__c], [Opportunity_Description__c], [Opportunity_Key__c], [Opportunity_Name__c], [Relationship_1_Email__c], [Relationship_1_First_name__c], [Relationship_1_Last_Name__c], [Relationship_1_Status__c], [Relationship_1_Type__c], [Relationship_2_Email__c], [Relationship_2_First_Name__c], [Relationship_2_Last_Name__c], [Relationship_2_Status__c], [Relationship_2_Type__c], [Report_Interaction__c], [RFI_IP_Address__c], [School_Code__c], [Sexual_Orientation__c], [Test_Score__c], [Test_Score_Key__c], [Test_Taken_Date__c], [Test_Type__c], [Other_Address_City__c], [Other_Address_Zip_Postal_Code__c], [Preferred_Name__c], [Salutation__c], [Email__c], [University_Email__c], [Work_Email__c], [Opportunity_Stage__c], [Interaction_Source__c], [Tribe__c], [Visa_Type_Code__c], [Application_Signed__c], [Emphasis__c], [Affiliated_Account__c], [Affiliation_Key__c], [Affiliation_Role__c], [Affiliation_Status__c], [Degree__c], [Degree_Code__c], [Degree_Conferred_Date__c], [Degree_Description__c], [Degree_Start_Date__c], [Degree_Type__c], [Major__c], [Primary_Affiliation__c], [Fast_App__c], [Lead_Rating__c])

    VALUES
    (
    'New', -- AS [Interaction_Status__c]
    'FALSE', -- AS [Lead_Only__c]
    '14270', -- AS [MLSAppId__c]
    'a0C29000001hFKzEAM', -- AS [Term__c]
    'a0J29000001CpwQEAS', -- AS [Recruitment_Interest__c]
    '0032900000PLwymAAD', -- AS [Contact__c]
    '00629000007U6FzAAK', -- AS [Opportunity__c]
    '38', -- AS [Percent_Complete__c]
    NULL, -- AS [ASU_Affiliate_Number__c]
    'Regular', -- AS [Program_Code__c]
    'Not Hispanic or Latino', -- AS [Ethnicity__c]
    'APO', -- AS [Mailing_City__c]
    NULL, -- AS [Work_Phone__c]
    '6233412671', -- AS [Home_Phone__c]
    'Incomplete', -- AS [SubStage__c]
    '9177', -- AS [Mailing_Postal_Code__c]
    'Undecided', -- AS [Admit_Type__c]
    'FALSE', -- AS [Application_Fee__c]
    'FALSE', -- AS [Conduct_I9__c]
    'FALSE', -- AS [Conduct_Explanation__c]
    'FALSE', -- AS [Evaluated_Transcripts__c]
    'FALSE', -- AS [File_Complete__c]
    'FALSE', -- AS [Final_Transcript__c]
    'FALSE', -- AS [Misconduct__c]
    'FALSE', -- AS [Official_Transcripts__c]
    'FALSE', -- AS [Permission_Form__c]
    'FALSE', -- AS [Personal_Statement__c]
    'FALSE', -- AS [Writing_Sample__c]
    'US Citizen', -- AS [Citzenship_Status__c]
    'White', -- AS [Race__c]
    'HHC, 2-4 GSAB', -- AS [Mailing_Street__c]
    '6832 Prussman Blvd', -- AS [Other_Address_Street__c]
    NULL, -- AS [Citizenship__c]
    NULL, -- AS [Mailing_Country__c]
    NULL, -- AS [Other_Address_Country__c]
    NULL, -- AS [Country_of_Origin__c]
    '10/11/1977', -- AS [Birthdate__c]
    NULL, -- AS [Future_LSAT__c]
    NULL, -- AS [Application_Submitted_Date__c]
    'Male', -- AS [Gender__c]
    NULL, -- AS [Cumulative_GPA__c]
    NULL, -- AS [Highest_LSAT_Score__c]
    'Undecided', -- AS [Opportunity_Type__c]
    NULL, -- AS [Preferred_Method_of_Contact__c]
    'Armed Forces Europe', -- AS [Mailing_State__c]
    'Colorado', -- AS [Other_Address_State__c]
    'FALSE', -- AS [LORs__c]
    'FALSE', -- AS [Deceased__c]
    'FALSE', -- AS [Do_Not_Call__c]
    'FALSE', -- AS [Do_Not_Contact__c]
    'FALSE', -- AS [Email_Opt_Out__c]
    'FALSE', -- AS [Ferpa__c]
    'FALSE', -- AS [Financial_Aid_Applicant__c]
    'FALSE', -- AS [First_Law_Degree__c]
    'FALSE', -- AS [Highest_Degree__c]
    'FALSE', -- AS [Special_Interest__c]
    'FALSE', -- AS [Resume__c]
    'Michael', -- AS [First_Name__c]
    'Volkert', -- AS [Last_Name__c]
    NULL, -- AS [LSAC_Account_Number__c]
    NULL, -- AS [Middle_Name__c]
    NULL, -- AS [Mobile_Phone__c]
    NULL, -- AS [Additional_Campaign__c]
    NULL, -- AS [Additional_Campaign_Key__c]
    NULL, -- AS [Additional_Campaign_Member_Status__c]
    NULL, -- AS [Additional_Campaign_Reference_ID__c]
    NULL, -- AS [Application_Number__c]
    NULL, -- AS [ASU_Law_Affiliate_match_complete__c]
    NULL, -- AS [ASU_Law_Affiliate_match_topic__c]
    NULL, -- AS [ASU_Law_Affiliate_method_of_contact__c]
    NULL, -- AS [ASU_Law_Affiliate_Notes__c]
    'FALSE', -- AS [ASU_Law_Affiliate_Received_no_response__c]
    NULL, -- AS [Audit_Reason__c]
    NULL, -- AS [Campaign__c]
    NULL, -- AS [Campaign_Key__c]
    NULL, -- AS [Campaign_Member_Status__c]
    NULL, -- AS [Campaign_Reference_ID__c]
    NULL, -- AS [Career__c]
    'FALSE', -- AS [Character_Fitness_Statement__c]
    NULL, -- AS [Competing_Law_School__c]
    NULL, -- AS [Constituent_ID__c]
    NULL, -- AS [Contact_Account_ID__c]
    NULL, -- AS [Contact_Description__c]
    NULL, -- AS [Contact_Title__c]
    'FALSE', -- AS [English_Proficiency__c]
    NULL, -- AS [Interaction_Upsert_Key__c]
    NULL, -- AS [Lead_Source__c]
    NULL, -- AS [Match_to_ASU_Law_Affiliate__c]
    NULL, -- AS [Opportunity_Close_Date__c]
    NULL, -- AS [Opportunity_Description__c]
    NULL, -- AS [Opportunity_Key__c]
    NULL, -- AS [Opportunity_Name__c]
    NULL, -- AS [Relationship_1_Email__c]
    NULL, -- AS [Relationship_1_First_name__c]
    NULL, -- AS [Relationship_1_Last_Name__c]
    NULL, -- AS [Relationship_1_Status__c]
    NULL, -- AS [Relationship_1_Type__c]
    NULL, -- AS [Relationship_2_Email__c]
    NULL, -- AS [Relationship_2_First_Name__c]
    NULL, -- AS [Relationship_2_Last_Name__c]
    NULL, -- AS [Relationship_2_Status__c]
    NULL, -- AS [Relationship_2_Type__c]
    NULL, -- AS [Report_Interaction__c]
    NULL, -- AS [RFI_IP_Address__c]
    NULL, -- AS [School_Code__c]
    NULL, -- AS [Sexual_Orientation__c]
    NULL, -- AS [Test_Score__c]
    NULL, -- AS [Test_Score_Key__c]
    NULL, -- AS [Test_Taken_Date__c]
    NULL, -- AS [Test_Type__c]
    'Fort Carson', -- AS [Other_Address_City__c]
    '80902', -- AS [Other_Address_Zip_Postal_Code__c]
    NULL, -- AS [Preferred_Name__c]
    'Mr.', -- AS [Salutation__c]
    'volkerts5@gmail.com', -- AS [Email__c]
    'michael.w.volkert.mil@mail.mil', -- AS [University_Email__c]
    'michael.w.volkert.mil@mail.mil', -- AS [Work_Email__c]
    'Applied', -- AS [Opportunity_Stage__c]
    'Student Information System', -- AS [Interaction_Source__c]
    NULL, -- AS [Tribe__c]
    NULL, -- AS [Visa_Type_Code__c]
    'FALSE', -- AS [Application_Signed__c]
    'Undecided', -- AS [Emphasis__c]
    NULL, -- AS [Affiliated_Account__c]
    NULL, -- AS [Affiliation_Key__c]
    NULL, -- AS [Affiliation_Role__c]
    NULL, -- AS [Affiliation_Status__c]
    NULL, -- AS [Degree__c]
    NULL, -- AS [Degree_Code__c]
    NULL, -- AS [Degree_Conferred_Date__c]
    NULL, -- AS [Degree_Description__c]
    NULL, -- AS [Degree_Start_Date__c]
    NULL, -- AS [Degree_Type__c]
    NULL, -- AS [Major__c]
    'FALSE', -- AS [Primary_Affiliation__c]
    'TRUE', -- AS [Fast_App__c]
    NULL -- AS [Lead_Rating__c]
    )
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated kidding, amused, unsure, silly

  • The most likely cause is Salesforce is returning that message to DBAmp because either a validation rule or trigger is unhappy with a value of one of the fields.

    You will need to check the validation rules and triggers on Salesforce to determine which is unhappy.

    Also, we highly recommend that you do not do inserts or updates directly using the linked server. We recommend you use the SF_TableLoader stored procedure to do so. For more information on using SF_TableLoader, take a look at chapter 5 of the DBAmp Doc: http://forceamp.com/hats/DBAmpDoc.pdf
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated kidding, amused, unsure, silly

  • I tried this SQL in salesforce directly and got a much more descriptive error pointing to the root cause. I was wondering if there is any option in DBAmp to report more descriptive error like sales force does, specially the name of the field with the problem. This is a custom field in saleforce. Could it be cutting for some reason? Here is the DBAmp and Salesforce return for the same query:

    DBAmp:
    "Error 1 : Value does not exist or does not match filter criteria."

    Saleforce:
    FIELD_FILTER_VALIDATION_EXCEPTION, Value does not exist or does not match filter criteria.: [Recruitment_Interest__c]
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated kidding, amused, unsure, silly

  • Try removing that field from the query and see if the error message goes away. What version of DBAmp are you currently running?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated kidding, amused, unsure, silly

  • I Salesforce log file the following is shown. As you can see the 1st line is similar to what DBAmp rerceves:

    4:13:29.1 (243633789)|VF_PAGE_MESSAGE|Value does not exist or does not match filter criteria.
    14:13:29.1 (243711483)|EXCEPTION_THROWN|[7]|System.DmlException: Insert failed. First exception on row 0; first error: FIELD_FILTER_VALIDATION_EXCEPTION, Value does not exist or does not match filter criteria.: [Recruitment_Interest__c]
    14:13:29.1 (244014409)|HEAP_ALLOCATE|[7]|Bytes:175
    14:13:29.1 (244146201)|FATAL_ERROR|System.DmlException: Insert failed. First exception on row 0; first error: FIELD_FILTER_VALIDATION_EXCEPTION, Value does not exist or does not match filter criteria.: [Recruitment_Interest__c]
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated kidding, amused, unsure, silly

  • What version of DBAmp are you running? Have you tried removing Recruitment_Interest__c from the query using the DBAmp linked server?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated kidding, amused, unsure, silly

  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated kidding, amused, unsure, silly

  • We recommend you use SF_TableLoader to inserts/updates instead of directly through the linked server. SF_TableLoader normally will give more detailed error messaging than directly through the linked server.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated kidding, amused, unsure, silly

  • Yes I was able to find the culprit field and remove it from the query. That fixes the problem. However, I was wondering why DBAmp is not reporting the name of the field that has the problem, even though Saleforce is reporting it.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated kidding, amused, unsure, silly

  • Sometimes we cannot get to every error thrown by the Salesforce server. SF_TableLoader may be able to get to it though.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated kidding, amused, unsure, silly

  • I was just trying table loader method, and ran into a problem. When inserting data into my local 'Interaction__c_Load' table it is saying that field "OwnerId" does not allow null. However SF_Generate did not create this field for me from the saleforce table. I created the lcoal table using SF_Generate method.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated kidding, amused, unsure, silly

  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated kidding, amused, unsure, silly