Hi all. I've got two tables where one refers to the other. One table is a product record table, and the other is an invoice table that has a reference to the product ID. I have some invoices in my invoice table that don't refer to valid product IDs and I want to pull them out. How can I create a derived field on the invoice to say whether or not it matches a valid product ID? I was thinking to use a mapping table but I can't think of the right way to do it. Thanks!
If the tables are linked and the Product ID from the Invoice table does not match a valid Product ID from the Product table, then any Product fields associated with that invoice number will be Null.
Create a table with Invoice Number and Product ID as the dimensions. Then pick a field from the Product table (anything other than Product ID should do). Then create an expression like:
If (IsNull(ProductField), 1, 0)
If Suppress Zero Values is checked in the properties, then only those invoices with unmatched Product IDs will show up.
