How to use fields from references in reports?

Get Help
1

Hi

I have three databases ‘SaleItems’, ‘Sales’ and ‘Products’. A Sale has a date and can have many SaleItems and a SaleItem consists of a quantity and a reference to a product.

Now, for example I want to create a report with all products sold in 2022 and the amount of sales for each.

I started a report with SaleItems and thought I could filter by [SaleItem].[Sale].[Date] and group by [SaleItem].[Product].[Name]. But I seem to be unable to do it like that, the only way I can get it working is by first creating a new field in SaleItems that looks up [Sale].[Date] and [Product].[Name] and then only use SaleItem in the report.

Is this how I am supposed to do it or is there a better way? It seems it should not be necessary to “pollute” the SaleItem database with all the fields from references that I may possible need in any of my reports just for a simple join?

Thanks!

2

Adding the [Sale].[Date] lookup is probably necessary, but not the [Product].[Name]
This should already be accessible in a report view based on SaleItems

3

Thanks for confirming this is the correct way.

I understand why Name should not be needed, it was a bad example, in practice I will need another lookup from the Product table (a SKU).

4

Don’t forget that on entity view you can hide fields you don’t really need to see/use :slightly_smiling_face: