none
SP2010 / InfoPath 2010 how to... RRS feed

  • Question

  • I have a SP2010 list with InfoPath 2010 template and I just added a new field with default value.  I have almost 5000 items in the list so I'm hoping there's a way to easily populate the new field with the default value without opening and saving each item? 

    Any help is greatly appreciated.

    Azu

    • Moved by Marvin_Guo Friday, January 10, 2014 7:51 AM
    Thursday, January 9, 2014 11:40 PM

Answers

  • I just created a datasheet view and manually populated the new column as a work around it.  Its not really the answer I'm hoping but it still did the job, I guess. Thank you all.

    • Marked as answer by Tamras72 Friday, January 17, 2014 5:08 PM
    Friday, January 17, 2014 5:07 PM

All replies

  • Hello,

    it depends on your situation. If you add a new column to a list and that new column has a default value, the new colum will be added to all items in the list, but the default value will only be applied if the list item is edited and saved without changing the default value. 

    The question really is: do you need to evaluate that column for existing list items. If yes, then you can write your evaluation logic to assume a default value and compare it to the actual value, i.e. if the column value is blank then assume the default value. Then continue with your processing. Or if you want to filter a list based on that new column, create a filter that looks for  either a blank value or the default value in that column. 

    If you don't need to process list items based on that column, then there is no immediate call for action. The next time an existing item is edited and saved, the new column will be assigned the default value, unless it is immediately changed, of course. So, edit the InfoPath form to include the new control and save. 

    If you really need to update all existing items in the list with the default value in the new column, there are several ways:

    jQuery and SPServices

    Use jQuery and the SPServices library created by Marc Anderson. It is available for free on CodePlex and contains a command to update multiple list items: http://spservices.codeplex.com/wikipage?title=%24().SPServices.SPUpdateMultipleListItems

    Pros: Can be run from the client. All you need is adequate access to the site and list. Can be used entirely with the browser interface.

    Cons: Requires some understanding of JavaScript and/or jQuery, requires downloads of jQuery and SPServices libraries and some setup work.

    Powershell

    Use Powershell as outlined in this link: http://stackoverflow.com/questions/9946801/update-all-items-in-a-list-using-powershell -- 

    here is the Powershell code. Note: in this example the column "Number" gets assigned a random number between 0 and 100. If you want to change the column "Widget" to the value "Blue", then you need to change that line accordingly. 

    $web = Get-SPWeb http://YourServer/ListLocation
    $list = $web.Lists["TheList"]
    
    foreach ($item in $list.Items)
    {
      $item["Number"] = Get-Random -Min 0 -Max 100;
      $item.Update();
    }

    Pros: Fast execution, no big setup, no downloads required

    Cons: Requires box admin access to the actual SharePoint server, or a good connection to the folks that have that access (if you are a server administrator, you won't run some obscure bit of Powershell just because a site user wants you to. In real life, there'll probably be a test run in a test environment and some red tape to handle).

    I guess this answer contains much more than you'd have thought. 


    cheers, teylyn




    Friday, January 10, 2014 8:23 AM
  • Hi,

    According to your post, my understanding is that you wanted to populate the new field with the default value without opening and saving each item.

    I recommend to export the list to Excel, then update the column in the Excel, and then synchronize from Excel back into SharePoint.

    You can follow the steps as below:

    1. Download and install this Excel Add-in (Instructions on use located here)
    2. Export your list to Excel.
    3. Filter your views in Excel to what you need
    4. Make the Changes
    5. Synch from Excel back into SharePoint
    6. Refresh the list

    Then you will populate the new field with the default value in all items.

    Best Regards,

    Linda Li


    Linda Li
    TechNet Community Support

    Friday, January 10, 2014 9:06 AM
  • Linda, the tool you link to is specifically for SharePoint 2007. Can you confirm that this still works for SharePoint 2010, since that is the version the OP is using.

    cheers, teylyn

    Friday, January 10, 2014 9:33 AM
  • Hi,

    The Excel Add-in  can be used in SharePoint 2010.

    I have test it in my SharePoint 2010 environment, everything works well.

    Thank you.

    Linda Li


    Linda Li
    TechNet Community Support

    Friday, January 10, 2014 9:36 AM
  • I followed the instructions as listed here: http://msdn.microsoft.com/en-us/library/office/bb462636(v=office.11).aspx

    1. I installed the add-in. 
    2. I saved the work book in 97-2003 format.
    3. I tried to publish by clicking the [Publish and Allow Sync] button, entered the address and name of the list I'm trying to update...but it gave me a message that I must rename the list before publishing it to the server. So I skipped the publishing part as I didnt want to create a new list (or was I wrong here?)
    4. I tried to sych my table but I dont have the 'synchronize with SharePoint option' when I right click on the table 

    To update the SharePoint list

    1. In the table in the workbook, make the changes to the data.

    2. With the pointer in the table, right-click, point to Table, and click Synchronize with SharePoint.



      Figure 4. The Synchronize with SharePoint option

      Selecting the Synchronize with SharePoint option 

    I have Office 2010 if that makes any difference? I verified in the Excel Options --> Add Ins that Synchronizewssandexcel is isntalled.

    Friday, January 10, 2014 6:12 PM
  • Thanks Linda! That is good to know. 

    Is there any particular reason that Microsoft does not advertise this tool for SharePoint? There are many situations where people want to dynamically update a SharePoint list from an Excel spreadsheet. This tool seems to be the ticket, but there is not much documentation about it (or the fact that it works in 2010 still.) 

    Does that tool also work in SharePoint 2013? Does it work in 365 sites?

    Why is it not a core component of SharePoint 2007/2010? Why doe people have to download a tool to do a job that previously was part of the out of the box experience? 

    Lots of questions, I know. I don't expect you to answer these right here and now, but if you - as a Microsoft employee - have any information to help people with the SharePoint - Excel - SharePoint relationship, I'd be very happy to see some links.



    cheers, teylyn

    Saturday, January 11, 2014 2:01 AM
  • Hi,

    If we use the Excel add in, we need to create a new list when synchronize with SharePoint.

    It is also works for SharePoint 2013.

    I will update it if I have new discovery.

    Thank you.

    Linda Li


    Linda Li
    TechNet Community Support

    Friday, January 17, 2014 7:23 AM
  • Hi,

    If we use the Excel add in, we need to create a new list when synchronize with SharePoint.

    It is also works for SharePoint 2013.

    I will update it if I have new discovery.

    Thank you.

    Linda Li


    Linda Li
    TechNet Community Support


    Thank you Linda.  The add-in would have been perfect if it can sych with the existing list I need to update.  But still very useful. 
    Friday, January 17, 2014 5:04 PM
  • I just created a datasheet view and manually populated the new column as a work around it.  Its not really the answer I'm hoping but it still did the job, I guess. Thank you all.

    • Marked as answer by Tamras72 Friday, January 17, 2014 5:08 PM
    Friday, January 17, 2014 5:07 PM