[EPIC] OCE Inventory Tracking System

[atdservicebot]

This epic tracks the work performed to implement a KNACK app for OCE's concrete mix inventory.

• OCE currently uses a combination of SharePoint, Excel, and Outlook to manage the inventory needs.
• The scope of this epic is to replace the spreadsheet and SharePoint tools with a KNACK app that will provide an internal expiration notification.
• (Out of Scope) We anticipate this app will be upgraded to add features for the external provider to receive notifications, a public portal for providers to submit attachments and initiate renewals, and internal features to manage mix renewals.
• Here is the new portal link: https://atd.knack.com/oce#home/

Name saroj t.

What application are you using?

Other / Not Sure

Describe the problem.

OCE is currently keeping track of their inventory systems via a excel sheet. They would like to have it so it sends an email when ever the mix is about to expire.

Sometimes the inventory is expires and they have to wait for another batch to arrive.

The file name is: Pre-approved & IDIQ HMAC Mixes - (\Coacd\dfs\PWD\Street & Bridge Division\Office_of_City_Engineer\Engin_Stndrds_Review\MATERIALS REVIEWS\2_HMAC)

Describe the solution you have in mind.

When those mixes are about to expire, we would like to send it to the producer to let them know that the mixes are about to expire. 3months prior expiring, 1 week before expiring and when it expires.

How will we know that our solution is successful?

We are able to send an email

Also the mixes are not expiring

Users

• Saroj T
• Michael K.
• Anna M.
• ocematerialreview email

Sponsorship

Office of the City Engineer

Which Strategic Direction 2023 “Mobility” indicators would this new application impact?

['System efficiency and congestion']

Which Austin Strategic Mobility Plan goals would this new application support?

['Placemaking — Build a transportation network that encourages social interaction through quality urban design, and connects users to the many places that make Austin unique.', 'Economic Prosperity — Promote economic growth for individuals and the city through strategic investments in transportation networks that meet the needs of the 21st century.', 'Innovation — Draw inspiration from forward-looking cities around the world, change the way we think about what’s possible, and set an example for the rest of the country.']

Describe any workarounds you currently have in place or alternative solutions you've considered.

None

They have to send the emails manually

Is there anything else we should know?

This is not an emergency

How have other divisions/departments/cities addressed similar challenges?

Unknown

Requested By Saroj T.

Attachment (32.34kb)

Request ID: DTS23-108155

[dianamartin]

6/12

Synced with Christina on this, we thought that maybe this might have to do with Dan V's request for a PWD inventory tracking project. We ended up mapping out SBO<>SMD relationship in the event that this request needed to create a new application. Christina will reach out to the stakeholders and scope what they need long term. Matt M. let me know in the meeting I was in with him that this engineering group manages over SBO materials and have a spreadsheet they track the mixed materials. They have an expiration date and want to know 3 months in advanced of the expiration and when Matt saw the ability in Knack to send emails based on a field he let the stakeholders know. They had apparently asked him to assist and they were told by the former IT group that their request was too small.

[dianamartin]

9/11 Diana's Notes

Participants: Saroj T, Michael K, Anna M, Jeebika S

Mixed Design: City of Austin Projects

• Review it
• Ready to approve
• Publish OCE SharePoint
• Internal Mixes
• Concrete Producers/Hot Mix
  • Renew every year
  • Expiration time
  • don’t want gaps between those designs
  • Resubmit designs

First thing, manual to SharePoint

• checking daily, weekly
• tried to check calendars
• send an email to producers
• 7 days before it expires

Creating own system

• Once they submit it
• Process, instead of checking it manually
• Upload and check those lists..
• Send it back to producers

Started to make a Power Automate App

• Didn’t finish it

Walk through process

• Approved Concrete Mixes

  • List of Producers (Folder in document library)
  • PDF
    • Mixed ID No.

  Inspector - checks the tab

  • Mixed ID No, inspects what’s on sight
  • Delete and replace for new one

Published PDFs

• Maintain spreadsheet - flags the expiration dates
• Interface spreadsheet with email notification

Questions:

• How long does it take to review and approve?
  • Usually 40 calendar days (1 month - 1.5 months)
  • Up to 3-4 months
• Tests, requirements

Contracts - don’t deal with those contracts

• only deal with the Vendor designs

Is there existing information? How would a Producer know what to do?

Flex Base Material

• OCE: Darren D.
• Approved stockpiles

Current Process

• Not published anywhere right now
• List of criteria

Construction Management group

• couple of conversations

They created a resource account?

One stop place

• disseminate internal documents

G drive: (link)

[ChristinaTremel]

Meeting Notes 9/11

Attendees:

Saroj T, Michael K, Anna M, Jeebike S, Diana M, Christina T. Darren D helps with base materials and it would be good to include him in the future. Would also like Robert to be included.

Current Process

• SharePoint site
• When a mix is about to expire, the producer works with OCE to go through a review process and once the review is approved, OCE publishes mix info in SharePoint
  • List of criteria for how to submit and typically new producers just email a distribution list PWDocematerialreview@austintexas.gov
• There are 3 folders for Approved Material Lists in SharePoint
  • All include Mix ID number and mix
  • Internal stakeholders can go in and see if mixes are available
  • Within each folder are all the approved mixes for that category
  • The PDFs all show the expire date. Once expired, they delete it and replace it with the updated PDF document that's collected via email
  • Would like producers to submit the updated mix before it expires
• Spreadsheet for tracking expiration dates
  • Currently also have an overall spreadsheet on the G drive to track all of these PDFs. An additional layer of work for maintaining the spreadsheet, but they compare it to SharePoint regularly
  • Cell color changes when they are days out of the expiration date
• Current time for review process:
  • 40 calendar days
  • If some of the tests fail, it will take a month to redo it so it can take 3 to 4 months
    • At one point, wanted a place where producers could submit specs and have auto rejection if it doesn't pass certain criteria. (this is not needed for the MVP)
    • Have documentation for all criteria listed out
• Other clarifications:
  • These are not with Master Agreements or Contracts

Current files:

• Concrete Mix Master List (Updated 09.06.23).xlsx
  • G:\Street & Bridge Division\Office_of_City_Engineer\Engin_Stndrds_Review\MATERIALS REVIEWS\1_CONCRETE_MIX_DESIGNS\Concrete Mix Master List (Updated 09.06.23).xlsx
• Pre-approved HMAC:
  • G:\Street & Bridge Division\Office_of_City_Engineer\Engin_Stndrds_Review\MATERIALS REVIEWS\2_HMAC\Pre-approved & IDIQ HMAC Mixes.xlsx
• Flexible base:
  • G:\Street & Bridge Division\Office_of_City_Engineer\Engin_Stndrds_Review\MATERIALS REVIEWS\3_FLEX_BASE\Stockpile Reviews.xlsx

Future Process Criteria (Top Priorities)

• Initial need to receive notification that they're about to expire so producers can submit the updated mix before it expires
• Ability to send an automated email to let contractors know when things are about to expire
• Process to submit a mix design because sometimes they don't submit within the expired time (this causes there to be a gap where no mix is approved and OCE doesn't want that
• Create a view that is accessible and works for the inspectors as well.

Next Steps:

• DTS will meet internally to prototype a Knack app based on existing spreadsheets and process defined in this meeting
• OCE will meet internally to provide additional detail to DTS on things they'd like to see
• Christina will schedule check in meeting with OCE to look at prototype and details that OCE discusses

[TracyLinder]

Prototype Work Sesh Scheduled: 10/10/23

Diana, Tracy

[TracyLinder]

Stakeholder Update Meeting Scheduled for 10/24/23

[TracyLinder]

Update 10/10/23

• I converted this issue into an epic. I'm giving the issue an estimate of 2 to account for the discovery time in the notes above.
• We determined this solution will require a new KNACK app/portal for the OCE Division.

[TracyLinder]

New Questions for Stakeholders:

• Do mixes need more than one attachment? What happens to the PDF attachment when a mix is renewed?
• We need to understand this better. Do the construction inspectors use this and how do/should we replicate it?
• Discuss what types of attachments might be associated with the producer records?

[susannegov]

1/22

• [x] Added attachments object table
• [x] Miro Board ERD for OCE app
• [x] Added stockpile_results data dictionary
• [x] Added stockpile_statistics data dictionary
  • [x] stockpile_results
  • [x] stockpile_average - Maybe use formula
  • [x] stockpile_current_statistics - maybe paginated report or a yearly update

I wrote down what the schema from the spreadsheet would look like in theory. Excel File link

stockpile_results table

Field Name	Type	Description
Stockpile Result ID	Auto-Increment	Unique identifier
Sampling Date	Date/Time	mm/dd/yyyy
Testing Agency	Multi-choice	Drop down. Allow to add a new option
Material Description	Multi-choice	Drop down. Allow to add a new option
Stockpile Location	Multi-choice	Drop down. Allow to add a new option
Plant Name	Connection	The plant location name
Stockpile ID	Short Text	ID is per plant location
Gradation 1 3/4	Number	Decimal
Gradation + 7/8	Number	Decimal
Gradation + 3/8	Number	Decimal
Gradation +4	Number	Decimal
Gradation + 40	Number	Decimal
Gradation+ 200	Number	Decimal
Optimum Conditions Dry Unit Weight	Number	Decimal
Optimum Conditions Moisture	Number	Decimal
Compressive Strength 0 Lat Pr	Number	Decimal
Compressive Strength 3 Psi Lat Pr	Number	Decimal
Compressive Strength 15 psi Lat Pr	Number	Decimal
Liquid Limit	Number	Decimal
Plastic Limit	Number	Decimal
Plasticity Index	Number	Decimal
Wet Ball (Mill)	Number	Decimal
Increase in 40	Number	Decimal
Created By	Connection	accounts
Created Date	Date/Time	mm/dd/yyyy
Modified By	Connection	accounts
Modified Date	Date/Time	mm/dd/yyyy

[TracyLinder]

Sync Tracy & Susanne 3/7/2024

List of Major Use Cases (features) to be tested:

1. Add a new producer
   • [ ] Setup what fields required
   • [ ] A confirmation that attachment has been submitted
2. Create a new mix for each of the 3 types
   • [x] Add filter menu between active/expired instead of two tables
3. Create expiration notifications for batches
4. Create a new plant

[TracyLinder]

Q: Why do we have a status comments field? Q: Should user be able to edit mixes from the producer page? (They are able to add attachments) Q: Should user be able to add a new mix from the approved mixes page? Q: Clarify which mix types require a plant.

• [x] Rearrange tables on producer page
• [x] Added a mix, it didn't populate the table on the producer page because it didn't save the connection between the producer and the mix. (Susanne figured out the bug; will finish updating the form.)
• [x] Don't see the producer of the mix on the approved mixes page due to this ☝🏼 reason (most likely)
• [x] Remove public mix list from top nav
• [x] Remove 'Save Record ID' button from producers page (set it to hide view when account is any)

[susannegov]

To do

• [x] Set notification for producer details page for Michael K and service email (the official task is paused currently)