Noob Help - Recreating a calculator based on vlookup type reference

General General Discussion
1

Hello,

I’m totally new to appsheets and have basically no coding experience. I have a spreadsheet (google sheet) with 2 columns of data. One column (column A) with the name of an exercise and another column (Cloumn B) with a decimal value indicating MET value of the exercise. I want to build an app with the following fields:

Weight: User input number value
Exercise: A dropdown field allowing a single selection based on values from column A
METs: A “calculated” decimal value corresponding to the value of column B for the selected exercise from previous field. This would be a vlookup formula in a spreadsheet.
Minutes Performed: User input number value
Calories burned: A calculated field based on the following formula: Weight * METs * Minutes Performed * .008 = Calories Burned

I don’t understand how to make Appsheets create the dropdown field then populate the METs based on the second column. I also don’t understand how to do the user input fields. I read about virtual columns, but I’m not sure if that’s what I really need for the user input fields. Eventually, I think I would want it to create a log of burned calories for each user, but I’m ok with figuring that out later.

Any help anybody could give would be appreciated.

2

Hey Moe

First off: sorry for the late reply, I’ve been very nose to the grind and not been paying attention to this community. Apologies. :pray:

I gave your question to Appster, here is what it had to say:

Welcome to AppSheet! It’s great that you’re diving into app creation with your data. Based on your description, you want to create an app where users input their weight, choose an exercise from a dropdown, and have the associated MET value populate automatically to calculate the calories burned.

Let’s break down the steps to build this app:

Overview of the Solution:

  1. Set up your Data Table:

    • You’ll have your Google Sheet with columns for Exercise (names) and MET values. This will serve as your data source.

  2. Create the Dropdown for Exercise Selection:

    • Use the exercise names from your data table to populate a dropdown.

  3. Set fields for User Input and Calculated Values:

    • Create input fields for Weight and Minutes Performed.
    • Automatically populate METs based on the selected exercise.
    • Calculate Calories Burned using a virtual column.

Steps to Implement:

  1. Connect Your Google Sheet:

    • In AppSheet, connect your Google Sheet as your data source. This will set up your basic app structure.

  2. Configure the Exercise Dropdown:

    • Go to your AppSheet editor and select the table connected to the sheet.
    • Click on the “Data” tab, and then navigate to the column settings.
    • For the column intended for Exercise selection, set its type to “Enum” and use the “Valid If” constraint to fetch distinct exercise names from your table.
      Valid If: TableName[Exercise]  // Replace 'TableName' with the actual name of your table.

  3. User Input Fields:

    • Ensure columns for Weight and Minutes Performed are set to “Number” type, allowing user input.

  4. Calculate Calories Burned:

    • Create another Column for Calories Burned based on the provided formula:
    • Use a dereference formula to automatically find the MET value based on the selected exercise, and do the math.
      Formula: [Weight] * [Exercise].[MET] * [Minutes Performed] * 0.008

Additional Tips:

  • Use Virtual Columns for Calculations: Virtual Columns are ideal for calculations like this as they dynamically update based on the inputs and selections.
  • Logging Burned Calories: To track calorie burn over time, plan to create a new table that logs each exercise session per user. This can be refined once the basic app setup is complete.

Feel free to check out the “Creating specific values for a dropdown list | AppSheet Explained” video under the Resources & Tools section for a detailed walkthrough on dropdowns. If you need any further assistance, reach out via the Answer Portal or let me know if you have more questions!

Not bad!

Here you can see the METs column is removed altogether, as it’s just containing a static value from another table (right?) - so no need to pull it into this other one when recording the value.

  • This is an example of Appster bringing data normalization principles into focus, some of the higher principles I’ve encoded into it’s instruction set.

Did you get something figured out @moe0303 ?

1 Like