Help with Excel Integration

[nborrerojr.]

I want to create a mapping configuration for the Excel integration that will give me all of the work items in a project. I want to be able to specify the project in the dialog in Excel.

How do I go about doing this? I can't figure out how to enable that Project field.

[MichaelChatel]

Hi Nolin,

It looks like this came up for you before, in a past inquiry here:

Project Selection in MS Excel Integration

As Matt mentioned, this is controlled through the mapping template, etc.

[nborrerojr.]

But how do I define it in the mapping template?

Below is what I have so far. Also, the server doesn't seem to be willing to use the updated file. It's still using an older version that I uploaded last night.

<?xml version="1.0" ?>
<mapping name="GA Work Items" template-version="2.1">
  <link-field external="Item_ID"
              data-type="integer"
              field-type="id" />
  
  <field external="Type"
         internal="Type"
         data-type="string"
         field-type="type"
         on-create-only="true">
      <default>ALM_Work Item</default>
  </field>
  
  <field external="Project"
     internal="Project"
     data-type="string">
    <default>/ST&Q/Sandbox/nolin</default>
  </field>
  
  <field external="State"
         internal="State"
         data-type="string">
    <default>Proposed</default>
  </field>
  
  <field external="Summary"
         internal="Summary"
         data-type="string">
   <default>Created in Excel</default>
  </field>
  
  <field external="Comments"
         internal="ALM_Additional Comments"
         data-type="string" />
  
  <field external="Assigned_User"
         internal="Assigned User"
         data-type="string" />
   
  <field external="Stakeholders"
         internal="ALM_Stakeholders"
         data-type="string" />
  
  <field external="Planned_Start"
         internal="ALM_Planned Start Date"
         data-type="string" />
   
  <field external="Planned_End"
         internal="ALM_Planned End Date"
         data-type="string" />
</mapping>

[MichaelChatel · Accepted solution]

Nolin,

Sorry, I was not clear on my last response, as I thought it was answered in your prior thread about this, from Matt.

That entry box is greyed out, because you're using a query, and the current design assumes you will specify in your query, which project(s) to query against.

The box does become active for data entry, if you are invoking this dialog through another means, where you aren't using a query, but creating a new list, for example.  (see my attached screenshot below)

[nborrerojr.]

Got it! So just to verify, the only way to specify a project when pulling a list of items is to save that project name in a query?

Also, I don't think it was updating the file before due to the & in the project name.

[nborrerojr.]

Another question...is there any way to specify the order that fields appear in teh Excel file? They do not spit out in the same order as they are defined in the config file.

[JoeBartlett]

Unfortunately not an automated way to define the order of the fields. However, you can do this manually by:

1) Use the Import Map option and select the template you want.

2) Go to the Developer tab in Excel and click the Source button.

3) Click and drag the fields from the XML Source pane onto your workbook in the order you like.

[nborrerojr.]

OK that works then. I'll just save an Excel file with my mappings stored and either make it a template or set it to read only so that I don't have to redo that layout.

Thanks for the help Joe and Michael! I think the Excel integration is finally demystified for me now. I have read chapter 12 in the Integration Guide; I just wasn't able to connect the dots on certain limitations before you guys clarified those things.

[nborrerojr.]

One last question....can you clarify how the on-create-only="true" attribute in the xml config files works?

[JoeBartlett]

Yes, as you might expect, it means that field is only recognized when a new item is created by using that template. If you update existing items those fields will be ignored.

[nborrerojr.]

Got it! Thanks again for the clarifications!

[nborrerojr.]

In case anyone else looks up this thread, I modified my Planned Start and End fields to the below values. I previously had them defined as strings which was incorrect. Also, I specified default values because if you use Excel to create new values, you cannot have blanks for the dates.

  <field external="Planned_Start"
        internal="ALM_Planned Start Date"
        data-type="date"
  field-type="date"
  external-date-format="yyyy-MM-dd"
  internal-date-format="MM/dd/yyyy" >
<default>01/01/2015</default> 
  </field>

  <field external="Planned_End"
        internal="ALM_Planned End Date"
        data-type="date"
  field-type="date"
  external-date-format="yyyy-MM-dd"
  internal-date-format="MM/dd/yyyy" >
<default>01/01/2015</default>
  </field>