[Report] Create a nightly automated export of inventory data

[atdservicebot]

What application are you using?

Data Tracker

Describe the problem.

Automatically export the inventory items list to an Excel file each night at a predetermined time between 10 PM and 5 AM. This export would only include inventory items which have an ACTIVE status and a FINANCIAL type.

This list is used to determine the quantity of an item on hand and its unit cost at the time the list is ran. The product of those gives the total value for that inventory item. The sum of those products gives the total inventory value.

There are times that we need to review historical information about an inventory item or the overall inventory value. Unless we ran an items report on the day for which the information is needed, it is somewhat difficult to determine prior quantities and values for an item. It is more difficult to determine the total inventory value for a prior day.

Describe the outcome you'd like to see when this feature is implemented.

Have an Excel file automatically created and saved daily that would include at least the following fields from the inventory items list: Status, Type, Item, SKU,Common Name, Object, UoM, Unit Cost, Total On Hand, and Total Value.

Since the unit costs include 4 decimal places, please format the unit cost field to display the full unit cost rather than just displaying to the cent level. (Formatting for 4 decimal places has been done in the attached example file but the original exported data was only formatted to two decimal places.)

Including additional fields (or all fields) in the Excel file is OK.

The data in the Excel file should be sorted in ascending order on the SKU field.

If is is "easy" to list the total overall inventory value, that would be nice. However, that is not critical as we can add a formula to sum the TOTAL VALUE of each inventory item to get the overall inventory total value.

The naming convention of the saved file should include the date the file was created. If the date could be shown as YY-MM-DD (or YYMMDD if the dashes cause a problem) within the file name, it would make is easy to view the files in increasing or decreasing date order based on the name (assuming that the remainder of the characters in the name do not vary from one day to the next). A possible naming format is "Inventory Items Status 21-11-02.xlsx".

For the future, assuming we are able to create a field that will show the current quantity of an item that is on order, the QTY ON ORDER field should be included in both the inventory items list and this Excel file. (The "on order" field was included on one of the old AIMS inventory reports.)

A file ran today is attached showing a possible Excel file with this information.

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

There is no work around currently being used. The item list could be manually ran and exported to Excel each day by a staff member. However, doing so during working hours means that the information might change after the list is ran on a given day. Automatically having this list run and export between 10 PM and 5 AM daily should result in consistent values that represent the end of one day/beginning of the next day values.

Is there anything else we should know?

There is no specific deadline for this. However, this will allow AMD and Finance to review inventory status information for prior dates as needed whereas we can currently only see that information for the current day unless, on a given day, someone happened to run an inventory list, export it to Excel, and also save it.

Implementation by January 1, 2022 is desirable and by December 1 would be great if practical.

Requested By Ronnie B.

Attachment (5.43mb)

Request ID: DTS21-102925

[amenity]

Will probably store these on G Drive

[mateoclarke]

Ronnie gave me a call after speaking with Brian C. He said the solution doesn't necessarily have to be a nightly export and seemed to suggest that a real time report in the Reports tag might be just as good. Either way, it sounds like there needs to be more discovery and scooping work before we commit to implementation.

[amenity]

Further email thread:

Ronnie 10/29

Brian talked with me after I submitted the DTS Request and he would like to discus this with y’all before you spend any noticeable amount of time. He is wondering if a spreadsheet can be developed that can include multiple days (rather than a separate file for each day) that would be searchable to display a specific day or a range of days.

John 10/29

Thanks, Ronnie.

It would be quite easier for us to produce a separate file each day. That is a simple query (just taking a snapshot of the current inventory totals).

If we were to maintain all this in just a single file, my concern would be that the file would somehow get corrupted through user editing. We would need to maintain a separate database table where we store historical snapshots.

But I can understand why a single file would be helpful. I do have a script which generates point-in-time counts based on the transaction logs. I've attached a copy. The downside is that it puts a pretty heavy load on the database. We'd need to think through the best way to deploy it. running_inventory_totals.xlsx

[johnclary]

@Charlie-Henry let's discuss

[johnclary]

This report should be sorted by commodity code ascending. Only capture "financial items"

[johnclary]

Socrata dataset is here. And here is a PR, which might be too cute, but makes use of Knack services with light modifications so as to ensure a running log is created.

Todo:

• Update knack services readme and get the PR reviewed
• Deploy airflow DAG
• PowerBI dashboard (@Charlie-Henry)

[johnclary]

I goofed the scheduling of this DAG, resulting in duplicate rows per day in the Socrata dataset. See PR #85 pending review.