Import values from other tables

[EPP_Recycling]

Hello AppSheet community,

I am working on an app that keep track of how much users recycle. There are four different tables, each for a category of trash:

1. Plastic Bin
2. Paper Bin
3. Glass Bin
4. Aluminum Bin

Within each table, there is a column that calculates the total quantity of each bin (for example in the Plastic Bin table that column is “Plastic Quantity” and in the Paper Bin table that column is "Paper Quantity’) There is another table that I want to use that has each of these values and calculates the “Total Amount Recycled” by adding them all together. I want to do this so that I can create a chart that will display all these values together to the user in another view.

Basically, I do not know how to basically import these “quantity” from each bin into this one table. I have tried using reference and REF_ROWS(), but they do not return number values that I can use for a chart. I’m at a loss of what to do.

Please, I would appreciate any help available. Thank you!!

[LeventK (Levent Kulacoglu)]

Basically you can create a gSheet with a QUERY formula containing those values and then import this sheet as a readonly table to your app. Then you can use those columns for in a Chart UX.

[TyAlevizos]

here’s a tiny sample app that sums things up inside the app using a single-row special “globals” table:

https://www.appsheet.com/samples/Built-from-your-app-spec-?appGuidString=8a000953-a917-47c8-9bf0-32146d351366

[EPP_Recycling]

Sorry, could you clarify bit more please about the values and how to make a QUERY formula?

[EPP_Recycling]

I’ve tried to replicate how the app brings in the values from the other tables, but it is not working. AppSheet only says “SUM function is used incorrectly”

[EPP_Recycling]

After some playing around, I finally got the formula to work:

SUM(Plastic Bin[How Many?])

Thank you so much for your help! I have another problem, however.

Since this app is being used by many users, I also want to make sure that my formula for “Plastic Quantity” is only adding up the plastic inputs of the current user. How can I make sure that this formula tells the app to only calculate the column values that match the current user’s email address (I have an email address column)?

[Marc_Dillon]

SELECT()

[LeventK (Levent Kulacoglu)]

@EPP_Recycling

After some playing around, I finally got the formula to work:

SUM(Plastic Bin[How Many?])

Thank you so much for your help! I have another problem, however.

Since this app is being used by many users, I also want to make sure that my formula for “Plastic Quantity” is only adding up the plastic inputs of the current user. How can I make sure that this formula tells the app to only calculate the column values that match the current user’s email address (I have an email address column)?

SUM(
	SELECT(
		Plastic Bin[How Many?],
		USEREMAIL() = [EmailColumn]
	)
)

[EPP_Recycling]

Thank you @LeventK ! I have integrated this formula into my app. Although it says that it is correct, for some reason it is not returning any results…

[LeventK (Levent Kulacoglu)]

The easiest solution is to apply a Security Filter to each one of the 4 tables as per email so that your initial expression of SUM(Plastic Bin[How Many?]) will automatically sum the filtered values as per user.

IF(
    "your_own_email_address" = USEREMAIL(),
    TRUE,
    USEREMAIL() = [EmailColumnName]
)

[EPP_Recycling]

@LeventK thank you for your suggestion! Is there another way to accomplish the same thing without a security filter?