Making hard copies of computed columns

Ask for Help
1

I have a table that looks like this;

image
image1449Ă—265 84.7 KB

Is there a way for me to write these computed columns to another table that stores by user/date for making a chart/graph? I have several actions I could add a set column/new record to, but I am not sure how to point to a specific record and determine if it exists, and if it exists, how to update it rather than write a whole new record. In my mind, I have a method that should work using relations, but not really sure how to set that up.

2

So do you want to put the data from these multiple rows into one row in another table?

3

Yes, one row would be best. As far as I know, I can select the proper column in the table for each graph of things I want to show (calories per day, weight per day, etc.)

4

Which specific columns do you want to write to another table? Sum, Last Weight and Last Weight Taken?

What does your data structure look like in the destination table?

5

Not really sure how to structure it is the thing. I know i’m going to need some relations but… I basically want the computed column of the total calories and weight for each day. Later I may add a column for the water consumption and et cetera. Start small and go big is what I always say. What do you always say?

6

So it’s the “Sum” column?

7

Yes

8

What I would do, for future scaling purposes, is to:

  • Create a template column in the table where you have the screenshot, something like:

“Key”: Value

image
image1344Ă—468 32.2 KB

  • In your Users table, create a joined list column that will join the key-value pairs, separated by a comma and a newline character.

image
image733Ă—436 54.1 KB

  • Add a template column to convert it to the right JSON format.

image

Now you have something like this.

{
"Weight": 0,
"Calories": 75,
"Beer/Soda": 70,
"Coffee": 60,
"Activity": 80,
"Water": 90
}

And can submit it to a new table as a single cell.

Then, you can use the Query JSON column to query the parts you need.

image
image695Ă—414 22.9 KB

That way, you don’t need to care about the row orders changing, or much work if you add a new row in the Actions table. Just need a new query JSON column in the destination table to query that new row’s key.

3 Likes
9

I appreciate the sleekness of this. But I don’t know j s. And so if I change anything in the future it’s sure to break. I was kind of hoping for a glide solution. Also, this particular app is a free app and i’m not sure j s is supported?

10

What @ThinhDinh offered up doesn’t involve any JavaScript or coding at all. Just a bit of JSON manipulation. Even if there was JavaScript involved, that wouldn’t be a problem because it’s available on all plans.

2 Likes
11

OK, thank you for the clarification. Obviously, I don’t know much about either. I will give it a whirl.

12

Thanks again @ThinhDinh. I was/am intimidated by this because I’ve never done it. @Darren_Murphy said it is available on the Free Plan. I will give it a whirl. That’s how you learn, right?

1 Like
13

Yes, the Query JSON and JSON Object columns are available on all plans.
Query JSON uses JSONata. If you’re not familiar with JSON, that could be a good place to start. They have a sandbox with lots of sample data to play around with, as well as all the documentation.

1 Like
14

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.