Example of importing XLS doc into two related objects?

I just came up with a second related scenario to this one.



Imagine you imported an XLS table of thousands of records, and then realize it would be best to extract some of the fields into a related object.



In this case, after creating 1500 Player objects, we realize it makes sense to pull out the team information into 150 related Team objects.



Two scenarios -- both start with denormalized data and both end results are normalized data in more than one object.

Hi Tim,



We don't have a ready example of this, but we should so I will work one up and get back to you when it is ready (this will be a good addition to our documentation as well). The short answer is yes it is possible to use a single CSV or XLS to import parent and child records and maintain a 1-N relationship between them. The key is first in the design of the CSV/XLS and next in the use of Triggers.



Regarding your second scenario, this can be done with an Object Script trigger that you would manually run on all the Player records. This trigger could use the Query API to check if a given Team has been created, if so attach it to the Player/if not create it then attach it. This is essentially how I planned to design the example, the only difference being this Object Script trigger will run whenever a Player record is created.



Thanks,

Matt

Hi Tim,



In this example we show how to import Players and the Teams they belong to (records of two different object types) from a single spreadsheet.



Step 1. Create your Application

Create an application containing both your parent and child objects. In this example we'll create a Player object with the Contact and Location attributes. We'll also create a Team object with just the Location attribute. Once created we'll rename the "Team" Record Name field to "Team Name". Next we add a one-to-many relationship between Team and Player (each Team can have many Players but a Player can belong to only one Team).



Step 2. Creating the Spreadsheet

Each row in the spreadsheet should represent a Player record. However, since we will also be importing Teams there should be columns for all Team fields as well as Player fields. In addition there needs to be a column for a unique Team identifier such as "Team Name", and every Player (i.e. every row) should have a Team Name value. For exmaple, column A: Team Nam

Thanks for the detailed reply, Matt!



Is "R55676092" the field name of the relationship?



For Scenario #2 (i.e. I already have data objects), after running the trigger to extract the Team Data into related Team objects, next step would be to delete the Team related fields from the Players?



Helps a lot, thanks again!

Tim

Hi Tim,



Yes "R55676092 is the integration name of the "Player" lookup field on the Team object. The "Team" lookup field on the Player object also has the same integration name (this is OK because these fields are on different objects).



Regarding deleting Team fields once the import is done, absolutely as long as you don't plan to do any further imports of Player/Team data in this manner.



Glad it helps,

Matt

Pavel pointed out that I did not wrap this trigger code in a check for rbv_api.isImport() so it will run anytime a Player record is created versus just when they are imported.



Since we only want Team records to be created when Players are imported (not when they are created through the UI, API, etc), we should only run this code if rbv_api.isImport() is true:

// Only create or attach Team if this trigger executes during an Import mode



if (rbv_api.isImport()) {



	// Get ID of Team this Player belongs to if it exists



	var team_name = "{!team_name}";



	var team_id = 0;



	team_id = rbv_api.selectValue("SELECT id FROM team3 WHERE name=?", team_name);



	



	// CASE 1: Team exists



	if (team_id) {



		// Attach Team to Player by updating Player record



		rbv_api.setFieldValue("player1", {!id}, "R55676092", team_id);



	}



	



	// CASE 2: Team does not exist yet



	else if (team_name && team_name.length>1) {



		// Create Team and attach Player to it



		var x = new Array();



		x["name"]="{!team_name}";