Change Request Name and Number

[DonSenchuk]

I'm looking at implementing Change Request functionality.

Is there a way to synchronize the Name to the auto-generated Number?

Is there a way to add a prefix to the auto-generated Number?

[MikeLockwood · Accepted solution]

Couple of thoughts:

1. When using autonumber, it's definitely a good idea to use prefixes, and possibly suffixes.  Do so in the OIR for the object.  Example here.  note - The position determines whether prefix or suffix, no specific tags for this.  Prefixes help especially with reporting.

2. It's very helpful to users for the Name to be some words describing the change in some way.  If you make the Number also be the Name, this removes a helpful function for users.  May want to take a second look at this.  But - Users tend to fill in Name with the "Joe Smith" or equivalent - have to guide them on what to put.

[DonSenchuk]

So you're showing 'CR' as the prefix and 'ECN' and the suffix, correct?

The reason I was thinking of the name=number route was because of 'Joe Smith' but also because the Description field should contain the description. The Name would really be something like ESR 01234. To me this is an added level of complexity where the user would need to grab a number from some spreadsheet. Since I can eliminate that via number prefixes I wanted to just match the name to the number.

Seeing how easy prefix/suffix would be for the number, I may just hard code the Name to match the sub-type of the CR being created. That would solve several issues at once if I can get it to work.

[BenLoosli]

In Mike's example you will have a 6-digit number prefixed with CR and suffixed with ECN. CR123456ECN

You can change the number of characters in the number by changing the '6' to however long you want your sequence number to be.

You can also change the way that the sequence numbers are generated by playing with the sequence table in Oracle. You can adjust the increments and the starting number. There are articles on how to do this in the community, I asked the questions back in the beginning of the year.

By default, the sequencing of the number is driven by a new start and then sequentially. Day 1, I have CR00020 for my first one, then do 3 more so CR00023 is the last for that day. Day2, the first is CR00040, then sequentially for that day.Day3 would be CR00060 and up. This can also be changed directly in Oracle.

[MikeLockwood]

By the way, I've shared this many times and had many discussions about it, but as far as I know, it's not been addressed by PTC.

If you use Oracle (don't know about SQL Server on this), sequences are "cached" with 20 values every time you start Oracle.  When you restart, you lose any numbers that have not been pulled. So - You might have autonumber 000523, 000524, ,000525.  Next number pulled is 000541.

Easy way to fix: update each sequence (there are many, all independent) to NOCACHE.

Seems that Windchill's creation and configuration of the database would address this by now but I don't think it does.

[DonSenchuk]

We're on SQL, so if anyone knows the specifics regarding the sequences for this, please do share.

Without doing a detailed study, it looks like our CNs don't skip numbers.

[JørnAHansen]

We're ‌on MS SQL as well. My experience is the same as your that we don't experience the number skips any more.

By reading your post I just realized now that this probably started at the point in time when we switched from Oracle to MS SQL.

[BineshKumar1]

I have seen the same issue with SQL server 2012. In both the cases, the root cause was an abrupt shutdown of SQL server machine because of hypervisor failure

The issue is described in this CS https://support.ptc.com/appserver/cs/view/solution.jsp?n=CS208897

Thank You,

Binesh Kumar

Barry Wehmiller

[DonSenchuk]

More questions regarding this.

1. Can I switch over to have any new CNs start at a certain number? (I assume this answer would also pertain to Change Requests, Problem Reports, etc.)

2. If the answer to #1 is 'YES', can I set up different start points for different contexts?

     2a. Specifically, we have 15 contexts where the CNs are at a certain number in the sequence (around 52000). Meanwhile, 3 other contexts are at a completely different point in their sequence (around 21000). This means that the first 15 would share a numbering sequence while the other three would share their own unique sequence.

[BenLoosli]

1) Yes, you can set the 'start' number by adding to the current number to arrive at your 'start' number. If your desired start number is 60000 and your current number is 52150, then you add the difference to the current number sequence value.

For an Oracle DB, you would have:

alter sequence WTCHANGEREQUESTID_seq NOCACHE;

alter sequence WTCHANGEREQUESTID_seq increment by 7850;

select WTCHANGEREQUESTID_seq.nextval from dual;

alter sequence WTCHANGEREQUESTID_seq increment by 1;

Verify these numbers on your system, might need to adjust by 1.

2) As long as they use a different *ID_seq number they can be unique.

You can create additional sequences in the DB and then modify the OIR to use those new sequence IDs.

[BenPerry]

Don,

The short answer is yes to both questions.  In your OIR, which might be residing at site level or org level, you're specifying the next number to come from WTCHANGEREQUESTID_seq, which is a number sequence in the database.  If you wanted to use different numbering for different contexts, you'd need to create a number sequence in the database for each context.  And then you'd need to put OIRs in the contexts to grab the number from the specific database sequence instead of the WTCHANGEREQUESTID_seq sequence.

In my opinion, this is overboard.  Perhaps too much overhead.  But I don't know your specific use case.  So perhaps for your business it might make good sense.

[BenPerry]

Ben Loosli‌, great minds think alike.  And they have the same name.

[gshankaraputhra]

Dear Don,

Did you figure out a way to populate the name with the serial number from the number field? If yes, can you share the logic.

I agree with you about utilizing Description field to describe the reasoning for change.

Regards,

Gautham

[DonSenchuk]

I haven't really started the project yet. The question came up in anticipation of upcoming changes we'd like to implement. If the final decision includes insertion of the number into the name I'll post how it was done.