Import values from other tables

EPP_Recycling
Participant III

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!!

0 10 316
10 REPLIES 10

LeventK
Participant V

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.

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

TyAlevizos
Participant V

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-321...

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
Participant III

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
Participant III

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โ€ฆ

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]
)

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

Top Labels in this Space