Purchase app, date totals difficulty

Ask for Help
1

Hello!
I am trying to make for my brother a small app that will allow him to enter some purchases. He has a water delivery startup, so he has to go fill 5 gallon bottles with different types of water everyday, and buy a few other products.

So they way I have done the submit form is;

  1. Select Date
  2. Select Product
  3. Select Quantity
    Submit

This adds the info to the googlesheet just fine!

The problem I have is displaying all this info. Maybe I have the logic wrong, but for one date, lets say 6-Dec-2021 there are 3 entries, so thats 3 rows of info, 3 totals, and I want one grand total for that day.

What would be useful to show in the home screen is;
Monthly total of purchases to date
A bar graph with a historic view of previous monthly totals

This is some sample data that I have submitted via the app;

image

I think the problem I am having is that on one date, there are several entries, and I need that daily total.

Any guidance is much appreciated, thanks!

2
  • First, in your glide data editor, you may need to create a template to “lock in” the formatting of the data. You may not need to, but if your date column contains an underlying time along with the date, that sometimes things won’t work. If you are recording date only and not the time, then don’t worry about the template column. Just remember it if you need it.
  • Next create a multiple Relation column that links the date column back to itself. This should give you a relation with three matches on December 6th for example.
  • Next create a Rollup column that gets a sum of the total amount through the relation. Each row should now show you a total for that day.
  • Now the tricky part is that you will have a grand total for each day, but you will have duplicates for those days that have multiple entries. Ideally, what what we need to do is get the grand total on only the first record for that day. So, for December 6th, only the first record has a grand total and the other two records have a blank for the total.
  • To take care of this, I would recommend first adding a Row ID column.
  • Once you have the Row ID column, then create another single Relation column that links the date back to itself. It will be like the relation we created above, but a single relation instead of a multiple relation.
  • Then create a Lookup column that gets the Row ID from that single Relation.
  • Now just create an IF column that compares the Row ID for the row to the Row ID in the Lookup column. If they match, then return the Grand Total. Else, return blank.

Now you will have a Grand Total for each day that’s only listed once per day. This should be useful when you build your charts. You can use similar methods using a Math column to extract only the month or year from the date, build your relations using the month/year and get a grand total for each month/year.

6 Likes