I have two tables. Both have Account IDs. A user can make selections within either table. What I need to do is display a list of Account IDs in the first table that are selected but not within the selection in the second table.
Eg.
Table A Table B
Account ID Account ID 1234 1234 2345 3456 3456 4567 4567 5678
My resulting display table should then only contain 2345.
Of course this is easy within a load but I need this to be dynamic within a selections that a user makes. It seems like a simple thing but I just can't figure out how to get a result, maybe I am just missing the obvious.
The background for this is that users can select one quarter then a second quarter and would like to then have one table displaying Accounts that have been cleared from the previous quarter to this new quarter, and another table displaying new Accounts that just popped up in the new quarter. I cannot do this within a load because I don't know which quarters the user will choose.
I was actually hoping for a solution with the two tables joined by a copy of the Account Id itself, but I didn't think it was possible to do this kind of thing when they are joined due to the selections cancelling out the differences anyway. If there is a solution where they are joined it is much better because my other table that I have created runs a lot faster when the tables are joined.
I will have to post a sample app later when I have a bit more spare time.
I have attached a sample app which covers part of what I am doing.
There are ~30,000 rows of test data there (mostly random generated).
The fields that are required for comparison are copied to another table. The fields that are just for filtering are just left in the first table.
The user tables compare movement of customers from one band to another between the two selected periods. One lists the total value of the second selected band and which band that value has moved from. This isn't really useful information in my opinion so I convinced the users to make the second table. The second table contains the difference after moving from one band to the next.
So these tables are for Accounts that are in both periods, but the users then want a list of Accounts that have been cleared (so are in the first selected period but not the second) and a list of new Accounts (in the second period but not the first).
Thanks Vlad, that is great. I am still trying to understand the expression, but do you know why the Customer Group filter makes the second list box (for the To Month) list all Account Ids when selected? I assume it has something to do with the filter being applied to the first table and causing the expression for the second table to break.
This is happening because the 2nd expression uses the COPY_ACCOUNT_ID field which is located in a different table than CUST_GROUP. I've updated the application so selecting the group doesn't break it anymore. Pay particular attention to the expression in the Customer Group listbox, because that's important.
