How to write an expression to show a 'complement' table (i.e. all values not in another table for a certain field)?
Hi.
I want to have 2 straight tables:
Table 1:
Contains 1 field (Product_ID), and 1 expression based on Sales (e.g. sum(Sales)). My expression is actually much more complicated than this, though. Based on my selections of Product_ID, certain records of Product_ID (and corresponding Sales expression value) appear in the table (dependent on my expression).
Now I want to create a second table so that it holds all other records not shown in Table 1. Is there a way to do this? i.e. Is there a way to write an expression for the second table based solely on what is shown in Table 1 in a field, independent of user selections
i.e. user selections --> drives Table1 output --> then this drives Table2.
For example:
All possible Product_ID values = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10}
I select certain Product_ID values.
Based on my Table1 expression, Table1 shows records for Product_ID values = { 2, 3, 5, 6, 7, 10 }.
(Note, my selection is not neccesarily { 2, 3, 5, 6, 7, 10 })
And I want Table2 to show records for Product_ID = { 1, 4, 8, 9 }.
Basing Table2 on an expression driven by user selections but having an opposite effect to Table1 does not work in the case of my expressions - Therefore, I need the expression in Table2 to be essentially independent of user selections and to be like "Show records for Product_ID = Product_ID's not in Table 1).
