Where can I find organisation specific reference numbers in the table data?

Question

I'm working with Salesforce data for the first time. The organisation has had it implemented and the data integrated into it using Scribe. We had boatloads of problems getting the daily integration to work properly which it now does only because we ended up having to heavily restrict the period of data that is synced from the company's existing customer/order management system (A hateful system called Access Supply Chain that runs on SQL Server) to Salesforce each night through Scribe.

Because of all the problems we had and the need to restrict how far back the integration would go with data we believe that there will be records missing from Salesforce. To check this I was looking at the raw Salesforce data by using an ODBC connector on the server. I'm able to query the Salesforce tables directly over a linked server.

What I can't see in the Salesforce data are any of the reference numbers that came from the previous (and still active) customer/order management system. It's all Salesforce unique ID strings. Yet those reference numbers are visible on the Salesforce front end so surely they must be in the data somewhere? Can anyone tell me if it's possible to see all the data that sits behind an Opportunity record and an Invoice record from querying the tables directly?

Answer 1

You say you can see the reference numbers in the UI... therefore, I would assume that there are specific fields that are being used to store them.

First, you need to identify what type of record (Object) is actually being displayed when you are viewing the reference numbers. E.g. it could be an 'Account' - you'll see the name of the record at the top left corner of the page layout.

You'll then need to work out which field is storing the reference numbers. You'll see the field label displayed in the UI... but the backend field name (which you'll be seeing through your ODBC connection) will be different. Normally they are similar, but this is not guaranteed - as they can be customised.

For example - in one of my records below - you can see the Object name at the top left, and the field labels against each field.

You'll then need to go into Salesforce Setup and view the full list of fields for the object you are looking at. So, to find the name of the field called 'Contact period ends' in my screenshot above, I would go to Setup > Customize > Account (or whatever object you are looking at) > Fields

You'll then see a list of fields - separated into two seperate lists - Standard fields - available on all Salesforce accounts, and Custom fields visible on this account only. It's highly likely (though not guaranteed) that the field you want will be a custom field. Find the field label on the page, and in the next column you will see the actual field name which you can use in your ODBC query.

So, if I had a similar ODBC connection setup to my org, I could access the data in this field with a query like

SELECT Contract_Period_ends__c FROM Account WHERE <Criteria here...>