Hello DB maverics!
I’m creating a Diet Tracker and would like to serve the users some insights, such as which weekday is the leanest/fattest. I know how to do this in SQL :
#SQL Pseudo code
select AVG (totalCal), DATEPART(weekday,foodDate) …
GROUP BY DATEPART(weekday, foodDate)
Is there any way of doing this is in Bubble?
I think what you need is the GroupBy options that show after a Do Search or List operation.
That is perfect for finding the average for a WEEK. I want to find the average for a WEEKDAY.
Don’t’ suppose you have a small demo app we could hop into to play around with do you? Would be easier to figure out and play with.
Sure. No clue how to do that. New to bubbles.
Ah ok, no probs. So in your editor, under the settings, General tab you’ll see this…
The drop down has an entry for Everyone can view/edit. If you change that then give us the URL we can take a look. If you’re not comfortable putting the URL in this post for all to see, just send me a message - or you could clone your app and share the cloned version.
I’ve cloned project. Check out the calendar page, where I try to package different insights based on the last month calorie intake.
Project link: https://bubble.io/page?type=page&name=index&id=morelessclone&tab=tabs-1
What I am trying to do:
I’ve added some additional data to the bottom of your Calendar page which I hope will help. It’s not quite finished and I don’t think I’ve managed to do exactly what you wanted, I was more playing with the data and trying to manipulate it somewhat to get values out in different formats.
I’m running out of time this morning, but I hope something in there will help anyway. I may come back to it later.
The more I look it the more my brain starts to hurt!
So, I think I have something working for you.
On page load I create some DOTWsum records, which is the average of a particular day for the months. It uses an advanced filter by extracting the day (0= sunday, 1 = monday etc) and does this a few times (expand it to 7 yourself).
Then the page shows the highest DOTWsums total and day (convert it to Day of the week yourself).
On page load, if you delete all DOTWsums for this user it will clear it down, and you then need to add the user as a constraint on the search.
Hope that makes sense. It is a bit of faff, but using a temporary table should work ok.
Well done pork1977gm and NigelG. What a team!
The filter method, that you both used was new to me. Thanks for showing me the way.
NigelG: You do the setup in the page loader. What if the values change during the page is used (tapping on the dates in the calendar displays an edit pop-up)?
Great stuff guys. I finish it up and we can all enjoy the end product.