cancel
Showing results for 
Search instead for 
Did you mean: 

Update Expanded Number?

SOLVED
Go to solution
Highlighted
Jane Hay
Regular Collector

Update Expanded Number?

TRIM 7.1.0 1157

 

We have a Record Type that is likely to run past it's available numbers before the end of the year. I know I can add an extra zero, but is there a way that I can update the existing records to include the extra zero in the Expanded Number field?

 

Numbering pattern is YYYY/GGGGG and we're currently at 2012/88016. We have roughly 10,000 records added each month using this record type. I'm going to change the numbering to YYYY/GGGGGG (adding one extra G), but this will only add the additional 0 prefix to newly created records and not the pre existing records. Given that the Expanded Number field is used as the Unique Identifier for updating records via an import, I'm stumped as to how I can update this field. Does anyone know if it is possible and if so how it can be achieved?

 

Thanks

9 REPLIES
EWillsey
Honored Contributor

Re: Update Expanded Number?

TRIMPort! Export them all, use a macro or script to update the expanded number, and then import again.

Or just write a quick VB script to do it.
Jane Hay
Regular Collector

Re: Update Expanded Number?

thanks for your super quick response.

 

TRIMPort was my first idea too, but I can't get it to update the Expanded Number.

 

I've tried to update Expanded Number 2012/10523 to 2012/010523 and I get the below error:

 

Detail: HP TRIM Property: Expanded Number, Import Field Name: Expanded Number, Value: 2012/010523, Error: Record Number '2012/010523' has already been used.

Am I doing something wrong?

EWillsey
Honored Contributor

Re: Update Expanded Number?

Doh, that presents a problem huh? You'd need to do it through a script then.
Jane Hay
Regular Collector

Re: Update Expanded Number?

thanks

 

I'll see if IT can write me a script. Learning VBA and the TRIM SDK is on my To Do/ Wish list, but I'm not there yet unfortunately.

EWillsey
Honored Contributor
Solution

Re: Update Expanded Number?

Well how's about a free head-start?? :)

 

Create a new excel workbook.  Press Alt+F11, add a reference to the TRIMSDK (tools->references), create a new module, paste in this code, and then execute.  Change the record type to match whatever you're doing.  Run in dev/test first, as always!  Note that if the record won't save for any reason then the number won't be updated (like missing container, conflicting dates, etc).

 

---- VBA Code -----

Sub updateRecordNumbers()

   '' assume record type is "Document"
   '' assume pattern used to be "KKK/GGGGG", needs to have an extra 0


   Dim tDatabase As TRIMSDK.Database
   Dim tDatabases As New TRIMSDK.Databases
   Set tDatabase = tDatabases.ChooseOneUI(0)

   Dim tRecordSearch As TRIMSDK.RecordSearch
   Set tRecordSearch = tDatabase.NewRecordSearch
   tRecordSearch.AddRecordNumberClause "?*"
   tRecordSearch.FilterClear sfRecordType
   tRecordSearch.FilterRecordType tDatabase.GetRecordType("Document")
   Dim tRecords As TRIMSDK.Records
   Set tRecords = tRecordSearch.GetRecords
   Dim tRecord As TRIMSDK.Record
   Set tRecord = tRecords.Next
   Do While (Not tRecord Is Nothing)
      On Error Resume Next
      tRecord.LongNumber = Replace(tRecord.LongNumber, "/", "/0")
      tRecord.Save
      Set tRecord = tRecords.Next
   Loop
End Sub

Jane Hay
Regular Collector

Re: Update Expanded Number?

fantastic, thanks so much. I'll give this a go in the test environment

 

ok, now let me see if I can figure out how to give you Kudos (I know I've seen that around here somewhere...)

Jane Hay
Regular Collector

Re: Update Expanded Number?

Hi again,

 

I was just about to attempt your code in test and noticed your comment: '' assume pattern used to be "KKK/GGGGG", needs to have an extra 0.

 

Our number pattern is actually YYYY/GGGGG.

 

I'm assuming your suggested code will update ALL of the documents against this record type (?). Would there be a way to limit the change to the records created 'this year'?

 

Thanks

Jane

 

EWillsey
Honored Contributor

Re: Update Expanded Number?

Yes it would update all. If you only want ones from this year, change the "?*" to "2012/*".
Jane Hay
Regular Collector

Re: Update Expanded Number?

oh you are good!

 

thanks again

//Add this to "OnDomLoad" event