Newbie confused by calculating total/remaining inventory and status of project completion

AppSheet AppSheet Q&A
,
1

Started using AppSheet on Monday of this past week (!) with an assignment to make a simple project tracking app that also tracks the status of total and remaining amount of construction material inventory at several customer construction sites. I’ve studied a dozen different sample and template apps for both project trackers and inventory, but I’ve never heard of a relational database until a few days ago and I can’t figure out how to, after entering all construction inventory items by customer location, then subtracting from that inventory as items are installed, how to show remaining inventory at each installation and show pie charts or other charts of (1) percentages remaining, and (2) a line graph by each installation and each installer showing #'s installed over each day or week etc. I don’t even know how to describe this to ask for help, but here’s my setup:

Tables:

Installation (information about customer construction locations)

Installer (information about our employees that install the inventory at customer locations and where those installers are assigned at the moment, contains REF to Installation table ID)

Inventory Types (different types of construction materials, contains REF to Installations table ID)

Inventory Log (enter deliveries of inventory types to customer installations, contains REF to Installations ID and REF to Inventory Types ID)

Installed Items Detail Data (enter each instance of each item of inventory as it is installed, contains REF to Installers ID, Installations ID)

Views:

I have a view (form) that allows entries of new inventory items and assigning these to customer installation, works well.

I have a view (form) that scans QR code as each instance of each inventory types is installed at each location, text descriptions of inventory items embedded in QR code is identical to that in Inventory Types, contains REF to Installer ID ,with LOOKUP that lookups where installer is assigned and returns the ID number of installation, Installation ID with LOOKUP that lookups up installation ID number from installer assignment and returns name of installation, Inventory Types ID with LOOKUP that lookups the text description of item installed in the QR codes and returns the Inventory Type ID of each item installed.) This view works.

I have a couple of views (tables) based on slices by customer location with a table listing each inventory type (grouped by inventory type) installed at each sliced location.

I’m stumped as to how to calculate remaining inventory, by count and by percentage remaining.

I’m stumped as to how to define a table view that will contain pie charts of what percentage of total inventory vs. what has been installed.

I’m stumped as to how to define a table view showing a line graph by day showing #'s of items installed at a location (so I can make a dashboard with all of these), and # of items installed by installer (ditto).

If anyone can even understand this and could provide a pointer or two, I’d be most appreciative and I’ll buy you a beer next time you’re in Colorado.

2

Hello @birdienumnum , I don’t think I’ll be going to Colorado anytime soon since it’s 8,765 km away, but anyways.

If you have your list of items in your “Inventory Types” related to each location, you can create a virtual column for adding up all inventory logs that are related to that item in that location and with that you can get the current inventory amount, I’d need to know the exact column to make the expression for you.

Also what do you mean with “by count” and “by percentage remaining”?

Create a dashboard with the “dynamic dashboard” option turned on, add in your “Inventory Types” table, add in the “Installation” table, create a new chart view type based on “Inventory Types” and set it up based on the virtual column I talked about above.

Now when you click a location it filters the inventory types, when you press an inventory type you get it’s graph, rinse and repeat for all the graphs you need.

How do you define “items installed at a location” in this system exactly? because as far as your app description included you’re just moving items around.