Allow One to One Table Linking



It would be great if two tables could share the same ID as a primary key. For example, say we have a client table and a sole trader table.

Not all clients are sole traders, some are limited companies or even individuals. At the moment in time I have a redundant column that acts as a primary key, but the primary key should be the client ID.

Thanks in advance


Just a thought/suggestion (that maybe you are already doing), but could you make the primary ID column in your Sole Traders table a formula that just points to your Client table links? You would need to set it up so that if the Client is NOT a Sole Trader, it would point to a different column/field.

For example, the IF formula in the below base is:
IF({ClientID}=BLANK(),{Sole Trader Name},{ClientID})

Other alternatives would be to indicate on the Client ID table which clients are and are not
Sole Traders (checkbox, single-select, etc.) and lookup that information in the Sole Trader table to then filter or calculate the ID field. Depends on where you want the control I suppose.