none
Auto fill fields with BCS data RRS feed

  • Question

  • I have setup a sharepoint foundation 2010 site for testing a customized list.  I am using BCS and external content types to connect to SQL database.  This works well. 

    In my custom list, I have created an External lookup column (looking at the external list) for the customer's account number. In the external item picker, I have the account number, customer name, and customer's shipping address.

    Also in the list, I have a text column called 'Customer Name'.  Here is what I want to happen:  When a customer number is picked from the external lookup column, I want the Customer Name field to be automatically filled while still filling out the new item.  I know that I can add the column from the external list to the dispform.aspx and any list view, but I want the info to populate while the user is still creating the item.  Any way of doing this?
    Tuesday, November 16, 2010 4:12 PM

Answers

  • Sorry for the delay in reply.

    I think we are talking cross purposes, I wasn't suggesting getting the BCS Account ID.  I was suggesting using the itemID from the query string to then retrieve all the fields from the web service including the customer name and account ID from the External Data field.

    This would be represented by &ID= or ?ID= in the querystring.

    If I am getting the wrong end of the stick and you want to grab or prefill external data values on the form, take a look at the following blog post:

    Essentially there are three elements you need to be aware of.

    • TEXTAREA contains the data
    • DIV contains what is shown to the end user
    • A is the validation click fo rthe data entered.

    Although the example provided above shows how to prefill the data, you essentially have the object and you can read the External Data value from either the TEXTAREA or the DIV.

    Try alert(getBDCTagFromIdentifierAndTitle("TEXTAREA","External Item Picker", bdcItemNumber).value) where bdcItemNumber is the location of where the External Data picker is on the form starting from 0 (zero).

    I hope that helps.

    Kind Regards

    Giles


    Giles Hamson MCTS, MCITP SharePoint 2010 | Blog: http://ghamson.wordpress.com | Twitter: @ghamson
    Saturday, November 20, 2010 11:36 AM

All replies

  • I have found the best way to do this is with JavaScript.

    I have blogged about this fairly recently: http://ghamson.wordpress.com/2010/10/11/prefill-a-list-form-field-external-data/

    Please take a look and ask further questions if you need to.

    Kind Regards

    Giles


    Giles Hamson MCTS, MCITP SharePoint 2010 | Blog: http://ghamson.wordpress.com | Twitter: @ghamson
    • Proposed as answer by Giles Hamson Wednesday, November 17, 2010 8:11 AM
    • Unproposed as answer by Clayton Cobb Wednesday, November 17, 2010 8:21 AM
    Wednesday, November 17, 2010 8:11 AM
  • Hi Giles,

    thanks for the reply, but in my case I need fill in the field with a name 'Customer Name' by BCS data from the picker. If I understood right, your solution automatically prefill external data field (in my case 'Account Number').

    Any help is appreciated!

    thanks, Ivan

    Wednesday, November 17, 2010 3:18 PM
  • The type of apporach that Giles suggested is the right one (in my opinion): using jquery/javascript to add the additional fields to the page (that will show the additional info you want like Customer Name) and to attach to the event Account Number changed and when this event occurs update the additional info.

    PS: not sure you can access easily the External Data directly from the BCS from jQuery client object model. You may have to add an external list (has all the entities) and then look in this list from jQuery.

    PS2: Why don't you choose Customer Name for the display field for the external data column? Is the Account Number of any use to show as the display value?


    Florin DUCA Logica Business Consulting, France
    Wednesday, November 17, 2010 3:41 PM
  • So just to confirm, you would like to prefill the customer name into the pop up of the BCS picker - rather than the External Data field?

    If so, unfortnately modifying the picker pop up page directly (/_layouts/Picker.aspx) would be unsupported.  And looking at the DOM (HTML Output) of the page, there doesn't appear to be any standard master page attached to it so that you could attach javascript directly.

    The only alternative I could suggest would be under the configuration of the BCS External Content Type.

    In SharePoint Designer you can create a filter under the Read List operation for "Customer Name" and set the property "Use to create match list in external item picker" to be checked (ticked).

    This will all you to prefill the Customer Name into the External Data column as suggested in my first answer and it will resolve appropriately assuming only one result is returned back.

    If multiple results are returned it will underline the text in red and ask the user to clarify.

    Hopefully that helps.

    Kind Regards

    Giles


    Giles Hamson MCTS, MCITP SharePoint 2010 | Blog: http://ghamson.wordpress.com | Twitter: @ghamson
    Wednesday, November 17, 2010 3:50 PM
  • Thank you guys, but I am not sure that we understand one another. I will try to explain it by following example:

    In my custom list I have  two columns (fields):

    - Account Number (External Data column type)

    - Customer Name (Single line of text column type)

     

    In the external item picker I have following columns:

    - Account Number with a value "C0001"

    - Customer Name a value "Test Customer"

     

    So, If I select Account Number "C0001" from external item picker (windows is closed), I need fill in also column Customer Name with a value "Test Customer".

    I can see BCS picker data in the hiden field "ctl00_m_g_05733761_2c2f_4a73_9600_6f8b35ac2498_ctl00_ctl05_ctl01_ctl00_ctl00_ctl04_ctl00_b587cce4_ce52_4e2e_b937_8d83925c1a51_Picker_OriginalEntities", but I do not know how to take this data, especially Customer Name value.

    thanks,

    Ivan

    Wednesday, November 17, 2010 5:19 PM
  • Why not add a single external data column with additional display columns (Account number and Customer name).

    On the editform in the "standard" way you shoudl only see the Account number (if this is the display column you wish to show). My question was, why use this for the display and not the Customer name?

    If you wish to show the account number and the customer name. Use jQuery in the edit form. From jquery, add the html that displays the additional "display" columns you wish to add whenever the page loads or the user changes the external column value.

    Adding text fields does you no good, because it duplicates the information. Also, when you update the external item, the text field won't get updated.


    Florin DUCA Logica Business Consulting, France
    Wednesday, November 17, 2010 5:44 PM
  • I agree with this, what is the reason why you need the Customer Name in a seperate field?
    Giles Hamson MCTS, MCITP SharePoint 2010 | Blog: http://ghamson.wordpress.com | Twitter: @ghamson
    Wednesday, November 17, 2010 5:47 PM
  • Why not add a single external data column with additional display columns (Account number and Customer name).

    On the editform in the "standard" way you shoudl only see the Account number (if this is the display column you wish to show). My question was, why use this for the display and not the Customer name?

    If you wish to show the account number and the customer name. Use jQuery in the edit form. From jquery, add the html that displays the additional "display" columns you wish to add whenever the page loads or the user changes the external column value.

    Yes this can be solution. But problem is, that I do not know how can I do this :-( . Sorry, I am new in SP...

    I have checked "Account Name" in the "Add a column to show each of these additional fields:" settings, but I can see "Account Number: Customer Name" external column in the list view only.

    I need to display external "Account Name" column in the DispForm.aspx and EditForm.aspx. Can you please give some short instructions how can I do that?

    Thank you very much!

    Wednesday, November 17, 2010 6:30 PM
  • Ivan,

    Unfortunately there is no out of the box way to achieve that.  In the past I have used JQuery and accessed the Lists.asmx webservice (Function: GetListItems) to place anything that is required on to the page.

    Add a Content Editor Web Part to the DispForm.aspx and EditForm.aspx.  Get the ID of the item from the querystring (Parameter: ID) and pass it to the getlistitems web service by running a SOAP request.

    You will then need to place the output somewhere on the page.

    Here are some links to get you started:

    This should give you enough detail to retrieve the data, then you just need to write the output where you want it on the page.

    I hope that is helpful.

    Kind Regards

    Giles


    Giles Hamson MCTS, MCITP SharePoint 2010 | Blog: http://ghamson.wordpress.com | Twitter: @ghamson
    Wednesday, November 17, 2010 8:22 PM
  • Thanks Gilles,

    I understand your idea... use "JavaScript: Accessing the getlistitems web service" will be solution.

    But I have problem with Javascript. I added Content Editor Web part to the EditForm.aspx and following code (it is just for test get value of BDC field):

    <script type="text/javascript">
    
    _spBodyOnLoadFunctionNames.push(getField('textarea','External Item Picker'));
    
    function getField(fieldType,fieldTitle) {
      var docTags = document.getElementsByTagName(fieldType);
      for (var i=0; i < docTags.length; i++) {
        if (docTags[i].title == fieldTitle) {
          alert(docTags[i].value);
        }
      }
    }
    </script>

    But I can not see any message. Problem is that "getElementsByTagName" does not work. If I use

      var tags = document.getElementsByTagName('TEXTAREA');
      alert(tags.length);
    
    I can see "0". Any idea?

    Thursday, November 18, 2010 11:19 AM
  • Sorry for the delay in reply.

    I think we are talking cross purposes, I wasn't suggesting getting the BCS Account ID.  I was suggesting using the itemID from the query string to then retrieve all the fields from the web service including the customer name and account ID from the External Data field.

    This would be represented by &ID= or ?ID= in the querystring.

    If I am getting the wrong end of the stick and you want to grab or prefill external data values on the form, take a look at the following blog post:

    Essentially there are three elements you need to be aware of.

    • TEXTAREA contains the data
    • DIV contains what is shown to the end user
    • A is the validation click fo rthe data entered.

    Although the example provided above shows how to prefill the data, you essentially have the object and you can read the External Data value from either the TEXTAREA or the DIV.

    Try alert(getBDCTagFromIdentifierAndTitle("TEXTAREA","External Item Picker", bdcItemNumber).value) where bdcItemNumber is the location of where the External Data picker is on the form starting from 0 (zero).

    I hope that helps.

    Kind Regards

    Giles


    Giles Hamson MCTS, MCITP SharePoint 2010 | Blog: http://ghamson.wordpress.com | Twitter: @ghamson
    Saturday, November 20, 2010 11:36 AM
  • Hi Giles,

    thanks for all your suggestions. I solved my issue via SP workflow.

    regards, Ivan

    Friday, December 3, 2010 2:30 PM
  • naviX7 actually stole his original question from another site where it was posted by me.

    I have an external data source configured using BCS that connects to our MS Dynamics database.  I then have a custom list with an "External Lookup" column called "Customer Name."  We have several customers with the same name since they have multiple offices around the country.  Here is what I want to do:

    Employee goes to this custom list and clicks the button to create a new list item.  This takes them to newform.aspx (obviously).  They click the little button next to the 'Customer Name' and the external data item picker opens.  Within this picker, I have it configured to show the account number, cust name and address.  The employee selects the correct customer.  However, the external column only returns the value of one of the columns from the picker.  If someone selects from the picker "Company ABC123", only Company ABC123 shows up in the field.  That is as designed.  HOWEVER (this is the big part), I want it to show more!  I want it to return not only the Company Name or Account Number or Address, but return ALL of the fields from the picker.  I dont care if they are non-editable. It will be used by the employee to verify that they have selected the correct customer account.  In my opinion, it is stupid that this functionality isnt built in.  The values can in fact be returned to the allitems view once you finish filling out newform.aspx and click 'Save.'  I just want it to do it while still filling out the form.  Could a form action workflow or something be used to do this? 

    NaviX7, how did you solve it with a workflow..?  I'm familiar with designing workflows, but not form action workflows.

    Wednesday, December 15, 2010 10:01 PM