How to create a relationship between two connected dataset tables?

Question

Hi,I've got two tables using connected datasets and each one is using a refresh key as the key value.&nbsp;The values used in the refresh key for one table are also present in the other table, so I've made a formula to re-create the refresh key value and want to use that as the reference for the relationship (refresh key -&gt; formula).The problem I'm having is that when creating the relationship the 'reference field' only lists 'related key' in the drop down box. Additionally the other fields in the connected dataset are not present in the lookup fields.&nbsp;Rather than using the 'related key', I want to use the custom formula to link the two tables together.&nbsp;Is there some way to use a field other than 'related key' when creating a relationship between two connected tables using refresh keys?&nbsp;[edit]As a comparison, I've also attached a screenshot (capture.png) of another table to table relationship I have setup between two connected datasources. In this one, I'm able to select other fields as the 'reference' field. The only difference I can see between the two setups is that one is a composite key field while the other is text. Can composite key fields reference any field other than the default 'related' one?

archiveuser · Answer

Hi Bob,

I've seen this happen where the field type of the key field and the desired reference field do not match. Can you tell us what the type of the key field of the master table, and the field you'd like to use as the reference? You should not necessarily need to make a formula for your reference field. If that does not work, I think it would be best for you to open a support case by going to Help > Manage Support Cases > + New Support Case so one of our customer care specialists can take a look at the table with you.

Thanks,

Brian

archiveuser · Answer

Hi Brian,&gt;Can you tell us what the type of the key field of the master table, and the field you'd like to use as the reference? The Master table is using a "composite" field type which was generated by combining three text fields together. The reference field is the same three text fields as the master key, but combined with a formula rather than the system 'refresh key'. &gt;You should not necessarily need to make a formula for your reference field.Can you expand on why I should not need to use a formula? I am trying to associate two pre-existing datasets in a one-to-many fashion. The values that make up the refresh key are unique on the master table when combined together and these same values are also present in the reference table, so I would think that I should be able to combine these values together on the reference table manually to match the format of he unique ones on the master table and then associate them together.

archiveuser · Answer

Hi Bob, I am facing the same problem. How did you resolve it? Thanks, Dmitry.

archiveuser · Answer

Hi Dmitry,The way I ended up resolving this was to modify the source data itself. You can create a relationship between two connected tables so long as the field you need to associate already exists in the csv that you are importing. Quickbase just can't do this through formulas for whatever reason (For QB admins: feature request :smile: ). So the best way for you to fix this is going to be to script up something that can concatenate the fields you need in the csv before importing it into QB. Once it's imported, then you should be able to easily create the relationship between the tables.

archiveuser · Answer

Thanks for coming back to me Bob. I also raised this issue with QB support.

Forum Discussion

How to create a relationship between two connected dataset tables?

Related Content

Cross App Relationships

Connected App same realm

SWEET CONNECTIONS: The Scoop on Relationships

Filter records based on table connection

Table Relationships - Connected CSV Table