function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
rfisher000rfisher000 

Feedback request on External ID

Currently we have a custom field on the Account object, called Client ID, that is has External ID checked in its definition.

During a resent upload, thru the API and via an UPSERT that refernces this field, one of the Accounts did and INSERT when it should have UPDATEd. (resulting in two records with the same value in Client ID)

 

1) Any idea what would cause this?

 

I've requested to my SF Admin to make that a "Unique" field as well, but he replied:

"The client ID field on the Account object is an “external ID” field and doesn’t allow duplicate values"

That field is a bit confusing because of the comments on the screen: 

 

Unique - Do not allow duplicate values
 

 

External ID - Set this field as the unique record identifier from an external system
 

 

2) Does the "Unique" checkbox do anything extra when a field is already and External ID. 

 

werewolfwerewolf

Are you sure the value you were inserting is really unique?  That is, it didn't have spaces or some sort of whitespace in the client ID field that made it look unique but actually wasn't?

 

I don't think the Unique checkbox does anything above and beyond External ID.

MarkSilberMarkSilber
You should also check to see if the radio button underneath is set to the first option, "Treat "ABC" and "abc" as duplicate values (case insensitive)", versus the second, "Treat "ABC" and "abc" as different values (case sensitive)".

If the external Id field is set to the first option, case-insensitive, then there must be a difference between the 2 record's external Ids as werewolf indicated as only 1 record can exist with the same external Id if the Unique option is also set.

Without the Unique option set, Salesforce will allow for more than 1 record with the same External Id, which is not recommended unless you are using the External Id field for easier sidebar searching and not upsert / update purposes.
rfisher000rfisher000

Yeah I've checked that a couple times.  Its "YZKd094T" in both records, with no leading or trailing spaces. (at least none that the edit field displays)

 

A user entered the account on 4/30/2009 2:54 PM, including this ID.

An API call performed and upsert on 6/24/2009 7:11 AM creating the second account.

rfisher000rfisher000

Let me see if I can convince the SF Admin to concede to setting that to Unique & case sensitive.

 

I'm still concerned that it did not find the original row on the UPSERT.

 

Any idea why it did not find and update it?

SuperfellSuperfell

You can have an externalId field that isn't unique, so if you want to be unique, you do need to set the unique option.

 

Does the user making the API call have access to the first account ? 

rfisher000rfisher000

The user being used has: Read/Create/Edit/Delete and View All /Modify All on the account object. (Along with all the other objects standard or custom)

 

Is there some other angle I need to look at this?  (like the owner)

MarkSilberMarkSilber
 If the user has Modify All Data permissions, then sharing rules won't apply and the user can see all Account records. Have you tried to manually create a new Account using the same Client ID field to see if you can reproduce the error?
rfisher000rfisher000

ALSO:

I've RE-requested to my SF Admin to make that a "Unique" field (with Case Sensitive option).

I mentioned the insight from these posting, so maybe he'll listen this time.

rfisher000rfisher000

Any suggestions on how to do that?

 

Can I do an upsert thru the Apex explorer, or is that only used for queries?

 

I'd hate to do a one-off app to test this.  A Q-n-D would be more dirty than quick, and I'm awfully nervous working with the production data.  Everything I have seen so far works everytime in the sandbox, not sure I could replicate there.

MarkSilberMarkSilber

I was wondering what happens when you try to manually create a new account with the same ID. 2nd choice would be to just try to use Data Loader with the 1 record. Do you have delete rights on the Account object?

 

I would also recommend that the Case Sensitive option not be enabled unless you really do have case sensitive IDs.

rfisher000rfisher000

Yeah, its a case sensitive ID.

 

I'm not so much worried about the create portion of the UPSERT as the select portion of the UPSERT.

If I give UPSERT an external ID field name and an ID, it should update (edit) the object and not insert (create) a new one.

MarkSilberMarkSilber
What I'm trying to validate is that the ID's are indeed the same and the index is setup properly. If you try to add a new record with exactly the same ID, you should receive a duplicate error message preventing the save. The next step would be to try an upsert.