1 Reply Latest reply on Sep 21, 2017 11:36 AM by Steven Dod

    SQL help to get Filename and Description from EPDM

    Guy Edkins

      Hi,

       

      I have a standalone in-house app I am creating that needs to query EPDM-SQL.  I need to hand in an EPDM users name and have it hand back to me all ECO files (we prefix the files name with ECO) and the description that goes along with each file. I know this data comes from the Documents and VariableValue tables. I have used this call  with some success - it returns all users with ECO's and the description. I am having a hell of a time handing in a users name and getting back just ECO's and the descriptions. Any one have some ideas?

       

      SELECT       max(Case when VV1.VariableName = N'Author' then VariableValue.ValueText END) as Author,

                          max(Case when VV1.VariableName = N'Description' then VariableValue.ValueText END) AS Description

                               FROM Documents INNER JOIN

                               VariableValue ON Documents.DocumentID = VariableValue.DocumentID INNER JOIN

                               Variable AS VV1 ON VariableValue.VariableID = VV1.VariableID

                                  WHERE (Documents.Filename like N'ECO-%') AND (Documents.Deleted = 0) and (VV1.VariableName in (N'Author', N'Description')

                                    and (VariableValue.ValueText <> '-'))

                                  Group BY Documents.Filename

                                  ORDER BY Author asc

        • Re: SQL help to get Filename and Description from EPDM
          Steven Dod

          edit

           

          Sorry, was not thinking SQL but VB.  Maybe it will inspire you or someone that can answer.

           

           

          I wrote a small script that would read the user name from a data card value then send them a message (i.e. a new EC was assigned to you type thing).  Below is the code I used and you might be able to adapt it to your needs.  It shows how to get the user name etc.

           

          get the name from the data card (or your field)

                  'Get the engineer assigned from the data card, returns Fullname

                  Dim myFile As IEdmFile5 = vault.GetObject(EdmObjectType.EdmObject_File, fileId)

                  Dim vars As IEdmEnumeratorVariable5 = myFile.GetEnumeratorVariable

                  vars.GetVar(varName, "", engineer)

           

          sub to send the message

             Private Sub SendMessage(ByVal vault As EdmVault5, ByVal msgBody As String, ByVal engUser As String)

                  'Declare the IEDMUser required variables
                  Dim userMgr As IEdmUserMgr5 = vault     'Set the user manager to the vault passed from OnCmd
                  Dim user As IEdmUser6                   'IEDMUser6 is required to access the FullName value

                  'Declare and set the Group variables to iterate through the Engineering Group to find the assigned engineer
                  Dim group As IEdmUserGroup5
                  group = userMgr.GetUserGroup("Engineering")
                  Dim pos As IEdmPos5
                  pos = group.GetFirstUserPosition

                  'Check each user in the Engineering group to find the FullName assigned to the data card variable
                  While Not pos.IsNull
                      user = group.GetNextUser(pos)
                      If user.FullName = engUser Then
                          user.SendMsg("An new Engineering Change has been assigned to you.", msgBody)
                          Exit While
                      End If
                  End While

              End Sub

           

           

          Hope it helps.

          Steve