Then, I have a query with CATEGORY, where the same field is used to identify the category or subcategory.
It is simple to create a relation from the ITEMS query to the CATEGORY query, based on the category field However, I need to create a 2nd relation from the ITEMS query to CATEGORY query again, this time based on the SUBcategory field. I'm thinking to clone the CATEGORY query, especially it contains a limited number of records.
Another solution coudl be to add a calculate column to the ITEMS query. This column will be the SUBcategoryID
@Jihwan_Kim@v-henryk-mstf many thanks, appreciated The screen shot below gives an indication of the queries and their relations. The query T_IIInvoiceItem stores the sales records by items with category only The query T_SIStockItem stores the item records, with category and subcategory ID's The query T_StStockType stores the category ID's and category text. A category can be also a subcategory. So, if I need to know the subcategory of an item, I need to contact the T_StStockType query. If I create a relation, based on the SiSubStockTypeID_N field, then I build a second relation. The line is obviously dotted, as it is not the active relation. I'd rather keep away from the USERELATIONSHIP approach, for simplicity. So, is there any alternative approach? I'm think to clone the T_STStockType query, as it includes a very limited of records, less than 100.
The first thing you need to know is that only one active relationship can be created between the two tables. If you need to continue to add a relationship, you can only create an inactive relationship. According to the creation logic given in the official document, you can modify it in conjunction with your own data model.