While table queries are very useful they provide only basic functionality. Coming from SQL databases I expected that at least basic query functionalities would be available thru Queries - I’m greatly missing two tables in query or at least query for data filtering based on data from another table.
My use case:
I have two tables. One is Code list table - table contains all available production machines (Line1, Line2, Line3 …) . The other table contains selected production machines for SKU. This is configurable thru application. So second table will have multiple production lines for SKU. EG:
SKU - ProductionLineId
10001 - Line1
10001 - Line2
10001 - Line4
10002 - Line1
10003 - Line3
If query would allow filtering based on values in another table Code list table can be automatically queried based on ProductionLineId values from another table (like equivalent “not in” functionality from SQL). User would not be able to select same production line and create table record that already exist in other table.
So I have no simple way to prevent multiple identical records.
Also unique index definition in SKU - ProductionLineId would prevent creation of identical records but this is another very useful functionality which would help to maintain table data integrity.
Are above functionalities planed for Tulip or is there any other way to implement query functionality described above.