Showing results for 
Search instead for 
Do you mean 

Modifying the container field at the back end

SOLVED
Go to Solution
Trusted Contributor

Modifying the container field at the back end

Hi, I'm hoping someone might be able to give us a starting point to look at an issue I am currently faced with.

 

Due to a modification of some internal processes I find myself in a position of needed to look up 90,000 files and modify their contents by moving them to a different container.

 

As you can imagine this is a rather laborious job if we have to conduct it manually so I'm hoping it might be possible to do it from the back-end.

 

I have a spreadsheet that has two columns - old file number and new file number - and would like to be able to have one of our IT bods write a script (or whatever) that looks up the file listed in column A, reviews the contents (if any) and moves those contents to the file listed in column B.

 

Can anybody advise if what I would like to achieve is possible and if possible, some hints on how to achieve it ie. what tables should we be looking in?

 

Thanks

Michael

 

4 REPLIES
Trusted Contributor

Re: Modifying the container field at the back end

Considering how many modifications are made to the database when you move a record to a different container:

 

GetRECORD @URI=29

SELECT COUNT(*)  FROM TSRECORD WITH (NOLOCK)   WHERE ( rcContainerUri = 1 AND  uri <> 29)

GetRECLOC @URI=124

SELECT *  FROM TSLABEL WITH (NOLOCK)   WHERE (uri=2) ORDER BY  uri  ASC

SELECT  TOP 1 *  FROM TSLABELITE WITH (NOLOCK)   WHERE ( lbiLabelUri = 2 AND  lbiItemType = 6 AND  lbiItemUri = 5)

SELECT  TOP 1 *  FROM TSLABELITE WITH (NOLOCK)   WHERE ( lbiLabelUri = 2 AND  lbiItemType = 6) ORDER BY  uri DESC

Transaction Begin

UPDATE TSRECORD SET rcContainerUri = 5,lastActionDateTime = '201109061350191',sysLastUpdated = '201109061350191' WHERE uri = 29

UPDATE TSRECORD SET isParent = 'T',lastActionDateTime = '201109061350191',sysLastUpdated = '201109061350191' WHERE ( uri = 1)

UPDATE TSRECORD SET isParent = 'T',lastActionDateTime = '201109061350191',sysLastUpdated = '201109061350191' WHERE ( uri = 5)

UPDATE TSRECLOC SET rlDescription = '11/3' WHERE uri = 124

DELETE FROM TSLABELITE WHERE (  uri = 15 )

INSERT INTO TSLABELITE (uri,lbiLabelUri,lbiItemType,lbiItemUri) VALUES (69,2,6,5)

Transaction End

GetRECORD @URI=5

GetRECELEC @URI=5

ConstructManyRecords: 5

SELECT *  FROM TSSPLISTIT WITH (NOLOCK)   WHERE (spItemUri=5) ORDER BY  spItemUri  ASC

SELECT *  FROM TSRECTERM WITH (NOLOCK)   WHERE (rtmRecordUri=5) ORDER BY  rtmRecordUri  ASC

SELECT *  FROM TSRECJURIS WITH (NOLOCK)   WHERE (rjRecordUri=5) ORDER BY  rjRecordUri  ASC

AddManyNotes (26): 5

AddManyAccessControls (6) (6): 5

AddManyExtraValues (6): 5

ConstructManyRecords: 1

SELECT *  FROM TSSPLISTIT WITH (NOLOCK)   WHERE (spItemUri=1) ORDER BY  spItemUri  ASC

SELECT *  FROM TSRECTERM WITH (NOLOCK)   WHERE (rtmRecordUri=1) ORDER BY  rtmRecordUri  ASC

SELECT *  FROM TSRECJURIS WITH (NOLOCK)   WHERE (rjRecordUri=1) ORDER BY  rjRecordUri  ASC

AddManyNotes (26): 1

AddManyAccessControls (6) (6): 1

AddManyExtraValues (6): 1

SELECT  uri,regDateTime,fullRecordId  FROM TSRECORD WITH (NOLOCK)   WHERE (URI IN (SELECT  A0.uri  FROM  TSRECORD A0 WITH (NOLOCK)  WHERE ( rcContainerUri = 1 AND  A0.uri > 0))) ORDER BY  regDateTime DESC  , fullRecordId DESC

SELECT  uri,regDateTime,fullRecordId  FROM TSRECORD WITH (NOLOCK)   WHERE (URI IN (SELECT  A0.uri  FROM  TSRECORD A0 WITH (NOLOCK)  WHERE ( rcContainerUri = 1 AND  A0.uri > 0))) ORDER BY  regDateTime DESC  , fullRecordId DESC

 

I suggest you not try this outside of the client.

 

What you can do is, all records whose new container will be the same copy-n-paste those record numbers from the spreadsheet to the TRIM client "Records Work Tray". That will query TRIM for those records then right-click > tag all and move the records. That may help a bit.

_________________________________________
HP Application Information Optimizer (AIO) Support
Honored Contributor

Re: Modifying the container field at the back end

Terry, the dataset your used to capture this SQL trace didn't include configuration for audit events.

In a production situation people will probably want to have a watertight auditlog trace back the change of container.

In that case many additional INSERTS are performed into the database and it'll be tricky to replicate that behaviour by a DBA.

 

 

Michael, if you want your container modifications from your Excel sheet to be replicated to TRIM, you can get someone to make a VBA macro using the TRIM SDK to loop through your spreadsheet and update TRIM.

 

We don't use TRIMPort anymore (in favour of our own tool), but maybe TRIMPort can update the container as well, but be sure to check if other fields don't get updated as well.

 

 


(Any opinions expressed in this forum are my own personal opinion and should not be construed as an official statement by DXC Technology.)

Analytics & Data Management
Application & Business Services
DXC Technology
Honored Contributor

Re: Modifying the container field at the back end

[ Edited ]

Strider,

 

Just have your IT guys add a visual basic module into the excel workbook.  Then use the TRIM SDK to do this in bulk.  I've pasted some code below that does what you're asking.  Have them use that as a starting point.  Make sure you do these tasks in a development environment first!

 

 

 

Sub updateContainers()
    Dim tDatabase As New TRIMSDK.Database
    tDatabase.Connect
   
    Dim lRow As Long
    lRow = 1
   
    Dim sColumnA_ContainerNumber As String
    Dim sColumnB_ContainerNumber As String
    sColumnA_ContainerNumber = Sheet1.Cells(lRow, "a")
    sColumnB_ContainerNumber = Sheet1.Cells(lRow, "b")
   
    Do While (sColumnA_ContainerNumber <> "")
        Dim tContainerA As TRIMSDK.Record
        Dim tContainerB As TRIMSDK.Record
        Set tContainerA = tDatabase.GetRecord(sColumnA_ContainerNumber)
        Set tContainerB = tDatabase.GetRecord(sColumnB_ContainerNumber)
       
        If (Not tContainerA Is Nothing And Not tContainerB Is Nothing) Then
            Dim tContainerA_ContentSearch As TRIMSDK.RecordSearch
            Set tContainerA_ContentSearch = tDatabase.NewRecordSearch
            tContainerA_ContentSearch.AddContainedWithinClause tContainerA
            Dim tContainerA_Contents As TRIMSDK.Records
            Set tContainerA_Contents = tContainerA_ContentSearch.GetRecords
            Dim tContainerA_Record As TRIMSDK.Record

            Set tContainerA_Record = tContainerA_Contents.Next
            Do While (Not tContainerA_Record Is Nothing)
                tContainerA_Record.SetContainer (tContainerB)
                tContainerA_Record.Save
                Set tContainerA_Record = tContainerA_Contents.Next
            Loop
        End If
        lRow = lRow + 1

       sColumnA_ContainerNumber = Sheet1.Cells(lRow, "a")
       sColumnB_ContainerNumber = Sheet1.Cells(lRow, "b")

    Loop
    tDatabase.Disconnect
End Sub

 

Cheers,

Erik

Trusted Contributor

Re: Modifying the container field at the back end

Thanks all, looks like our guys are having some success.

 

Much appreciated.

 

Cheers

//Add this to "OnDomLoad" event