Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Matt FolgerMatt Folger 

(Linking external (ERP) Orders to Accounts) Best way to interlink these two objects?

We integrate some data from our ERP over to Salesforce for better visibilty and reporting of our customers.  I'm bringing over the order lines from the ERP database nightly using Jitterbit.  However I don't have a solid data association between the Account objects and the Orders yet.  We bring over the Account Number of the ERP system into the Orders object; and we have the ERP Account number 'stamped' on the Salesforce Accounts that we've done business with; but presently there isn't any hard linkage between these two objects.

I'm wondering what type of relationship would suffice here.  Looking through the various types (Lookup, Master-Detail, Hierarchical) none of them are a match for what I'm after.  I don't want this to have to be user driven; I'd rather it associated itself to the other record if there was a match.  In my previous experience you can't assoicate two records in Salesforce based on an external key.  All associations must be done with SFIDs for things... is that the case?  And if so, any recommendations on how to associate this data with the minimal amount of overhead?  
Best Answer chosen by Matt Folger
Christan G 4Christan G 4
Awesome! I am glad you were able to find a solution to your issue. If you need help with anything else, please feel free to reach out. 

All Answers

Christan G 4Christan G 4
Hey Matt, after reviewing your situation, I think this can be done via the use of Maps with Apex. Just to confirm my understanding, there is a field that captures both the ERP # on both the Orders object and Account object? If yes, is each ERP # unique?
Matt FolgerMatt Folger
The ERP software considers an Account a billing location and organizes plants and locations under that as "Ship-To" addresses.  Makes sense for a billing and invoicing and inventory managment software. 

Salesforce has an Account for each location in which this customer does business; and if we've processed an order with them then they also have a field that has an ERP account number on them.  There might be a dozen accounts in Salesforce with that same (ERP) account number just like there are a dozen 'Ship-To's in the ERP software. 

The Account Level Data of the ERP system is 'merged' onto the SF Account if it has a matching ERP Account ID.  There are specific fields that pertain to the ERP system data that are used for this merger. 

The Orders data is brought into SF using Jitterbit on a nightly basis and has the ERP Account numbers on them.  The Accounts have the same ERP Account ID number on them.  The ERP Account IDs are unique to the ERP system, but not the Salesforce system. 

And with this all explained I'm already seeing the problem.  If there are multiple Accounts in Salesforce that have the same ERP Account ID, then how to do we pick which one of these we should use?  We used to bring over the ERP Accounts as their own record type, and we can resort back to doing that if it'll solve this problem. 

Let's say that that wasn't an issue here.  We do have lingering "Master" Account Record Types that were part of the old batches that brought those into Salesforce.  If I was just working with those high-level accounts, what would I need to do in Apex or Maps, as you are suggesting? 
Christan G 4Christan G 4
Yes, I think it would be possible assuming that each of these master or high level accounts have their own unique ERP # or some other unique field value that is also present on the Order object. In order to be able to use Maps for this situation, there has to be at least one unique field value to associate both objects. This field can be a picklist, etc and doesn't have to be a look up field.

Are you trying to bring data from the Account object into the Order object? If not, I know you mentioned that you would like to associate them in some way. I guess I am just curious as to what type of association you are looking for? You can create a look up field on the Order object and have it automatically linked to the correct Account record upon creation. It wouldn't require user intervention. Would something like what I mention fulfill your requirement?
Christan G 4Christan G 4
After thinking about this more, I think we can possibly add other fields to filter on when creating this association. For example, lets say there are multiple accounts with the same ERP # but with different Account Names. If the name of this account is also present on the Order record and has the same ERP # then we can filter between these two fields possibly. If there is a match, the look up field on the Order object would automatically associate itself to that account and on that account record you can have a related list of all orders associate with it.
Matt FolgerMatt Folger
I like the idea you're hitting at with your second post.  If I bring over the Accounts in a slightly different way I can maintain the merging data into the Accounts from the ERP system and I can also bring them over as their own Accounts as well as "Master" Record Type Accounts. 

Would it be possible to do what you're suggesting using Apex or Maps if the Account ERP IDs were the same as others BUT they had a specific Record Type and that (ERP Account ID + Record Type) created a unique pair? 
Christan G 4Christan G 4
Yes, I think that is definitely possibly but I would like to test out the idea first in my dev org  before confirming. Below is an example scenario. When possible, please confirm that my understanding so far is correct regarding the Accounts.

Example:
Account Record Types: Regular and Master
  • Lets say that there are 5 Accounts that were made using the "Regular" record type and they all have the same ERP Account IDs being 5464.
  • A 6th Account was created with the "Master" record type and it is ERP Account ID is 5464. This is the only "Master" Account with this ERP Account ID.
  • An Order record is in process of being created which has the same ERP Account ID.
  • Output: When the Order record is saved to the database, you would for it to be automatically associated to the "Master" Account with the same ERP Account ID? The associated account would showcase in the Order Lookup field.

 
Matt FolgerMatt Folger
The 2nd and 3rd bullets that you post I'm not exactly following.  The uniqueness of the records could be boiled down to the level of "Master Account with an ERP Account ID" would be a unique pair in our instance.  Although I found a quicker "band-aid" solution to this by just bringing over the Order Details records (using Jitterbit) including the customer name (human readable like "XYZ Manufacturing" as opposed to "Account ID 34342").  Even though these records still won't be asociated with any specific SF Accounts, it will work for our purposes. 
Christan G 4Christan G 4
Awesome! I am glad you were able to find a solution to your issue. If you need help with anything else, please feel free to reach out. 
This was selected as the best answer
Matt FolgerMatt Folger
Thanks for being so helpful!
Christan G 4Christan G 4
Anytime!