Bug: TCP Days Under Review & Total Days equations showing negative values

[atdservicebot]

What application are you using?

Right of Way Portal

Describe the problem.

Knack is currently calculating the Days Under Review metric based on the "Created" date rather than the "Submitted" date, which is resulting in instances where this metric contains negative numbers (see example in screenshot). We would like for it to be based on the "Submitted" date to avoid this problem.

Website Address

https://atd.knack.com/row#tcp-projects/

Internet Browser: Chrome

Requested By Kyle K.

Attachment (34.91kb)

Request ID: DTS24-111985

[TracyLinder]

@ChrispinP this was upgraded from minor to major so we'll need to discuss. I'll slack you.

[TracyLinder]

Next Steps

1. Update the field to prevent the -1 day from happening from today forward
2. Update the -1 fields to correct to 0 days
3. For those that are more than -1 days, ROW will need to assist in identifying the records and the correct start/end dates so we can do a batch update.

[TracyLinder]

@TracyLinder will inform Samson

[TracyLinder]

Message to Samson & Kyle:

Howdy, I want to update y'all on the ticket submitted about the negative review days on the TCP app. Our devs are working on it and identified 2 root causes for this happening; only one of them we can fix without your help.

1. KNACK is rounding the number down, so we can fix this to keep it from happening. Currently this only happens with reviews that start and end on the same day; the system rounds to -1. We will fix this. We will also fix all records with a -1 review time. The number will update from -1 to 0 if I understand correctly.
2. For all cases where the review day is less than -1, we won’t be able to fix them without your guidance. The most helpful thing you could do is provide us with a spreadsheet with a row for each of the submissions that need to be fixed. This way we can do a batch update. For each submission we will need to know:
   1. TCP ID
   2. Submission ID
   3. Correct Start Date
   4. Correct End Date

Please let me know if there are questions.

[ChrispinP]

Fix for TCP & CSWZ Submission Cycles:

• [x] Created Review Completed Time field - Defaults to 23:59
• [x] Created Concatenated Review Completed field - Combines Review Completed Date + Review Completed Time
• [x] Clear Conditional Rule from Review Completed Timestamp field

CSWZ

• [x] Update records: Explicitly Set Review Completed Time value to 23:59 and clear all Review Completed Timestamp values
• [x] Set new Conditional Rule for Review Completed Timestamp field
• [x] Force Update all 482 records

TCP

• [x] Update records: Explicitly Set Review Completed Time value to 23:59 and clear all Review Completed Timestamp values
• [x] Set new Conditional Rule for Review Completed Timestamp field
• [x] Force Update all 29700 records

Conditional Rule:

Final Result of fix:

Total Days & Days Under Review equations will no longer round to -1 for previous 00:00 values

For values smaller than -1 (i.e. -18) those cycle records must be manually updated. Either: A) Submitted Date is after the Review Started Date or Review Completed Date B) Review Started Date is after Review Completed Date

• [x] Export List of Records with values smaller than -1 that must be manually updated for ROW Staff
• [ ] Batch update list of records with corrected dates provided by ROW Staff for both Cycle objects

[ChrispinP]

Review Completed Time is now set on the inline edit rule for the Completed Date column rather than defaulting to 23:59. This prevents the value from appearing on non-completed records. Additionally removed an old rule that was overwriting the custom Timestamp causing the bug to persist for newer records. This resolves the issue again.

[ChrispinP]

Exported records that need to be manually updated. 37 TCP Submission Cycles and 10 CSWZ Submission Cycles where the Total Days are negative. I have prepared both spreadsheets to provide to stakeholders.

[ChrispinP]

Sent updated spreadsheets to Kyle. Pending data updates.

[KaroEngstrom]

[heart] Engstrom, Karo reacted to your message:

---

From: Chrispin @.> Sent: Wednesday, April 17, 2024 7:32:43 PM To: cityofaustin/atd-data-tech @.> Cc: Engstrom, Karo @.>; Assign @.> Subject: Re: [cityofaustin/atd-data-tech] TCP Days Under Review & Total Days equations showing negative values (Issue #15254)

External Email - Exercise Caution

Sent updated spreadsheets to Kyle. Pending data updates.

— Reply to this email directly, view it on GitHubhttps://github.com/cityofaustin/atd-data-tech/issues/15254#issuecomment-2062068336, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AX7UN6ENZJ25ATFQ4FANUWLY53E5XAVCNFSM6AAAAABBYZC472VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDANRSGA3DQMZTGY. You are receiving this because you were assigned.Message ID: @.***>

CAUTION: This is an EXTERNAL email. Please use caution when clicking links or opening attachments. If you believe this to be a malicious or phishing email, please report it using the "Report Message" button in Outlook. For any additional questions or concerns, contact CSIRT @.***"

[ChrispinP]

@KyleErich Hey Kyle, wanted to check in on this issue and see if there has been any updates or decision on fixing these historical dates for these calculations?

[KyleErich]

@ChrispinP Here's my table with the information requested. I had to approximate on a lot of these since the correct data wasn't always present. Thanks. TCP Knack Adjustments.docx

[ChrispinP]

@KyleErich Hey Kyle, circling back on this. I'm attempting to open the attached document but it is either protected or corrupted. Are you able to attach a csv of the data instead? Thanks

[KyleErich]

@ChrispinP Sure, see attached. TCP Knack Adjustments.csv

[ChrispinP]

Records with negative Total Days is now 0 💯

I found 80 more records that still have negative Days Under Review where the submitted date is correct and we just need to correct either the Review Started or Review Completed date.

TCP Submission Cycles 80 Negative Days Under Review 06.11.24.xlsx @KyleErich Here is a spreadsheet of these 80 records we need to update.

[TracyLinder]

Per Chrispin, the bug is fixed. I spoke to ROW and they are ok closing this out as a fixed bug. I'll ask ROW to submit a ticket for the data cleanup of the list of 80 Chrispin attached above.