Coming to you all for some advice request regarding building out reports for my app.
TL;DR - complicated data structure that needs to summed up, running into “app too busy” errors.
Preface: I’m building a management tool for race teams. Starting out with a parts inventory.
The relevant Things are: Part, Part Movement, Part Category, Part Prices, and Applications.
Here’s a simplified diagram of the Things and their links:
Any constructive criticism is most welcome about this setup!
Here’s the logic for the setup:
- Each part can be in multiple categories
- Each part can be applicable to one or more manufacturers, models, or car types
(for example, a windshield wiper might be applicable across the board, whereas a steering wheel will be specific to a single manufacturer, model and car type)
- Each part can have multiple prices across time (a price for a part might go up or down), or depending of it’s condition (for example, a used engine with 10k miles will have a different price than a brand new engine or one with 5k miles, but they remain the same part)
- To track inventory as well as services done on a car, any new entry is done as a Part Movement.
- Ordered a new part and adding it to inventory? Add a new part movement with +1 for Qty. Choose a price linked to that part and the values copy to the part movement (to keep historical records)
- Used a part in a car service? Add a new part movement with -1 for Qty. Choose a price linked to that part and the values copy to the part movement (to keep historical records)
This is where we get to the reporting!
First, here’s a sample of a report:
We need to see a summary of the total cost and retail value of all parts.
Let’s keep in mind that we’ll need to filter the report as well (i.e. Generating a report that contains parts for only a specific vehicle).
For each part, we need to search for it’s Part Movements.
Drilling down further, for each Part Movement we need to calculate the following for new, used and total:
- Qty * Cost (Total Cost)
- Qty * Retail (Total Retail)
Now that we have the Cost and Retail for each Part Movement, we move back up to the Part level.
For each Part, we need to sum the following for all of it’s Part Movements:
- Total Qty Used
- Total Qty New
- Total Qty
- Avg Cost
- Avg Retail
- Total Used Cost (sum of filtered Step 1)
- Total Used Retail (sum of filtered Step 1)
- Total New Cost (sum of filtered Step 1)
- Total New Retail (sum of filtered Step 1)
- Total Cost (sum of Step 1)
- Total Retail (sum of Step 1)
My main concern at this point is that I haven’t found a way to catch if the workflows below fail and deal with it accordingly.
My gut also tells me there’s a much better way to do this, but I can’t for the life of me figure it out.
I did test generating these values and some of the totals as each Thing is created or modified using backend triggers.
It seems these triggers are only somewhat reliable. It’s happened that triggers just didn’t run, and the report ended up being off since the “total” fields were blank. It seems safer to generate the values on request
If any of you have any advice, it would be much appreciated, and thank you for reading this whole novel! Hopefully this proves useful for others as well.
Here’s what I’ve done so far, doing hours of testing and failing.
Test: Repeating group of parts that sums and filters as required directly on the front end.
Text elements with many “search for” and advanced filters to account for filtering by items in a list that can’t be done with a regular search filter (i.e. User selects filter by Manuf 1 and Manuf 2, need an advanced filter to intersect those values with the values in the Part’s Applicable manufacturers).
Result: Way too slow, page starts timing out and getting Chrome errors about unresponsiveness when a report contains anything over 100 parts.
While this always ended up displaying the data, the fields became unmanageable very quickly, and it’s terrible UX to have Chrome say the page is unresponsive.
Test: Create a “Report” and “Report Line items” Thing. Two backend workflows that are passed the relevant filters as parameters.
The workflow does a search for all the Part Movements (filtered by all, new parts only, or used parts only), then take’s “each Part Movement’s Part”, unique elements, and filters them by the parameters provided. Again, we need to use some advanced filters here to account for intersecting lists which obviously slows things down.
The result of this search is the list of Parts we need, that is “set list” to the Report Thing.
Now we trigger a second, self-iterating, workflow to run Step 1 and Step 2 above for each Part.
To this workflow, we pass the Report’s “Parts” first item, and a Search for all of this Part’s Part Movement.
This second workflow creates a new Line Item for the report, and sets all the relevant fields using the provided parameters.
(this screenshot is actually from the test below, so in this test I was settings all the fields in one step but the screenshot only shows a couple fields being set)
In a second step, the workflow removes this part from the Report’s field (to prepare the next iteration for “report’s Part:first item”, and adds this line item to the Report’s Line Items field.
Finally, once the Report’s Part field is empty and all our line items are calculated, we have one more step which sums up all the line items to the Grand Totals on the Report.
Result: This has been working, as long as only one report is launched at a time. As soon as I try to generate two reports simultaneously, I get an “app too busy” error. This is also fairly time consuming (~15 minutes for 500 parts) but at 2 seconds per Part that’s not unreasonable. I could conceivably create a queue for report generating, to ensure that no two reports are run at the same time across users.
Test: Same as the second step, but instead of having a single backend workflow to set all the line items totals at once, I split it up in multiple workflows as per the screenshot above. This slows the generating down a bit more, but seems to lead to less “app to busy errors”
Result: I was able to run two reports simultaneously sometimes, but still run into issues of “app too busy” on certain occasions if I do so.