Best way to Collect and Compare value from multiple related table and show in UX?

[matteolavaggi]

Hi to all, i'm in trouble with my first appsheet application deployment and i need to find a way to do this:

I would track order made of product and compare it to target also made of product.

The database structure is this

Orders

-> ID | Data | Related Order Details (list of ref)

Order Details

-> ID | Order (ref) | Product (ref)

Targets

-> ID | Related Target Details (list of ref)

Target Details

-> ID | Target (ref) | Product (ref) | Quantity 

Order store product sell with a date

Target store the selling target created for every product and date

Order Details has not quantity because order detail row (with product) are inserted one by one (also if some one need to buy 3 pcs of the same product we will create 3 entry Order Details in the Order).

Target Details has quantity because is easy to create the target (for example sell 10x PRODUCTA today).

I'm not able to find a way to track this. In a excel style i would filter target for data, filter target details for that target, filter product for that target details, multiply for quantity and i have the target for each product . Next i will filter oder for data, filter order details for that order, count the product and i have the selled for each product.

But i cant find a way to have this in appsheet.

How can i get all the product in the order details for the order with a specific date?

How can i get all the product in the target details , multiply for the quantity , for the target with a specific date?

And how can i handle this to show the target progress in a view? I need to create a table "Goal" with column Data | Product | Sell | Target and populate it whit these query above? But i dont wont to save these data, they must be computed "on the fly" to track the progress, not stored. 

I use gsheet as database.

[MultiTech · Accepted solution]

Hey Hey!

You might check out a video series I did that shows how to start building an Order Building app (with a shopping cart style of functionality) all the way through several updates and iterations.

• While not exactly the scenario you're describing, the "nuts and bolts" of how the system is put together will closely resemble what you might need/want inside your system.

https://www.youtube.com/playlist?list=PLo4qc7daF3rczyFE7kviX_U_9lH4Z-pY9

Specifically check out how list dereferences are used for totals; this will help you with your goal totals.

The "Building Slice" might also be of use for you, where you might create a "Todays_Target" slice to hold the record for today - and then maybe a subsequent slice holding the child records for today's target record, similar to how the shopping cart app has a slice of the Building_Order_Details.

Hope it helps. 💪 Happy Apping!

_________________________________________________________________________________

I asked Appster what it thought, and this is what it had to say:

To address the challenges you're facing with tracking and comparing orders to targets within your AppSheet application, you're on the right track with your database structure comprising Orders, Order Details, Targets, and Target Details. Let's break down this into actionable steps to create a dynamic comparison in your UX without storing computed data:

1. Creating Virtual Columns for Computation:
- In the Orders table, add a Virtual Column (VC) that will count the number of Order Details associated with each order for a specific date. Something like this:

COUNT([Related Order Details])

- In the Targets table, add a VC that will calculate the total target quantity for products by using a formula that multiplies the Quantity in Target Details by the number of Product references for a specific date.

2. Dynamically Comparing Targets and Orders:
- To compare the target to the actual orders, you would need a VC that either resides in the Orders table or in a separate table that could calculate the difference between the counted order details (sell) and the target details (target quantity) for each product.

3. Setting Up a UX View for Target Progress:
- To visualize the target progress, create a Dashboard view or some other form of UX view that dynamically represents the data from these virtual columns. You can utilize Charts or perhaps a Detail view style with VC that displays progress bars, showing a visual comparison between targets and actual sales.

Now regarding your specific questions on how to achieve certain calculations in AppSheet, here's some guidance:

- Calculating the Target for Each Product:
- This will involve creating VCs that essentially perform "lookups" on the related Target Details for each target, multiplying each related quantity by the product reference. Here's a formula structure you might employ, adapted into an AppSheet-friendly approach:

SUM([Related Target Details][Quantity])

- Counting the Number of Each Product Sold:
- If Order Details are created one by one for each product sold, your Orders table can simply use the COUNT formula on the [Related Order Details] list to tally the quantity sold for each product.

_____________________________________________________________________________________

If you're new to AppSheet and looking for how to handle more complex interactions, perhaps like a shopping cart mechanism, I recommend checking out the "Shopping Cart in AppSheet | BUILD VIDEO" series. It provides valuable insights and can be found under the 'Resources & Tools' section. Here's the link to part one of that series on YouTube.

For a step-by-step walkthrough creating a shopping cart style functionality and handling related tables in a productive manner, you might find the video "Customer-Specific Products || Shopping Cart [BUILD] - Part-4" helpful. It dives into the concept of "blueprint tables," showing how to manage complex table interactions efficiently. You can watch it here.

Lastly, consider watching "Tracking Inventory Through AppSheet Automation || Let The Bot Do All The Work!!!" to understand better how AppSheet's automation can be leveraged to connect and create records between related tables easily. This could be instrumental in automating your order and target tracking processes. Check out that video as well.

Remember to approach this step-by-step, ensuring that each part of your app functions correctly before moving on to the next. This will help you spot any potential issues early and develop a more reliable application. If you're still having difficulties or need more personalized guidance, consider upgrading to the Apprentice support tier in the Answer Portal for a monthly one-on-one meeting where we can delve deeper into your app's specific needs.

___________________________________________________________________________________________
You can chat with the lite version of Appster if you have a ChatGPT Plus subscription.
You can gain access to the full version by signing up at www.MultiTechVisions.com/answers
 - Main Menu > Support Tiers