Update Task Order code to consider DEPT when merging records

[ChristinaTremel]

Current State from Charlie:

We're using that TASK_ORDER_ID field as the primary key and if there are duplicate TASK_ORDER_ID's we "coalesce" by just commas separating all of the BYR_FDU's into one text field. It looks 24ART01073 has different data all across all of the fields, not just BYR_FDU so we will need to rework this if we want it to behave differently.

Update we'd like to make:

• Create field that combines TASK_ORDER_ID field and DEPT field as the primary key.
• Use new field TASK_ORDER_DEPT_ID generated in Knack to map that connection - #18043

Background Context:

When the Public Works and Transportation departments merged in October, finance deactivated all of the TKs in DEPT 2400 and created these TKs again in 6200 (our new DEPT code). Currently the only TKs showing in our applications are the 2400 TKs that are inactive and we are unable to see the active current state of the TK that was created in 6200. Users would like the ability to see that information!

[Charlie-Henry]

Hey @ChristinaTremel, do we want to only make this change for finance purchasing or do we want to also include the data tracker?

[ChristinaTremel]

We need this change for both applications! And I just exported the objects from both applications to have as a backup.

[Charlie-Henry]

@ChristinaTremel, did y'all take a look if I broke something with that test data tracker instance?

[ChristinaTremel]

I did and it looks like it did completely remove the task orders from the work order records..

Prod work_orders_signals object:

• 39779 records where task order is not blank

Test work_orders_signals object:

• 0 records where task order is not blank

I'm going to schedule a sync with you and @dianamartin to figure out the best approach to this early next week.

[Charlie-Henry]

It's likely because the way I updated the data. I just deleted all of the task orders then replaced them. I think if I update the existing data to create the new IDs then upload the remaining, the connections should stick around? Maybe we need another data tracker test instance 😆

[Charlie-Henry]

@ChristinaTremel could you check the new test data tracker instance? I think we should be good now?

I see the two 24ART01073's here: https://builder.knack.com/atd/test--austin-transportation-data-tracker--29-jul-2024/records/objects/object_86?builder_table_sort=field_4837|desc&builder_table_search=24ART01073

[ChristinaTremel]

This looks perfect! 🙌

• I was able to see the TKs coming from 2400 as inactive and 6200 as active
• I checked the work order signals object and 39,849 have records with TKs so we didn't lose data there - compared that to production and it looks good
• The signals object with a task order connection also still has 50 records with TKs

@Charlie-Henry this is exactly what we would want to implement into production for both Data Tracker and the Finance app.

[Charlie-Henry]

Cool! I'll start with data tracker production first

[Charlie-Henry]

Production data tracker should now be updated, I'll start processing the Finance Purchasing app now

[ChristinaTremel]

QCed Data Tracker:

• I was able to see the TKs coming from 2400 as inactive and 6200 as active as we did in the test environment
• 39,907 signal work order records had TKs so we didn't lose any TK data 🎉
• The signals object with a task order connection also still has 50 records with TKs so looks good!
• Everything seems to have updated as expected in Data Tracker and I'll be able to work on issue #17078 now that the TKs are accurately showing active/inactive

Will QC Finance and Purchasing once the updates are completed!

[Charlie-Henry]

@ChristinaTremel finance purchasing is done!

[ChristinaTremel]

🎉 Thank you!! I QCed the Finance application and the task order object looks perfect. Also confirmed that the 20 related service request job records still have TK information captured.

I'll let the users know before closing this issue out, but I think we're good to move it to review/QA!

[Charlie-Henry]

The one thing I noticed was the "display name" of the task order should now have duplicates, I'm not sure maybe we add the department code to that as well?