Tables [dbo].[Goal]
This is the version level table for goals in the application. There will be a record added to this table everytime a goal is created/modified.
KeyNameData TypeMax Length (Bytes)NullabilityDescription
Cluster Primary Key PK_Goal_ID: IDIndexes XGoal_ItemID_IDID[dbo].[uniqueid]9NOT NULLKey to the dbo.goal table
Indexes XGoal_ItemID_IDForeign Keys FK_Goal_ItemID: [dbo].[Item_Goal].ItemIDItemID[dbo].[uniqueid]9NOT NULLItem. link to Item_Goal.ID
NoteActivityID[dbo].[uniqueid]9NOT NULLAudit Note. Link to Act_Hdr_Note.ID.
Foreign Keys FK_Goal_GoalStatusDE: [dbo].[Order_Status_DE].GoalStatusDEGoalStatusDE[dbo].[dict_id]5NOT NULLStatus. Link to Order_Status_DE.ID.
Foreign Keys FK_Goal_TargetAnswerDataTypeDE: [dbo].[Answer_Data_Type_DE].TargetAnswerDataTypeDETargetAnswerDataTypeDE[dbo].[dict_id]5NOT NULLData type. Link to Answer_Data_Type_DE.ID
Foreign Keys FK_Goal_TargetOperandDE: [dbo].[Operand_DE].TargetOperandDETargetOperandDE[dbo].[dict_id]5NOT NULLOperand. link to Operand_DE.ID
TargetValuevarchar(255)255NOT NULLThe goal target. It's a varchar field but can also hold numeric values. App will treat it as text/numeric based on the TargetAnswerDataTypeDE column
Foreign Keys FK_Goal_TargetPicklistID: [dbo].[Picklist].TargetPicklistIDTargetPicklistID[dbo].[dict_id]5NOT NULLAnswer Picklist. Link to Picklist.ID
Foreign Keys FK_Goal_TargetPicklistAnswerDE: [dbo].[Answer_DE].TargetPicklistAnswerDETargetPicklistAnswerDE[dbo].[dict_id]5NOT NULLTarget Answer. Link to Answer_DE.ID
StartDateFuzzyWhenvarchar(50)50NOT NULLDate goal started in fuzzy format (ie 04 Jan 2016)
StartDateFuzzySpecificityvarchar(1)1NOT NULLShould be a D for "date" or "F" for fuzzy date.
StartDateFuzzySortAsvarchar(50)50NOT NULLStart date in a date format.
DurationNumberint4NOT NULLA number for duration. Works along with DurationUnits
DurationUnitsvarchar(2)2NOT NULLDays/Months/Years
ToBeMetDTTMdatetime8NOT NULLCalculated date based on DurationNumber and DurationUnits
BaseLineValuevarchar(255)255NOT NULLThe current value of the item the goal was set for. We store the last result saved prior to the goal being started. So if the patients Glucose was 150 when a goat was started to bring it to 120… the baseline would be 150.
ManagedByProviderID[dbo].[uniqueid]9NOT NULLWhen ManagedByProvidertype is P it would tie to provider.id. When R it would tie to referring_provider_de.id
ManagedByProviderTypechar(1)1NOT NULLP - Provider/R - Referring Provider
TrackedByID[dbo].[uniqueid]9NOT NULLWho is tracking the goal. Link to idx_user.id, or team_de.id, or referring_provider_de.id depending on the TrackedByType
TrackedByTypechar(1)1NOT NULLU/T/R (U for user/provider, T for Team, R for referringprovider.
PatientConfidenceLevelint4NOT NULL0-10 rating of patient confidence
RecordedDTTMdatetime8NOT NULLDate entry was recorded
OverdueDTTMdatetime8NOT NULLDate entry is overdue
Foreign Keys FK_Goal_SiteDE: [dbo].[Site_DE].SiteDESiteDE[dbo].[dict_id]5NOT NULLSite. Link to Site_DE.ID.
WhoForID[dbo].[uniqueid]9NOT NULLuser/provider the goal is tied to. Link to dbo.Idx_user.id
WhoDidItID[dbo].[uniqueid]9NOT NULLuser/provider who added the goal. Link to dbo.Idx_user.id
LinkedItemTypechar(2)2NOT NULLPL/RS/FI (order, result, finding)
LinkedItemDE[dbo].[dict_id]5NOT NULLItemID. When The LinkedItemType is 0 this corresponds to dbo.qo_classification_de. When it's RS or FI it corresponds to dbo.qo_de
ParentGoalItemID[dbo].[uniqueid]9NOT NULLID of parent goal. Link to Item_Goal.id
ProblemLinkListvarchar(500)500NOT NULLA piped list of problems the goal could be tied to. Each entry in the list links to dbo.Problem_header.id
Check Constraints CK_Goal_IsUnverifiedFLAG_Yes_Or_No : ([IsUnverifiedFLAG]='N' OR [IsUnverifiedFLAG]='Y')IsUnverifiedFLAG[dbo].[BOOL]1NOT NULLUnverified? Y/N
VerificationTaskOwnerID[dbo].[uniqueid]9NOT NULLID of user/team who owns a task. Link to idx_user.id
VerificationTaskOwnerTypechar(10)10NOT NULLType of owner. Would be U/T for user or team
Check Constraints CK_Goal_SaveAsDefault_Yes_Or_No : ([SaveAsDefault]='N' OR [SaveAsDefault]='Y')SaveAsDefault[dbo].[BOOL]1NOT NULLY/N