Formulars only in Glide not in GSheet, need a sumif to another tab?

App: https://go.glideapps.com/app/wHBzBTnhNJeVh0uKYS7G

I have one tab to which you can add any food you’ve eaten on a given day.

  1. I can’t make the vlookups in Google Sheet because when I’d add a new item, it would be added below and the formular would not apply. https://prnt.sc/r6wcj4

So I made a relation + lookup in Glide. Now you can add food and how much and it will calculate how many calories you’ve eaten.

=> However in another tab I need the sum of consumed calories in a day. https://prnt.sc/r6wbjf
a) any way to make the data from Glide formulars also appear in GSheets?
b) any way to make a sumif date=today then sum calories.

  1. is there an option to add search functionality to the choice item? Going through hundreds of foods to find the ingredient you ate today is a bit inconvenient :slight_smile:

Thank you so much, this is (hopefully) the last piece!

To my knowledge, there’s no way to create GSheet columns from original Glide Data. If you need to have all the data in your sheet, the VLOOKUP can still work if you first sort the data within the formula. Something like:
=VLOOKUP(__,Sort(_____),__ ,False)

However, to do a sum by date within Glide is a bit tricky as the rollup tool doesn’t allow you to reference a second column. So, do this:

  1. In a second sheet, create a column of unique dates ( =unique(LogSheet!B2:B))
    Screen Shot 2020-02-24 at 8.47.07 AM
  2. In Glide, go to that sheet and create a multiple relation between the unique dates sheet and the log sheet:
  3. Create a rollup column that summarizes the values of Relation > Calories by calculating the sum.
  4. You now have a tab that will sum by date dynamically. Build out this tab in your app! Bonus: in the details view of the inline list, create another inline list component with that relation column to get the items that constituted that total:
1 Like

Ok I am officially mindblown. This is some glide/gsheet magic. One thing though: I don’t need it in a list like you have, in this case I’d only need the current day!

  • sum by day => accomplished!
  • give me the number for today => ???

Any solutions for this?

Yep!

You can run a =Today() formula in a new column in the “unique dates” sheet to check if date is today. If so, spit back true.
Screen Shot 2020-02-24 at 9.38.30 AM

Back in the app, filter by this “IsToday” column:

Or you could do if it’s today, then “Today” (or anything) else blank and then filter by “is not empty”. Personal preference.

Now, the only limitation is that Today() and Now() formulas don’t auto update the app, so tomorrow it will still think it’s “today” and show the totals from the previous day until a new form entry is submitted to create the new date and there by update the app. If you have a Pro app, then this is a non issue as you can have the Glide app refresh “every few minutes in the background”.

@JackVaughan …another situation where a “current date/time” column would come in handy :wink:

1 Like

Hm, maybe i am missing sth. the sum happens through the rollup in glide. so yes, could sorta blend out all the other days which are not today. however, I need the specific value (sum of calories) from that sheet/tab in another tab.

so blending the others out to make that one appear is unfortunately not helping. out of all of them, would need to pick the one for today and get that into another tab/sheet. :thinking:

Ok. So, are you simply wanting to take the “sum of today” and use it as value in some sort of “today dashboard”? Where is this value going and for what purpose?

Exactly. Not sure if you saw the previous link. posting again here https://prnt.sc/r6xqll

in Food log you can add all your foods
in Home you should see the summary of how many cal consumed

Ok…I think all you have to do is create a relation to the Today() column (seen here as "new Column) in your dashboard by creating a column (either in the spreadsheet or as a template column) where the value is persistently “true” (seen here as “TodayTrue”). So, the relation will only ever refer to “today’s data”.

Then do a lookup of the sum calories (New Column 0):

2 Likes

Oh god, I can feel it, so close! Thank you so much @Robert_Petitto already!

Ok, TodayTrue works! Just noticed that a step earlier sth doesn’t work. Specifically step 3 which you mentioned in your first answer.

For me the rollup doesn’t show the option to sum up the calories from the Items eaten per day like it did for a/b/c and d/e/f for you?

(even tried to lookup the calories and sum those up. Rollup doesn’t recognize that field either https://prnt.sc/r73qq7 :/)

In that list that pops up when you go to select a sheet/column in the Rollup, scroll down and see if that relation you created is at the bottom.

1 Like

:unicorn:
And you sir just won the award for damn awesome human being!

Should you ever have any nutrition related questions, let me know. I am actually a coach on the side.

(now off to getting to know Parabola to source some ingredients and meals to feed into the app)

1 Like

I’m honored! I’ll be sure to take you up on your offer! Best of luck and don’t hesitate to reach out if you need further assistance.

2 Likes

Happy to:)

One more thing though came up (sorry). Since I am doing more math now in Glide vs Sheets, any way to round results? Currently getting results like “433.23423423kcal burnt” which obviously doesn’t look nice.

Not that I’m aware. It has been mentioned in previous posts:

Alrighty then 🤷
Thanks anyway Robert, again!

No problem! I, too, would love to see a round() formula in the Math column. Add a feature request!