In this tutorial you will learn how to use aggregation formulas and how to use one app's data in another one.
You will create two apps: one for adding new invoices and one for reporting them.
1.Invoice scheme
1.a
Create a new app. It will be the app for adding new invoices. Add a spreadsheet widget to it.
1.b
Fill one of it's columns with data that you would want to get about every invoice: department, product, value, date and subject.
1.c
Set all B column cells as editable. Cells type set as:
-dropdown for B1
-text for B2 and B5
-date for B4
-currency for B3
1.d
Next add another spreadsheet section. In one of it's columns put department names that will be then visible in dropdown.
Go back to cell B1 in first spreadsheet and in 'Elements' field put department names from the second spreadsheet:
1.e
Hide the section with department names. You will do it by putting: =false in 'visibility' field.
2.Adding new invoices
2.a
If we want the app to save created invoices we will have to create a workflow for it. Add 'workflow actions' widget. Then go to workflow tab.
2.b
Add one state - 'saved' and one action - 'save'.
3.Last changes
3.a
To be able to use this app's information in report app you need to add aliases to cells B1-B5 - simple names that will tell what kind of information is in the cell. An example of an alias:
3.b
Finally, add yourself as the app creator.
4.Invoice raport
Now you will create a raport app.
4.a
Add a table widget to it.
4.b
Next, go to 'Data' tab, find your first app there and bind it with the table.
4.c
Name the table columns the same as the fields in the invoice scheme.
4.d
Then add invoice aliases from the first app to the columns. You can do it by dragging them to the table or by putting the names manually.
4.e
This table will gather all of the added invoices information. To give it the right order click on 'Date' column and choose sort descending.
5.Aggregation formula
5.a
We will use an aggregation formula to be able to know how much does every department spend. You will need a new table section with two columns.
5.b
Column A name 'department' and column B 'invoices summed value'.
5.c
As you previously did you need to bind the new table with the first app. Now use aliases which you created in the invoice-creating app: in column A put: =department and in column B: =sum(value). The second one is an aggregation formula that sums by groups, in our case - by departments.
5.d
Change B column type to 'currency'.
5.e
Check if your app works by clicking 'preview'.
5.f
Add yourself as a creator.
6.Invoices
6.a
Now run the first app a couple of times, creating different invoices in different departments. In one run you will add only one invoice. Your app should look similarly to this:
6.b
After you've created a couple invoices run the report app. It should now show data about all of the added invoices and about value of summed invoices from different departments.
An example of a raport:
0 Comments