Forum Discussion

ArchiveUser's avatar
ArchiveUser
Qrew Captain
9 years ago

How to create a relationship between two connected dataset tables?

Hi,

I've got two tables using connected datasets and each one is using a refresh key as the key value. 

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 -> 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. 

Rather than using the 'related key', I want to use the custom formula to link the two tables together. 

Is there some way to use a field other than 'related key' when creating a relationship between two connected tables using refresh keys?

 

[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?

Formulas and functions
Relationships
Tables and fields

8 Replies

Sort By
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    9 years ago
    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's avatar
    ArchiveUser
    Qrew Captain
    9 years ago
    Hi Brian,

    >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'.

    >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's avatar
    ArchiveUser
    Qrew Captain
    8 years ago
    Hi Bob, I am facing the same problem. How did you resolve it? Thanks, Dmitry.
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    8 years ago
    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's avatar
    ArchiveUser
    Qrew Captain
    8 years ago
    Thanks for coming back to me Bob. I also raised this issue with QB support.
  • GraysonCooper's avatar
    GraysonCooper
    Qrew Member
    8 years ago
    Having the same issue. Definitely submitting for support/feature request. Trying to get this to work based on a copy/paste from the formula field to a text field within QB, but no luck so far.
  • BobJordan's avatar
    BobJordan
    Qrew Cadet
    8 years ago
    So far as I know there's been no movement on the feature request and use of formulas will still not work, so the only solution is to modify the source data to include a unique value that exists in both sets.
    The easiest way I found to do this is to manually concatenate multiple fields together. You can do this manually in excel, but for automation purposes you'll probably want to make a script and set it to run automatically using windows task scheduler.
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    8 years ago
    Part of the reason you can't use formula's is because you'd have to lock the other data that is used in the formula, and it can't be dynamic.

    As it is now if you use a key field, and then change that key field value, it will mess up all the relationships.

    There are always some work arounds depending on your situation. (either script, a sync table, or pre-creating records)