Community
Vault Customization
Share your knowledge, ask questions, and explore popular Vault API, Data Standard, and VBA topics related to programming, creating add-ins, or working with the Vault API.
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Query Vault Professional 2014 for part/assembly details from a spreadsheet

5 REPLIES 5
Reply
Message 1 of 6
AlexFielder
1033 Views, 5 Replies

Query Vault Professional 2014 for part/assembly details from a spreadsheet

Hi all, Say I have a spreadsheet containing drawing numbers for a bunch of legacy parts/assemblies that I have to create according to agreed naming conventions. i.e. a drawing number such as DR-###### becomes a part or assembly called DT-######.ipt (if it is an assembly: AS-######.iam). Is there an API method I can use that will be able to tell me whether the original drawing number exists in the vault (albeit with the new naming convention), where it is located and what lifecycle state it is currently in? This would ideally be something I could create using VBA - although I'm not adverse to developing it as a .NET addin for Excel (or even standalone?) I don't want to spend weeks developing something if someone has an example they would be willing to share. Perhaps I have overlooked a sample amongst the dev. tools?? Thanks, Alex.
5 REPLIES 5
Message 2 of 6
Redmond.D
in reply to: AlexFielder

Yes, you can run searches thorugh the API.  I don't think you will be able to run a search on exact patterns like DT-######.ipt.  However, you can run something like "name contains DT" and "name contains ipt" to filter down the list of files.  From there you can use code to verify the exact pattern.

 

I don't recommed VBA.  It's much easier to use VB.NET instead.  The Vault SDK has .NET libraries you can use and sample code.



Doug Redmond
Software Engineer
Autodesk, Inc.

Message 3 of 6
AlexFielder
in reply to: Redmond.D

Hi Doug, Thanks for confirming what I initially thought/subsequently found after posting! The API sample I plan to beg/borrow from is the Vault Browser sample included with the SDK- is that the one you were referring to? Is there also an SDK sample showing how to retrieve lifecycle state information or is that an exercise left for the end-user to figure out? Thanks, Alex.
Message 4 of 6
wayne.brill
in reply to: AlexFielder

Hi Alex,

 


The Vault Browser sample is a good example for working with files and folders. Previously I had updated the ItemEditor sample to get lifecycle state of Items. I attached that project. Here is the updates I added to the code. I believe the approach would be similar to get the lifecycle state of a file.

 

//wB added

 

string myPropString = myGetItemPropertyByPropertyNameOld(item, "State");

newRow[

"State Property"] = myPropString;

myPropString = myGetItemPropertyByPropertyNameOld(item,

"Provider");

newRow[

"Provider Property"] = myPropString;

 

//wB end added

 

//wB added

String myGetItemPropertyByPropertyNameOld(Item mySuppliedItem, string myPropertyName)

{

string returnString = string.Empty;

 

try

{

PropDef[] itemProps = m_connection.WebServiceManager.PropertyService.GetPropertyDefinitionsByEntityClassId("ITEM");

 

// use LINQ to query the array

IEnumerable<PropDef> selection =

 

from propDef2 initemProps

 

wherepropDef2.DispName == myPropertyName

 

selectpropDef2;

 

if(selection.Count() == 0)

 

thrownewException("Property not found");

 

PropDefitemNamePropDef = selection.First();

 

long[] itemIdArray = newlong[] { mySuppliedItem.Id };

 

PropInst[] propValues = m_connection.WebServiceManager.PropertyService.GetProperties(

 

"ITEM", itemIdArray, newlong[] { itemNamePropDef.Id });

 

// LINQ will sort for you IEnumerable<PropInst> sortedValues =

 

from value inpropValues

 

orderbyvalue.Val

 

selectvalue;

 

PropInst[] values1 = sortedValues.ToArray();

 

PropInstmyPropInst = values1[0];

 

if (myPropInst.Val != null)

{

returnString = myPropInst.Val.ToString();

}

}

catch (Exceptionex) { }

 

finally{ }

 

returnreturnString;

}

//wB end added

 

 

 

Thanks,

Wayne



Wayne Brill
Developer Technical Services
Autodesk Developer Network

Message 5 of 6
wayne.brill
in reply to: wayne.brill

Hi Alex,

 

The example in this DevBlog post could also be helpful (it is VB.NET)

http://adndevblog.typepad.com/manufacturing/2013/11/vault-2014-api-example-getting-property-values-o...

 

 

Thanks,

Wayne

 



Wayne Brill
Developer Technical Services
Autodesk Developer Network

Message 6 of 6
AlexFielder
in reply to: wayne.brill

Thanks for the reply/sample Wayne; much appreciated!

 

(Because I had nothing else to go on) I've taken the approach of borrowing the useful parts of the VaultBrowser SDK Sample so that I have an Excel AddIn which runs from a button in the 2010 Ribbonbar.

 

The code in the Ribbonbar button looks like this:

 

private void QueryVault_Click(object sender, RibbonControlEventArgs e)
        {
            if (Globals.ThisAddIn.Application.ActiveWorkbook.Name == "Project Tracker.xlsx")
            {
                Globals.ThisAddIn.InitializeSearchFromExcel();
            }
            else
            {
                MessageBox.Show("This application will only work in the Project Tracker.xlsx file!");
                return;
            }
            
        }

 Coupled with the AddIn code:

 

using Autodesk.Connectivity.WebServices;
using Autodesk.DataManagement.Client.Framework.Vault.Currency.Entities;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using VaultBrowserSample;
using Excel = Microsoft.Office.Interop.Excel;
using Framework = Autodesk.DataManagement.Client.Framework;
using Vault = Autodesk.DataManagement.Client.Framework.Vault;
using VDF = Autodesk.DataManagement.Client.Framework;

namespace QueryVault
{
    public partial class ThisAddIn
    {
        #region Member Variables

        private Vault.Currency.Connections.Connection m_conn = null;
        //private Vault.Forms.Models.BrowseVaultNavigationModel m_model = null;
        public bool NoMatch = false;
        private List<Framework.Forms.Controls.GridLayout> m_availableLayouts = new List<Framework.Forms.Controls.GridLayout>();
        //private List<ToolStripMenuItem> m_viewButtons = new List<ToolStripMenuItem>();

        //private Func<Vault.Currency.Entities.IEntity, bool> m_filterCanDisplayEntity;
        private PropDef[] defs = null;
        public ListBoxFileItem selectedfile;
        #endregion

        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
        }

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {
        }

        #region VSTO generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }
        
        #endregion

        public void InitializeSearchFromExcel()
        {
            m_conn = Vault.Forms.Library.Login(null);
            if (m_conn != null)
            try
            {
                Excel.Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook;
                Excel._Worksheet ws = wb.ActiveSheet;
                if (!ws.Name.Contains("MODELLING"))
                {
                    MessageBox.Show("Try switching to one the tabs labelled \"MODELLING\" and try again!");
                    return;
                }
                    
                Excel.Range range = ws.UsedRange;
                //Excel.Range range1 = range.Columns[1];
                //foreach (Excel.Range r in range1.Cells)
                //{
                //    string str = r.get_Value(Excel.XlRangeValueDataType.xlRangeValueDefault).ToString();
                //    //string str = (string)r.Value2();
                //    if (str != string.Empty)
                //        MessageBox.Show("Drawing number= " + str);
                //}
                for (int i = 2; i < range.Rows.Count; i++) //need to start at row 2 accounting for headers and such-like.
                {
                    Excel.Range r = range.Cells[i, 1];
                    string str = r.Value2 != null ? r.Value2.ToString() : "";
                    //string str = r.get_Value(Excel.XlRangeValueDataType.xlRangeValueDefault).ToString();
                    //debugging
                    if (str != string.Empty)
                    {    //MessageBox.Show("Drawing number= " + str);
                        Match match = Regex.Match(str, @"(.*\W)(\d{5,})(\S.*)|(.*\W)(\w\d{5})(\S.*)", RegexOptions.IgnoreCase);
                        if (match.Success)
                        {
                            //search for the drawing number in the vault
                            if (match.Groups[2].Captures.Count > 0)
                            {
                                Console.WriteLine("Drawing number matches the ###### or A##### pattern : " + match.Groups[2].ToString());
                                str = match.Groups[2].ToString();
                            }
                            else if (match.Groups[5].Captures.Count > 0)
                            {
                                Console.WriteLine("Drawing number matches the ###### or A##### pattern : " + match.Groups[5].ToString());
                                str = match.Groups[5].ToString();
                            }
                            
                            Excel.Range rVaultedFileName = range.Cells[i, 3]; //hidden in Excel!
                            Excel.Range rState = range.Cells[i, 4];
                            Excel.Range rRevision = range.Cells[i, 5];
                            Excel.Range rFileType = range.Cells[i, 6];
                            Excel.Range rVaulted = range.Cells[i, 7];
                            DoSearch( str, rVaultedFileName.Value2.ToString());
                            if (NoMatch != true && selectedfile != null)
                            {
                                if (selectedfile.File.EntityName.EndsWith(".iam"))
                                {
                                    if (selectedfile.File.EntityName.StartsWith("AS-"))
                                    {
                                        rFileType.Value2 = "Assembly";
                                    }
                                    else if (selectedfile.File.EntityName.StartsWith("DT-"))
                                    {
                                        rFileType.Value2 = "Detail Assembly";
                                    }
                                }
                                else if(selectedfile.File.EntityName.EndsWith(".ipt"))
                                {
                                    rFileType.Value2 = "Part";
                                }
                                //add/update some information about the file in the Excel spreadsheet.
                                //storing the filename that was selected means we don't need to prompt the user to choose again.
                                rVaultedFileName.Value2 = selectedfile.File.EntityName.ToString();
                                rState.Value2 = selectedfile.File.LifecycleInfo.StateName;
                                rRevision.Value2 = selectedfile.File.RevisionInfo.RevisionLabel;
                                #region Is Vaulted
                                //change the font to Wingdings
                                rVaulted.Font.Name = "Wingdings";
                                //set the value to the right character to present a tick.
                                rVaulted.Value2 = ((char)0xFC).ToString();
                                #endregion
                            }
                            else if(NoMatch == true) //need to mark the "VAULTED" Column with a Cross
                            {
                                #region Is NOT Vaulted
                                rState.Value2 = "NA";
                                rRevision.Value2 = "NA";
                                //change the font to Wingdings
                                rVaulted.Font.Name = "Wingdings";
                                //set the value to the right character to present a tick.
                                rVaulted.Value2 = ((char)0xFB).ToString();
                                #endregion
                                //reset the NoMatch bool
                                NoMatch = false;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("The error was: " + ex.Message + "\n" + ex.StackTrace);
                throw;
            }
            //we need to be sure to release all our connections when the app closes
            Vault.Library.ConnectionManager.CloseAllConnections();
        }
        private void DoSearch(string p, string VaultedFileName)
        {
            SrchCond searchCondition = new SrchCond();
            searchCondition.PropDefId = 9; //filename
            bool searchedAlready = false;
            if (VaultedFileName != string.Empty)
            {
                searchedAlready = true;
                searchCondition.PropTyp = PropertySearchType.SingleProperty;
                searchCondition.SrchOper = 3; //equals
                searchCondition.SrchTxt = p; //our filename
            }
            else
            {
                searchCondition.PropTyp = PropertySearchType.SingleProperty;
                searchCondition.SrchOper = 1; //contains
                searchCondition.SrchTxt = p; //our drawing number
            }

            RunSearch(searchCondition,searchedAlready);
        }

        private void RunSearch(SrchCond searchCondition, bool searchedAlready)
        {
            SrchCond[] conditions = new SrchCond[1];

            conditions[0] = searchCondition;

            string bookmark = string.Empty;
            SrchStatus status = null;

            //search for files
            List<File> fileList = new List<File>();

            while (status == null || fileList.Count < status.TotalHits)
            {
                File[] files = m_conn.WebServiceManager.DocumentService.FindFilesBySearchConditions(
                    conditions, null, null, true, true,
                    ref bookmark, out status);

                if (files != null)
                    fileList.AddRange(files);
            }
            if (fileList.Count > 0)
            {
                /*we have a match in Vault for our drawing number 
                 now we need to decide whether we completely automate the next step or allow the user to select the expected file.
                 */
                if (searchedAlready)
                {
                    foreach (File file in fileList)
                    {
                        ListBoxFileItem fileItem = new ListBoxFileItem(new VDF.Vault.Currency.Entities.FileIteration(m_conn, file));
                        Globals.ThisAddIn.selectedfile = fileItem;
                        //we should only be returning one file because we're searching for it directly!
                        return;
                    }
                }
                else
                {
                    //create a form object to display found items
                    string selectedfilename = string.Empty;
                    FileSelectionForm fileForm = new FileSelectionForm(m_conn);
                    //iterate through found files and display them in the search results list box
                    foreach (File file in fileList)
                    {
                        ListBoxFileItem fileItem = new ListBoxFileItem(new VDF.Vault.Currency.Entities.FileIteration(m_conn, file));
                        fileForm.m_searchResultsListBox.Items.Add(fileItem);
                    }
                    //update the items count label
                    fileForm.m_itemsCountLabel.Text = (fileList.Count > 0) ? fileList.Count + " Items" : "0 Items";
                    //display the form and wait for it to close using the ShowDialog() method.
                    fileForm.ShowDialog();
                }
                
            }
        }
        private void InitializePropertyDefs()
        {
            defs = m_conn.WebServiceManager.PropertyService.GetPropertyDefinitionsByEntityClassId(VDF.Vault.Currency.Entities.EntityClassIds.Files);
            if (defs != null && defs.Length > 0)
            {
                Array.Sort(defs, new PropertyDefinitionSorter());
            }
        }
    }
    #region "Search Condition Item Class"
    class SrchCondItem
    {
        public SrchCond SrchCond;
        public PropDef PropDef;

        public SrchCondItem(SrchCond srchCond, PropDef propDef)
        {
            this.SrchCond = srchCond;
            this.PropDef = propDef;
        }

        public override string ToString()
        {
            string conditionName = Condition.GetCondition(SrchCond.SrchOper).DisplayName;
            return String.Format("{0} {1} {2}", PropDef.DispName, conditionName, SrchCond.SrchTxt);
        }
    }
    #endregion
    #region PropertyDefinitionSorter Class
    /// <summary>
    /// Used for sorting collections of PropertyDefinition's.
    /// </summary>
    class PropertyDefinitionSorter : IComparer
    {
        /// <summary>
        /// Class (static) constructor that creates a static Comparer class instane used for sorting PropertyDefinition's.
        /// </summary>
        static PropertyDefinitionSorter()
        {

            m_comparer = new Comparer(Application.CurrentCulture);

        }

        private static Comparer m_comparer;

        public int Compare(object x, object y)
        {
            PropDef propDefX = x as PropDef;
            PropDef propDefY = y as PropDef;

            lock (m_comparer)
            {

                return m_comparer.Compare(propDefX.DispName, propDefY.DispName);

            }

        }

    }
    #endregion
    #region "ListBoxFileItem"
    /// <summary>
    /// A list box item which contains a File object
    /// </summary>
    public class ListBoxFileItem
    {
        private FileIteration file;
        public FileIteration File
        {
            get { return file; }
        }

        public ListBoxFileItem(FileIteration f)
        {
            file = f;

        }

        /// <summary>
        /// Determines the text displayed in the ListBox
        /// </summary>
        public override string ToString()
        {
            return this.file.EntityName;
        }
    }
    #endregion

}

 And finally the form that gets called when we need to search (if we haven't already):

 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using VDF = Autodesk.DataManagement.Client.Framework;

namespace QueryVault
{
    public partial class FileSelectionForm : Form
    {
        private VDF.Vault.Currency.Connections.Connection m_connection;
        public string selectedfilename;
        public FileSelectionForm(VDF.Vault.Currency.Connections.Connection connection)
        {
            InitializeComponent();

            m_connection = connection;
        }
        /// <summary>
        /// Takes the selected file in the list and passes the relevant information about it back to our Excel document.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            ListBoxFileItem selectedfileitem = (ListBoxFileItem)m_searchResultsListBox.SelectedItem;
            if (selectedfileitem !=null)
            {
                Globals.ThisAddIn.selectedfile = selectedfileitem;
                this.Close();
            }
            else
            {
                MessageBox.Show("You need to select a file!");
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            Globals.ThisAddIn.NoMatch = true;
            this.Close();
        }
        private void m_openFileToolStripMenuItem2_Click(object sender, EventArgs e)
        {
            OpenFile();
        }
        private void OpenFile()
        {
            if (m_searchResultsListBox.SelectedItem != null)
            {
                ListBoxFileItem fileItem = (ListBoxFileItem)m_searchResultsListBox.SelectedItem;
                OpenFileCommand.Execute(fileItem.File, m_connection);
            }
        }

        private void m_searchResultsListBox_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            OpenFile();
        }
    }
}

I also copied across the "Condition, OpenFileCommand" Classes from the VaultBrowser sample but I don't need to post them here since they're available in the SDK.

 

Overall I think the above is working pretty well, but (and I was laid awake thinking about this last night) I've realised that there's a whole other set of information I can potentially pull out of the Vault that will help us to understand the effort required to finish a project of this kind.

 

Things like:

 

  • (Probably using iLogic) a mapped-from-vault count of features in parts/assemblies/detail assemblies to highlight the effort contained within each.
  • File Path which would show unique occurrences which have been reused between assemblies. (Currently we can do this using States since the once project which is complete has a state all to itself.
  • Parent Of/Child Of relationships (Uses/Where Used) - to compliment the File Path information above (and potentially help with new/updated drawings)

Outside of the Vault (and probably the scope of this thread) I plan to implement the following (based on an article I read yesterday regarding Agile Projects):

 

  • Assign a score to each survey drawing (and therefore the subsequent assembly/detail assembly created from it) based on the total occurrences of components that make up each item list. i.e. (and unless I've misunderstood the article above) one Sub-Assembly might have a score of 10 based on the number of components in its item list, but a high number of occurrences of the same type of bolt in the parent assembly might also get a score of 10 because the effort required to place them all is the same as creating the sub-assembly.
  • Assign a score to each part drawing provided that is initially nothing more than a count - until such times as we know the effort applied to each from the Vault-mapped "Feature-effort" information above.

The accumulative score from these metrics would initially give us an idea of what effort is required for the baseline dataset provided by the survey team - then as the project progresses we would begin to see a pattern emerge which would show us whether the figure we had quoted was somewhere near accurate and enable to adjust the effort applied to each project.

 

TL;DR: The VaultBrowser source did what was needed (with a few tweaks!).

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

Autodesk Design & Make Report