Section 8 (Lesson 3/10): In this lesson, we’re going to work with chart data. We’ll start by preparing our database with a new data type called “transactions.” Then we’ll upload a CSV file from our resources folder with some dummy transactional data. Finally, we’ll connect the data to our admin chart to display it.

You’ll also learn about:

Resources folder: ➡ https://e6387a14ba6d0bf3e823090f8d959...

Bubble editor: ➡ https://bubble.io/page?type=page&name...

So the first thing we're going to do is set up our new data type called "Transaction". We'll add three fields to that. Then we'll upload our CSV file in the resources folder, and then we'll work on our display, basically our chart element, and make sure everything is rendering as it should. So let's dive in!

So over on the Data tab, please create a new type called "Transaction". With every app, you'll probably find that there is a monetization strategy. So basically we're creating this Transaction data type, we're importing our CSV data to basically mimic what transaction data could look like. A very simplified version.

Let's add a new field. Call the first one "Amount". It will be a number field. Now we need a Company field. For instance, which company is paying for this subscription or this particular payment. Of course, we need to link that to a Company type, so the field type can be found under Data Types --> Company, and then we'll create.

And we do have a Created Date. So remember I talked about in the previous section about value over time. That's what we plot on the chart, value over time! And in order for this chart to plot value over time, we're going to just create a dummy field for now called "Date" because we need varied dates. If we had to upload all of this data, the Created Date would just be the same, so that's why we are creating another field just with some dummy data with a range of different dates.

So we'll call this next field "Date". So the Date that the transaction took place, and that is of Basic Type "Date". Okay, but naturally, every time Bubble writes something to the database, creates a new row, it will include the Created Date step, so that's what I use in my apps. We're just putting in this field because we need a range of dates for the chart to render correctly.

Let's head over to the App data sub-tab now and let's click "Upload". Head over to your Resources folder, look for the Data folder, and within that, you have "Workplace Data - Transactions.csv". Okay, uploading. I'm going to select the type of data to be a Transaction, and then I'm going to map fields. And it's done it all for us.

For the Company, the field to match will just be the Company Name. Okay, let's validate the data now. "Your data set is ready for upload," then click "Upload data". Fantastic, let's close that.

And here you can see we have Amounts of varying Amounts and we have Companies, we have... Orbit has made multiple Transactions. That's basically what we're trying to do. And here we have our varying Dates. Can you see that our Created Dates are all the same? That's why we needed this Date field.

Now we need to copy across from the company page, we're going to open up Layers and Group Container and look for our Group overview. Open that up, and this is the one we want. So with that highlighted, I'm going to choose "Edit," "Copy". And then I'm going to head over to my new admin page. Highlight Group Container because we want it inside the Group Container. Here are the other ones from what we copied across and we will delete those shortly. So Group Container is highlighted, "Edit", "Paste". Okay, bring up the Property Editor by double-clicking in Group overview. And on the Conditional tab, it will match.

Okay, now the top-most data, let's jump into the demo app to see what we need here. So Monthly revenue, Monthly signups, and Monthly jobs. And we're actually not configuring anything here, this is just for display purposes. I can see we have a "Search for Jobs" field, maybe we'll do that one, but more importantly it's this Annual revenue chart that I'm interested in.

Okay, back to our app. I'm going to click on Group 7 days and just copy and paste that. What we need to actually do is under Group Analytics, we bring up the Property Editor on the Layout tab. Let's just remove the max width here, so it spans edge to edge. We need a bit more space. And then in Text Total applications, we can change it to "Monthly revenue" in the Appearance tab so that it's now Text Monthly revenue. Let's add in a placeholder. Going to clear this expression. Just type in a large figure in there.

And while we're at it, let's just name this all correctly, so Group Total becomes Group Monthly revenue. Group 7 days becomes Group Monthly signups. Let's change the label to "Monthly signups" as well. And it's an arbitrary number, that will do. We're going to clear out what we have in there. Let's say 1,024, business is booming. The last one, we're going to rename the Group to Group Monthly jobs. The label becomes "Monthly jobs" and we're going to clear out the expression in the Appearance tab, just type in an arbitrary number.

Okay, and this is very basic in terms of analytics. In what I've built in dashboards in the past is a dropdown with day, week, month, annual, you can get really granular to help build out analytics within dashboards.

Now over to the chart. The first thing I want to do is grab some text and get it inside this Group chart and then call it "Annual revenue". Okay, on the Layout tab, let's just remove the min width and the min height. And we need to add some gap spacing inside the Group chart so let's do that and just add 20 px. Nice and neat.

So we've got some data to play with, and currently we're searching for Applications and we're grouping it by Creation Date. Very well. Instead, we're going to search for Transactions. Let's have a look at the grouping, because what we're trying to do here is group per month, so how many transactions happened in this month and what is the sum total of these transactions.

So :group by, and we're currently... Creation Date, we're going to change that to Date. Remember what I said? Creation Date is the more natural one, but because we've uploaded data, the Creation Date is all of the same date, today's date. So we're going to choose this Date field instead.

Type of grouping is this time going to be a month. Choose interval of every single month. And where do we need to start? Well, the first time a transaction was created. We could also set it to, let's look back at the previous year. Let's do a search for Transactions. I'm going to remove that. I'm going to click on close, and then we do want the first item's Date instead of first item's Creation Date.

Okay, but the start date is another value that could be dynamic. So if you want your users to be able to, or as an admin person, to be able to get more granular, you can have a dropdown, link a dropdown's value dynamically to this expression to say, give me the last week, month, year, up to you. The last nine months between two dates, whatever you'd like!

And here we're looking for the sum of the Amounts. Each month, what is the sum of all of the transactions that occurred? So Aggregation 1 is the Sum and the Field to calculate on is the Amount. And let's close.

So the value expression Bubble has already changed to the Current point's Sum of Amount. Perfect! Looks like the label expression is correct with our Date instead of the current date and we formatted that.

Now, the next thing I want us to do is actually scroll down slightly and look for "Customize tooltip number formatting". Check that box, and what we're going to do is customize when we browse over these little tooltips, Bubble is going to actually tell us the sum amount. I wanted to format it as a Currency. So dropdown, choose Currency, large numbers so I'm not going to choose a decimal place. We are going to choose a thousand separator, and we're going to choose US Dollars. And I think we might actually be ready to view this.

One other thing I wanted to do quickly is just on the Workflow tab, we've got the "Page is loaded" event. I just wanted to delete that altogether, please. And then click on the "Page is loaded and Current User is logged out" event. We've got it's going to page index.

I want to add a new one, so I'm going to "Click here to add an event". This is going to be called "Page is loaded" and I'm going to change this card to purple. And on the conditional expression, I'm going to say only when Current user's User type is not.. and then what we need to create now is an "Admin" option. So if they aren't an Admin, then Navigation --> Go to page index, so only the Admin user can access this page.

Okay, so on the Data tab, let's head over to the Option sets, please. Go to User Type and just type in "Admin". Create. Now we can go back to the workflow and finish this off. So if the Current User's User type is not Admin, therefore it is Candidate, Company, or someone else without an account, go to page index.

Before we test that, we do need to update our own user account as an Admin, so I'm going to click on the Data tab, head over to App data, go find our users, and find your own account. Here is mine here, the first one created. I'm going to add my User Type of Admin, and then I'm going to click on "Save".

Okay, so let's now run as your own user account! I've just gone ahead and removed the debugger bar for now. Here is my image top right-hand corner, and if I click on Overview, here we have some data! You can see the formatting with the numbers, these are summed up. And it looks like we just need to fix up the labels for our months.

So let's head over to the Design tab. Let's just do this one thing then we'll take a break. So the Label expression says Current point's Date:formatted as... and we're just looking for the month here, okay? So custom format will be "mmmmm" so it's the full month, April. Okay, let's preview, refresh the page and look at that! Very, very nice. We could have also chosen the first three letters as well, but this looks pretty great.

So let's evaluate the data here. So in March, I've got $140. I'm going to head over to the Data tab, look at Transactions. And in March, we have $140. Let's just make sure the summing is working. So for June, we have one, two rows in the database, one of $120 and $80. So we should have $200 of revenue in June. Let's head back. Let's go find June, and then we have $200.

Okay, fantastic! So pretty much done with that. I'm not going to make the Monthly jobs and Monthly signups dynamic, we did that in the previous section. Let's move on to just getting our Users sorted and Jobs because something else we need to do is look at our privacy rule from the perspective of an Admin User because we need to be able to access everything, and currently, we can't. So, I'll see you in the next lesson.