Lookup Field with Picker V2.56 and Bulk importing of data

Dec 16, 2009 at 7:21 AM


First of all I'd like to thank you for creating this solution which made my life so much easier in building business forms without the need to use InfoPath.

Second, I'd like to find out how the data is stored in the column, as in what are the actual values that are stored in the database? I assume they are the IDs of the corresponding lookup values. Is that correct? If so, what format are they stored in? Is is stored ID1;ID2,ID3.... or ID1, ID2, ID3 ? I'm attempitng to use Bamboo Bulk List import utility ( http://store.bamboosolutions.com/onlinedoc/ContentFrame.aspx#AppName=HW18%20UC7.htm&ProductID=36 )to do bulk inserts of data but having limited success with Lookup fields that have multiple choice enabled.  I was able to import fields that had that option disabled by specifying the ID of the corresponding linked value in the lookup list.

Hope this makes sense.



Feb 19, 2010 at 3:29 PM

Bulk import of data into the lookup picker field is the same as regular lookup columns. The main difference comes down to the "Edit in Datasheet" view. What i've done in the past is to have a 2nd lookup column configured identically except configured as a Menu. Using PowerShell, I've configured the column to only be visible on the Datasheet view with a workflow or event handler to copy values into the Lookup Picker. Once initial load and massaging is done, I just delete the regular lookup column. Otherwise, setting the value isn't very different than using a regular lookup.

Mar 2, 2010 at 11:20 PM

You should be able to bulk import (cut and paste) in to the picker column in datasheet view if you follow the correct format (as you suggest).

Assuming you're picking on the Title column then the syntax would be ID1;#Title1;#ID2;#Title2... and so on.

So, for example, to paste in the 3rd value ("Blue") and the 6th value ("Green") in a list e.g you would put 3;#Blue;#6;#Green.  Note the leading hash is omitted.

Mar 3, 2010 at 12:39 AM

Correction : You can't of course paste into the Picker column - my fingers got ahead of me. Duh.

What I did was to create another column (plain text) and paste the formatted data into that and then copy across to the picker column using a simple workflow.