Best practice forum (Archived)

Question about Totara's Appraisal Tables

We are trying to figure out how the data is organized in the appraisal tables and I've been asked to try and find out what tables/columns the responses to the different stages of an appraisal are stored.

There are tables named appraisal_quest_data_3 and appraisal_quest_data_6. There are columns inside these tables called things like data_31, data_64, data_37 etc etc. Since these column names aren't very descriptive (at least to an outsider looking at them) I was hoping maybe someone from Totara could help explain these tables and how they are organized.

To put this into context, we have a very strict requirement to the way that we will print / display the appraisal forms (governement) so i'm having to build a new template to populate the data of the appraisal so that i can generate our version of the form using the data that Totara stores during the appraisal process. So i'm needing to find all of the data elements and where they are located so that i can auto-populate the form i've created.

Thank you for your time!!

John

responses to the different stages of an appraisal be stored.

Permalink | Reply

Hello John,

Suffixes in table names (like "3" in "appraisal_quest_data_3") are their ids in table "appraisal". So query "SELECT name FROM {appraisal} WHERE id=3" will return appraisal name. These tables created dynamically during appraisal activation.

Suffixes in column names (like "31" in "data_31") are their ids in table "appraisal_quest_field". So query "SELECT name FROM {appraisal_quest_field} WHERE id=31" will return question name.

How answer are stored also depends on question types.
Simple questions store answer values in "appraisal_quest_data_[id]" table. Dates stored in timestamps.
"Long text" also use endings "trust" and "format" to indicate if answer is stored in "html", "plain text", or other format.

"Multiple choice (one answer)" stores id of "appraisal_scale_value" table.

For some questions it's impossible to know how many fields required for answer during activation, so they are stored in different tables (one to many relationship):.
Answers on "Multiple choice (several answers)" question type stored in "appraisal_scale_data" table. Answers on "... review" questions stored in "appraisal_review_data".

To get question ids for a stage, you need to join "appraisal_stage_page" first to get pages id and then "appraisal_quest_field".

Also, Please find attached db schema structure for appraisals.

Hope this helps,

Regards,

Valerii

appraisal_db_schema.pdf

Permalink | Show parent | Reply