6 Replies Latest reply on Jul 3, 2018 7:33 AM by Anton Lämmerhirt

    Skip numbers in serial number generator

    Anton Lämmerhirt

      We are using a serial number generator for all parts and assemblys. We are using the same numbers in our ERP system.

      Sometimes we have specific items in our ERP system, we do not draw in SolidWorks and do not create a part with it's number in the PDM. Now we want to skip these numbers in the serial number generator when we create new parts, to prevent multiple items with the same number.

      Is there some way of skipping / excluding specific numbers in a serial number generator?

        • Re: Skip numbers in serial number generator
          Michael Dekoning

          Not out of the box. You'd have to write a custom serial number generator using the PDM API.

          • Re: Skip numbers in serial number generator
            Martin Solem

            Exclude no, skipping yes. Depending on how often you need to do this, it can be a pain to maintain.

            You can manipulate the counter from Administration tool.

            Let's say this is your current counter:

            In ERP, the next 7 digits are used, but they are not SOLIDWORKS items.

            I can manually skip 7 numbers, hit OK and the next file i create will be a unique item.

            New file, data card:

            • Re: Skip numbers in serial number generator
              Ulf Stockburger

              Hi Anton

               

              Although I have the opinion that a PDM add-in is the best and securest solution, your problem can be solved by a simple macro. Check the CAD's FileSaveNotifiy event and execute all necessary steps when calling it. As soon as the event has been processed, the CAD takes control again and continues working as usual.

               

              The following examples assume that Solidworks CAD is used, that the ERP System is based on an SQL Server and that reading access to the ERP tables with the item numbers is available.

              1. React to saving documents with VBA

                  PreSave.zip: https://forum.solidworks.com/thread/21658#88655

                  Checks and further functions has to be implemented here.

               

              2. Execute SQL query with VBA

                  This function shows how a SQL query can be executed from VBA.

              Function GetLastErpNo() As Long
                  Dim sqlQuery As String
                  Dim adoCn As Object
                  Dim adoRs As Object
                  Set adoCn = CreateObject("ADODB.Connection")
                  Set adoRs = CreateObject("ADODB.Recordset")
                  adoCn.ConnectionString = "Provider=SQLOLEDB;User ID=<user name>;Password=<password of user name>;Data Source=<SQL Server name>;Initial Catalog=<ERP DB name>"
                  sqlQuery = "<your query to select the last ERP No>"
                  adoCn.Open
                  adoRs.Open sqlQuery, adoCn, 0, 1
                  GetLastErpNo = CLng(adoRs.Fields(0).Value)
                  adoRs.Close
                  adoCn.Close
              End Function
              

                  For security reasons, it should only be possible to retrieve data using a Stored Procedure in a production system.

               

              3. Adjust PDM serial number with VBA

                  This function synchronises the serial number in PDM with the last number in ERP.

              Dim vault As IEdmVault5
              Dim snNoValue As IEdmSerNoValue
              Dim snNbrs As IEdmSerNoGen7
              Dim newEpdmNo As Long
              Dim snNo As Long
              Function SetLastErpNo(newEpdmNo As Long) As Long
                  snNo = -1
                  Set vault = New EdmVault5
                  vault.LoginAuto "<your vault name>", 0
                  If Not vault.IsLoggedIn Then
                      Exit Function
                  End If
                  ‘ http://help.solidworks.com/2014/english/api/epdmapi/EPDM.Interop.epdm~EPDM.Interop.epdm.IEdmVault7~CreateUtility.html
                  Set snNbrs = vault.CreateUtility(EdmUtility.EdmUtil_SerNoGen)
                  Do
                      If (snNo < newEpdmNo) Then
                          Set snNoValue = snNbrs.AllocSerNoValue("<your serial number name>", 0, "", 0, 0, 0, 0)
                          snNo = snNoValue.Value
                          If snNo > newEpdmNo Then
                              snNoValue.Rollback
                              snNo = snNo - 1
                              Exit Do
                          End If
                      Else
                          Exit Do
                      End If
                  Loop
                  SetLastErpNo = snNo
              End Function 
              

                  The PDMWorks Enterprise xxxx Type Library has to be integrated.

               

              Basically it's only shown what is at least necessary to solve the problem. Most of the work, the error handling, is missing.

               

              BiI SAP-PDM

              • Re: Skip numbers in serial number generator
                Jason Capriotti

                Sounds like your ERP is the master system for generating the numbers. I would just let the users enter the number field in PDM manually to match ERP, or you have a custom PDM-in written that can assign the number from the ERP system for the users.