Creating a query to link three tables
Most times, we need to extract data from multiple tables; so, it's very important to understand how to join multiple tables. In this recipe, we will see how to join three tables, set up the method to calculate totals, and add filters to limit the result.
How to do it...
Create a new query object with Object Designer.
In Query Designer, choose
DataItemfrom the drop-down list in theTypecolumn.Select
Currencyas aData Sourcevalue from the table list.In next row, select
ColumnasTypeandCodeasData Source. Keep the methodNoneas it is.In the next row, select
DataItemin theTypecolumn andSales Invoice Headerin theData Sourcecolumn.In the next row, select
DataItemin theTypecolumn andSales Invoice Linein theData Sourcecolumn.Add one more row with
ColumnasTypeandAmountasData Source. For this row, changeMethod TypetoTotalsandMethodtoSum.Maintain the indentation of all rows as shown in the following screenshot:
To set up a relation...
