Relations help

[I_NeedMorePower]

Hello Power community!

I want to create a 1 to * relation between two tables. for example the tables are:

Table 1: Items (YearID, BranchID, ItemID)

Table 2: Trasactions (YearID, BranchID, ItemID)

The relation should be Items 1 --------- > * Transactions

but the issue is, when I try to create the relation, it becomes many-to-many.

The reason for that is, in Items table, there is a field called YearID and BranchID. In each year and in each branch, the item IDs can be re-inserted each year and in each branch....So the item ID is not unique in its own table ITEM....

The same thing in TRANSACTIONS table, there is a YearID and BranchID.

So in order to workaound this issue, what should I do?

Should I create a new columns in each table with YearID + BranchID + ItemID and create the relation using it?

or should import the Year Table and Branch table and create multiple relations?

I have several tables in my dataset with this issue, so I don't know if I sould go with creating new columns as keys for the relations.

Any tips would be appreciated 🙂

Thanks in advance!

[AnthonyTilley]

In my opinon i would have suggsted the concat colunms approach you mentioned. and join the two colunms. but this is only really suitable if you are using only these two tables and they are small data sets

If you will later want to beable to filter or calculate based on the year table or Branch table then you will need these tables anyway so would be best to go down the prefered route and bring these tables in and use them as a bridging table between your two tables above.

I always try to follow a rule that the least amount of calculated colunms is probabaly the best approuch