Uniconta Dasboard can link tables via "Data Sources". Calculated fields cannot be used before Joins and Unions. They can only be created and used in the overall table.
| Join |
Joins are used to gather data from different tables into a single table. This is done by "linking" two or more database tables via data. For example, if the Date field is in both tables, they can be linked and used as a key. If the Employee initials are in both tables, they may be linked and used as a key. These "links" between tables are called "joins". Once the join is made, fields from both tables can now be used in the same table, pivot table or graph etc.
NOTE: Do not include fields with the sub-designation "ref". If these fields are included in a separate query, the speed will be greatly reduced. |
| Union |
Union. Union combines data from two or more tables into one Data Source that includes all rows belonging to all queries in Union. Very suitable if the two tables to be "joined" have the same table structure. In a Union, duplicates are removed.
E.g.
Table 1
| Init |
Date and time |
Timer |
| PJ |
02-03-2019 |
4 |
Table 2
| Init |
Date and time |
Timer |
Other |
| KK |
05-03-2020 |
8 |
|
Overall table (Union)
| Init |
Date and time |
Timer |
| PJ |
02-03-2019 |
4 |
| KK |
05-03-2020 |
8 |
|
| Union All |
As Union except that duplicates are not removed. |
In order to create Join or Unions, a minimum of two tables must be selected. Read more
here
The tables are shown in data sources.
Create a "Join" or "Union" Data Source:
Select Data Source and press [New Data Source]
Query Builder opens
Join
Select "Join" (Default)
Select the first table by dragging the table from the left side into the right side of the screen. Then drag the second table.
Now make Joins between the two tables. Here we have chosen to join the [Debtor] field from both tables.
After pressing OK, you can now choose which fields to include in the overall table.
The complete table now appears under Data source and can be used like any other table.
Union
Select "Union or Union All"
Here are almost similar tables a lot. E.g. Budget vs. Realized.
Select the first table by dragging the table from the left side into the right side of the screen. Then drag the second table.
Press [Ok]
The complete table now appears under Data source and can be used like any other table.
ATTENTION: Joins followed by Unions will often not work.
NOTE: Calculated fields cannot be used before Joins and Unions.
Joins and Unions across accounts and using data from multiple accounts.
Read more
here