Editing Field standardisation (section)

==Standardised fields==
===Context===
Field names need to be unique only within a ''service''. Therefore, there is no benefit in making detailed 'helpful' fieldnames such as <code>WhanauOraTotalSpend</code> and <code>TupaiaTotalSpend</code>. A better strategy is to simply name each <code>TotalSpend</code>, and then it becomes much easier to aggregate spend across services<ref>Or indeed, joining any data point across services.</ref> if there is a good use case for doing so.

In the first example, a sample query would need to look something like:<ref>Note this has been drafted as an example without testing.</ref>

<syntaxhighlight lang="sql" line>
with ctea as
(
	select
	a.clientid,
	sum(j."FamilyTotalSpend")
	
	from kotahi.activities a,
		jsonb_to_record(customfielddatajson) 
			as j(
				"FamilyTotalSpend" float
				)
	
	where 
		activitytypeid  = 27 --Whanau ora request submission
		and
		customfielddatajson->>'FinanceCompleted' = 'on' --has been paid
	group by a.clientid
)
,
cteb as 
(
	select
	a.clientid,
	sum(j."TotalSpend")
	
	from kotahi.activities a,
		jsonb_to_record(customfielddatajson) 
			as j(
				"TotalSpend" float
				)
	
	where 
		activitytypeid  = 32 --Transition to adulthood financial assistance
		and
		customfielddatajson->>'FinanceCompleted' = 'on' --has been paid
	group by a.clientid
)

select 
a.clientid,
sum(a.FamilyTotalSpend,b.TotalSpend) "TotalSpend"

from ctea a
left join cteb b
on a.clientid = b.clientid

group by a.clientid;
</syntaxhighlight>

In the second example, the approach is much simpler:

<syntaxhighlight lang="sql" line>
select
a.clientid,
sum(j."TotalSpend")

from kotahi.activities a,
	jsonb_to_record(customfielddatajson) 
		as j(
			"TotalSpend" float
			)

where 
	activitytypeid in (27,32)
	and
	customfielddatajson->>'FinanceCompleted' = 'on' --has been paid
group by a.clientid;
</syntaxhighlight>

Also note that <code>jsonb_to_record</code> seems easier to cast data types from a JSON array for aggregation, but there might be better ways.<ref>Postgres 17 has [https://www.postgresql.org/docs/17/functions-json.html#FUNCTIONS-SQLJSON-TABLE JSON-TABLE], for example.</ref>

===Reconciling old keys===
If suboptimal naming is already in operation, standardisation will have to occur in downstream logic. For example, reference data which 'maps' fields to standardised naming and/or potentially baked into analytical products such as materialized views. Either way this should also be documented separately.

===List of standard keys===
Below is a list of fieldnames that should be used for a given thing. This should be kept updated for each new service/activity, or any changes made to existing work.

{| class="wikitable sortable"
! Fieldname || Purpose
|-
| ClientPresent || Yes/No indicating whether client was present for this activity<ref>Note that 'Client present' is available as a 'generic' Kotahi field. However it is currently not possible to set these as Mandatory, so we are defining them separately in each activity.</ref>
|-
| TotalSpend || Any value used as a running total for any payments to client/family
|-
| RequestAmountTotal || The total value of any requisition or request for financial assistance to a client/family
|-
| RequestItem[n] || Description of an item or product being requested
|-
| RequestAmountItem[n] || Cost of an item or product
|-
| ContactDuration || Duration of a completed activity
|-
| ContactDurationUnit || Unit of duration
|-
| ContactMethod || The method of any completed contact or contact attempt (eg. phone, email, etc)
|-
| ContactType || The main purpose of a contact or attempted contact (eg. goal plan review, administration, etc)
|-
| ContactLocation || Where the contact took place (eg. onsite or at home)
|-
| ContactOffsiteDescription || Optional description of the offsite location
|-
| Notes || Free text narrative to describe the contact
|-
| [Domain]Goal[n] || Specifies a goal domain (eg. health, finance) and a fixed number of goals
|-
| [Domain]GoalStatus[n] || Records the current status of a particular goal
|}

===List of standard options===
Where an activity field provides fixed/generic options, these should also have standardized fieldnames for easy retrieval. Note that these values are a streamlined/edited list using the list from 2024 as a starting point.

{{Ambox||nocat=true| text = '''Note''' that options are just labels so case and spelling is not so important, and they are not shown here. Fieldnames are case-sensitive and impact heavily on reporting.}}

{| class="wikitable sortable"
! Fieldname || Allowed options || Allowed option fieldnames || Current?
|-
| ContactDurationUnit || Minutes || Minutes || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| ContactDurationUnit || Hours || Hours || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| ContactDurationUnit || Days || Days || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| ContactMethod || Face to Face || FaceToFace || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| ContactMethod || Messenger || Messenger || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| ContactMethod || Phone Call || PhoneCall || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| ContactMethod || Text Message || TextMessage || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| ContactMethod || Email || Email || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| ContactType || Administration || Administration  || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| ContactType || Advocacy || Advocacy || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| ContactType || Assessment || Assessment || style="text-align:center;" | <span style="color:#e01b24; font-weight:bold;">{{#far:circle-xmark fa-xl}}</span>
|-
| ContactType || Case review || CaseReview || style="text-align:center;" | <span style="color:#e01b24; font-weight:bold;">{{#far:circle-xmark fa-xl}}</span>
|-
| ContactType || Cold call visits || ColdCall || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| ContactType || Did Not Attend || DNA || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| ContactType || External Referral || ExternalReferral || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| ContactType || Family conference || FamilyConference || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| ContactType || Family Request || FamilyRequest || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| ContactType || Goal Plan Review || GoalPlanReview || style="text-align:center;" | <span style="color:#e01b24; font-weight:bold;">{{#far:circle-xmark fa-xl}}</span>
|-
| ContactType || Internally Referred || InternallyReferred || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| ContactType || Workshop || Workshop || style="text-align:center;" | <span style="color:#e01b24; font-weight:bold;">{{#far:circle-xmark fa-xl}}</span>
|-
| ContactLocation || Aged Care Facility || AgedCareFacility || style="text-align:center;" | <span style="color:#e01b24; font-weight:bold;">{{#far:circle-xmark fa-xl}}</span>
|-
| ContactLocation || Cafe / Other || CafeOther || style="text-align:center;" | <span style="color:#e01b24; font-weight:bold;">{{#far:circle-xmark fa-xl}}</span>
|-
| ContactLocation || GP Visit || GP || style="text-align:center;" | <span style="color:#e01b24; font-weight:bold;">{{#far:circle-xmark fa-xl}}</span>
|-
| ContactLocation || Home / Whānau Visit || HomeVisit || style="text-align:center;" | <span style="color:#e01b24; font-weight:bold;">{{#far:circle-xmark fa-xl}}</span>
|-
| ContactLocation || Hospice || Hospice || style="text-align:center;" | <span style="color:#e01b24; font-weight:bold;">{{#far:circle-xmark fa-xl}}</span>
|-
| ContactLocation || Hospital Clinic || Hospital || style="text-align:center;" | <span style="color:#e01b24; font-weight:bold;">{{#far:circle-xmark fa-xl}}</span>
|-
| ContactLocation || Offsite || KPT || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| ContactLocation || Onsite || KPT || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| ContactLocation || Social Agency || SocialAgency || style="text-align:center;" | <span style="color:#e01b24; font-weight:bold;">{{#far:circle-xmark fa-xl}}</span>
|-
| ContactLocation || Specialist Clinic || SpecialistClinic || style="text-align:center;" | <span style="color:#e01b24; font-weight:bold;">{{#far:circle-xmark fa-xl}}</span>
|-
| [Domain]GoalStatus[n] || Complete || Complete || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| [Domain]GoalStatus[n] || In progress || InProgress || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| [Domain]GoalStatus[n] || Incomplete || Incomplete || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| [Domain]GoalStatus[n] || Not applicable || NA || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|}

====Assessment====
Note that there is no <code>Assessment</code> contact type above. This type is a hangover from Aiga where freetext narrative case notes were used to hold all contact types - including 'structured' assessments. In Kotahi, we want '''all''' assessments to be structured as an activity, so that we can pull data around goals and progress for all clients. Therefore, any 'assessment' should be the formal activity for the service concerned.<ref>For example, <code>Assessment & Goal plan</code> in TTA service, or <code>Whanau ora assessment/goal plan</code> in Whanau ora service.</ref> If any other notes or comments need to be made at the same time, free text areas will be available within the assessment activity.

This same principle also applies to contact types <code>Case review</code> and <code>Goal plan review</code>, as of update log [[Whanau ora/Tupaia 20250224]].

====Workshop====
Also note that the contact type <code>Workshop</code> has been removed, since a scheduled 'group session' should be recorded via the 'Group Activities' component in Kotahi.

This will be visible in the client timeline and 'Group Activity', but is not saved as an ''Activity''.

====Contact location====
As shown, there were previously a wide range of options here. However, we determined that we really only want to know if it occurred onsite at K'aute, or out in the community - whether a home visit or somewhere else. This field therefore now has only two available options.