Forum Discussion
Conditional table relationships
Hello,
I have 3 tables, Sales, Store and DistributionCenter. And these are my tables schemas em relationships:
| Sales | Store | DistributionCenter | ||
| storeID | ----> | storeID | ||
| distributionCenterID | storeName | distributionCenterName | ||
| specialCode | distributionCenterID | ----> | distributionCenterID |
And these are my tables data:
| Sales | ||
| storeID | distributionCenterID | specialCode |
| 1 | 1 | 1 |
| 1 | 1 | 1 |
| 1 | 1 | 2 |
| 1 | 1 | 2 |
| 2 | 2 | 1 |
| 2 | 2 | 2 |
| 2 | 2 | 2 |
| Store | ||
| storeID | storeName | distributionCenterID |
| 1 | StoreFoo | 1 |
| 2 | StoreBar | 2 |
| DistributionCenter | |
| distributionCenterID | distributionCenterName |
| 1 | Foo |
| 2 | Bar |
| 3 | Special |
So, the Sales tables is connected with stores' tables and the table Stores is connected with the table DistributionCenter. The problem is if the value of specialCode column on Sales table is 2, then the right distribution center is Special and the storeID can not be changed.
For example, a sale with specialCode as 1 and storeID as 1, points to distribution center 1 (Foo). But if the specialCode is 2, then it must points to distribuition center 3 (Special). Is it possible to accomplish that without changing the relationships?
Thanks.
Anonymous we can try this by adding new column in sales table:
Dist Name = var d= RELATED(Dist[distributionCenterName]) return if(Sales[specialCode]=2, LOOKUPVALUE(Dist[distributionCenterName], Dist[distributionCenterID], 3), d)
4 Replies
You should add a calculated column in your sales table for distributioncenterid and then link this new column directly from sales table to distributioncenter table.
parry2k, thanks for helping me. I known that I can do that. But I do not want to change my relationships. So is it possible to do it without changing it?
Anonymous we can try this by adding new column in sales table:
Dist Name = var d= RELATED(Dist[distributionCenterName]) return if(Sales[specialCode]=2, LOOKUPVALUE(Dist[distributionCenterName], Dist[distributionCenterID], 3), d)