You are on the right track. The data is in the ts_Changes table. It sounds like you are expecting to see exactly the same thing in the table as you see in the Change History, but this is a relational database so the data is stored like that. Instead, a lot of the data is actually references to other tables. For example, the TS_FLDID column is a number that tells you which field was changed, but have to look at the TS_FIELDS table to figure out which field has which number. The TS_TYPE column tells you what type of field was changed (String, binary, relational, etc). Then, depending on the type of field, you will look at the prior and new columns for that type of field.

Each column is explained in the schema guide, but it can be confusing. Can you tell us more about what you are looking for? Maybe we can explain it better.

We are actually looking to pull the data into a .csv file so that we can import into Jira.  So I have been tasked with getting the change history data exported out.  Thank you for pointing me to the TS_Fields and TS_Type tables.  I will take a look at them and see if I can get farther.  I had looked at the schema, but was not able to figure this out and yes, this takes relational database to a whole other level!

As noted by Cindy:

The ts_ChangeActions table basically shows which Item changed, the Transition involved and who did it.  This is like the "title line" in the History.  The "ts_Changes" table contains the details of that change.  This is what you see when you click the "+" to expand a change history title line.  There will be 1 record in "ts_Changes" for each field that changed during that Transition.  A record in "ts_ChangeActions" will be referenced by 1 or more "ts_Changes" records via the "ts_ActionStamp" column.

Hi,

I just have a follow up question on this.  I looked at and understood the TS_fldid and ts_Fields table to be able to figure out what field was changed; however when I look at row for the change which has a 0 in TS_Type so it's an integer field and I'm looking at the TS_Priorint and TS_NewInt in TS_Changes and they have 17 and 18 for the prior and new, but in SBM that shows as Low and High.  What table am I supposed to use to find that data?  Also how are the Low and High considered an integer change and not a string field change?  Same with my example above.  I found the "Moved" in the TS_Fields table, but in TS_Changes the ts_priorint and ts_newint show up as 0 and 1 and that equates to "No" and Yes".  How do I figure that out?

Thanks!

Reading the data from the database is not an easy task. Have you looked at the table diagrams on pages 451 and 452 of the schema document? It gives a visual of how these tables are tied together. 

About the field that has the Low/High value, I will assume that is a Single Select field. To translate the number from the TS_Changes table to the words you see in the browser, you would need to look at the TS_SELECTIONS table. The TS_ID will match the number you see in ts_changes and ts_name is the value you see in the browser.

You asked why these were numbers (17 and 18) instead of strings (Low and High). This is because selection options can be renamed at any time using Composer. If these were strings, all the data would be broken if one of the options were renamed. SBM has worked hard to make sure everything is stored relationally so names and text can be changed freely without impacting existing data. 

About the field called "Moved". I will assume this is a binary field. Binary fields are always stored as either 0 (for "no" or "false") or as 1 (for "yes" or "true"). Again, these values can be renamed in Composer. I believe these are also in the TS_SELECTIONS table.

I hope that helps.

Hi Vickie,

Thanks so much for that additional information!  I think those are the missing pieces to my puzzle.  Especially thank you for pointing out the table diagrams in the schema document.  I've been pouring over that document trying to figure out the tables and never saw the diagrams - hahaha.  They will be very helpful!

Thanks!

Linda