The reason for this suggestion originates in product construction sets. The way products are constructed and then prepared for export to different international markets results in a many to many data table.

One part can be contained in many different final products. The same part can also be in a set. A set can contain many products that can also be sold individually.

To solve this issue we would require a secondary key to be able match data correctly.

The attached images show the tables involved. Our culprit here is the Construction sets table, that shows which components form the final product. We also have the final product table and the parts list table. These two basically contain data on which is which. The diagram shows the basic setup. Finally I've also attached the final result tables. One with only using the product table list and one with using both the final product list and the parts list.

The goal is to be able to add in Discovery either the Parts list or Product list and get data only for those or add them both to get the final information on how many parts were sold based on the construction list. The data is calculated based on the order of columns or which columns are added to the drop zone. This is the main point of context sensitivity. The construction list table is hidden from the end user and is used only to translate data based on input tables.

We already have a functioning many to many table in Pyramid. That is the security table. We need a similar solution for us to use in data modeling side of things.

I hope I managed to explain the problem well enough. If not I can provide further answers to any questions.

I don't know if this can be implemented but it would give us a lot more options to deal with the problems we face on a daily basis.