3 Replies Latest reply on Aug 11, 2017 7:23 AM by Patrick.Cashman

    Get component Description from the modified component in Variant Manager using VBS

    ozaouiidrissi

      Hello Communitie,

       

      I need your help to provide fast script related to VM :

       

      i am developing a VBS script that generate a BOM in txt file for selected component in schematic, the problem in my script is when i want to get the modified component Description i switch to variant view, as you know this operation take time to be done (in my case 2s or more and lose lot off time), so their is any way to get this value without switching to the variant view, for example when i want to get NewPartNumber i use the function provided in the VM reference without switching to the Variant View, i need to do the same for component Description.

       

      Many thanks for your help,

      Otmane,

        • 1. Re: Get component Description from the modified component in Variant Manager using VBS
          Patrick.Cashman

          There is no Description property in the VMComponent object. If I'm not mistaken, I think it's a property (attribute) you (as in, your company, with however you have it set up to add properties to the components) add either in the PDB or from your component data source.

           

          If you want to get that, the fastest way would be to get it from your data source or the library directly, depending on how the property is added. If you don't know how to do that, we can go over it, but first let's make sure you're not on the wrong track to start with.

          • 2. Re: Get component Description from the modified component in Variant Manager using VBS
            ozaouiidrissi

            Hi Patrick,

             

            Yes no Description property in the VMcomponent ( i tried to find it in the VM reference file but nothing), what i mean by VMcomponent is the replaced component in the variant ( many time in my case VMcomponent is different from master component ), so if you expect that the fastest way to get description of the component would be to get it from data source or library directly please let me know how to do it with VBscript ?

             

            Many Thanks,

            • 3. Re: Get component Description from the modified component in Variant Manager using VBS
              Patrick.Cashman

              You are welcome

               

              To get any property from a part in the library, do the following;

               

              (I assume in this case that you use DxDatabook.  If your organization uses DMS, I can't help you with that, as I've never used DMS, but I suspect the overall approach is similar).

               

              - First, find out if the property is part of the PDB, or if it comes from your DxDatabook datasource.  How do you find this out?  Use the Library Manager to open your central library, find the PDB for the part, and see if the Description property is there. If it is, follow the example in this thread to extract the Description value from the PDB via a script.

              - If the property is not in the PDB, you'll have to execute a query on your DxDatabook database.  How to do that?  Well, it's a fairly complicated subject overall, but once you get past all the overhead of making the connection to the database, the actual query statement is not that difficult. You will need to do some searching on the web for examples of how to connect to your database with vbscript, but not to worry - this is a subject on which there is abundant information, examples and guidance from many sources.

               

              The basics of accessing a database using an external software are:

              - Find the right connection string for the database you are using.

              - Create a database connection object

              - Invoke the connection object using the connection string

              - Create an SQL query statement

              - Execute the statement using the database connection

              - The result of the query will be a dataset.  This is a temporary group of one or more datatables, depending on the nature of the query.

              - Get the datatable from the dataset

              - Get a datarow from the datatable

              - Cycle through the values in the datarow (they are indexed like an array) and get the value that corresponds to the appropriate column in the table

              - That's the answer.

               

              Here is some code that demonstrates how to do this with MS Access: (note this code is from vb.net; you'll need to make some changes if you use vbscript, but you should be able to get the idea with these examples)

              Public con_str As String

              Public working_db_path As String

              Public db_name as String

              Public conn As OleDbConnection

              Public da As OleDbDataAdapter

               

              'get a list of tables in the database

              Sub get_table_list()

                      Dim dts As DataTable

               

               

                      con_str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & working_db_path & ";"

                      conn = New OleDbConnection(con_str)

              Try

              table_list.Clear

              conn.Open()

              'Get table and view names

              dts = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, Nothing})

              Dim int As Integer

              For int = 0 To dts.Rows.Count - 1

              If dts.Rows(int)!TABLE_TYPE.ToString = "TABLE" Then

              If dts.Rows(int)!TABLE_NAME.ToString() = "ALL_PART_NUMBERS" Then GoTo next_table

              If dts.Rows(int)!TABLE_NAME.ToString() = "X_central_lib_parts_partitions" Then GoTo next_table

               

              'Add items to list box

              table_list.Add(dts.Rows(int)!TABLE_NAME.ToString())

              'debug.Print("Table name is: " & dts.Rows(int)!TABLE_NAME.ToString())

              End If

              next_table:

              Next

              Catch ex As Exception

              MessageBox.Show(ex.Message.ToString(), "Data Load Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

              End Try

              conn.Close()

              dts = Nothing

              End Sub

               

              'here's an example of how to read a property value from the database

              Sub get_smt_th_attribute()

              Dim n, i As Integer

              Dim tsb As New StringBuilder

              Dim dr as Data.DataRow

              'Dim ds As New Data.DataSet

              'Dim dt As New Data.DataTable

              dim smtth_col_index as Integer

              'Dim data_vals As New ArrayList

              dim vals as New ArrayList

              dim kvp as New KeyValuePair(Of string, string)

              'http://vb.net-informations.com/dataadapter/dataadapter-datagridview-oledb.htm

               

               

              conn = New OleDbConnection(con_str)

              conn.Open()

               

              'clear the part_mt

              'part_mt is a dictionary with part number and mount type

              part_mt.Clear

               

              'loop through each table in the database

              For n = 0 To table_list.Count - 1

               

              debug.Print("now reading table: " & table_list(n))

               

              'setup the sql statement

              sql = "select * from " & table_list(n) & " where Part_Number IS NOT NULL"

               

              'start the data adapter and load the table data

              da = New OleDbDataAdapter(sql, conn)

               

              Dim ds As New Data.DataSet

              Dim dt As New Data.DataTable

               

              da.Fill(ds)

              dt = ds.Tables(0)

               

              'get the index of the SMT_TH column

              smtth_col_index = 0

              For i = 0 To dt.Columns.Count -1

              If dt.Columns(i).ColumnName = "SMT_TH" Then

              smtth_col_index = i

              End If

              Next

               

              If smtth_col_index = 0 Then

              GoTo load_all_part_data_next_table

              End If

               

              'now cycle through each row in the table and get the SMT_TH value for each part

              For Each dr In dt.Rows

              If Not dr.ItemArray(0) = "" Then

              'debug.Print(dr.ItemArray(0) & vbtab & dr.ItemArray(smtth_col_index))

              If dr.ItemArray(smtth_col_index).ToString = "" Then

              msgbox("Part number " & dr.ItemArray(0) & " in the database does not have a SMT_TH value.  Please correct that then start over.")

              exit sub

              End If

               

               

              If Not part_mt.ContainsKey(dr.ItemArray(0)) Then

              part_mt.Add(dr.ItemArray(0), dr.ItemArray(smtth_col_index).ToString)

              End If

              End If

              Next

               

              ' da = Nothing

              dt = Nothing

              ds = Nothing

              ' dr = nothing

              load_all_part_data_next_table:

              Next

               

              conn.Close

              End Sub

               

              If you use a database different than MS Access, you will need to find the correct connection string for that database.  Do a google search for database connection string for <your database software tool> and you'll find it quickly.

               

              In your case, the SQL statement to find the record in the database with your alternate part number would look like this:

              (assume a part number value of 123456)

              sql = "select * from " & table_list(n) & " where Part_Number = 123456"

               

              The result would be a dataset with one datatable with one row in it.

               

              Keep in mind the returned dataset and included datatable are composed of only the data your query pulls from the database.  The datatable will have the same structure as the table from which the query results come, but it will only have the data your query asks for, not the whole table.  These objects are temporary, and you see that I dispose of them with the = Nothing statements at the end.

               

              This should get you going.  Best of luck getting it to work.