Forum Discussion

MatthewMatthew2's avatar
MatthewMatthew2
Qrew Trainee
12 years ago

Formula to look up a corresponding field from a related table?

I'm a new QuickBase user. This is probably a simple question, but I don't know how to construct a formula to look up a corresponding field from a related table.

I have a parent table and a child table with a one-to-many relationship. The child table contains only two fields: Code and Description. There are thousands of records in the child table, each containing a Code and a corresponding Description.

The parent table has a form. One of the fields in the form allows users to manually enter a Code. Another field in the form -- which is a text formula field -- is where I want to enter a formula that will return the corresponding Description based on the Code entered in the first field. Both the Code and Description fields from the child table are part of the relationship to the parent table.

I'm not sure what functions in Quickbase will produce the desired result. I've scoured online help, the knowledge base, the community, etc. for several hours. The closest I've come is finding a response saying yes, QuickBase can do this, but no instructions on how. Can someone please help? Thanks in advance.
Account and realm management
Forms
Formulas and functions
  • MarkShnier's avatar
    MarkShnier
    Qrew Cadet
    12 years ago
    The help that you want to review is the one for making relationships.

    Here are the steps.

    In fact, what you think of as your child record is actually the parent. I presume that the code is unique in the code table. If you already have a relationship where your data entry form record is the Parent, then go back and delete that relationship as its backwards.

    Then, make the Key field in your Codes table the Code field.

    Now - you make your relationship where One Code record is related to Many data entry records. It sounds like you want to have the user select a code from the drop down list so you can allow the relationship to create a field called related Code (which you can late rename if you like. Then bring in the description to your data entry record by adding the description as as look up field.

    Having said all that, it might be helpful if you describe the bigger picture as to what you are trying to accomplish here, as we may be able to help more if we understand what you are trying to do. ie what is your use case?
  • MatthewMatthew2's avatar
    MatthewMatthew2
    Qrew Trainee
    12 years ago
    Thanks, Mark.  You're correct -- each code in the Codes table is unique and the relationship was backwards.  I have deleted the relationship, changed the key field in the Codes table to the Code field, and re-created the relationship so that one Code record is related to many Data Entry records.

    Now, back to the data entry form.  I do NOT want the user to select a code from a drop down list.  There are over 14,000 codes so that would be impractical.  I want the user to be able to manually enter the Code (which they will know), and I want an adjacent field in the Data Entry form to return the Description of whatever Code was manually entered in the previous field.

    Does that help clarify what I'm trying to do?
  • MarkShnier's avatar
    MarkShnier
    Qrew Cadet
    12 years ago
    OK, so the trick to that is to create a new field  in your data entry table which is a formula text field (assuming that your code is a text field, otherwise numeric), and make the formula simply be the code field that you want to enter.  Then once again delete the relationship  but this time build it on the  formula field.  So to be clear, a data entry field, and then a text formula field equal to it and build the relationship on that formula field.

    So, the effect of this is that you are not "directly' entering the key to connect the tables and hence you will not be put into the record picker which most users hate.  The description will certainly pull in when you save.  If you have that formula field on your form (even if its hidden by a form rule which causes it to be always hidden) then it will probably pull in during order entry after you tab out of that data entry field and the formula calculates the value of the link field to the relationship.
  • MatthewMatthew2's avatar
    MatthewMatthew2
    Qrew Trainee
    12 years ago
    Worked like a charm!!!

    Yes, the Code is a text field.  What you suggested basically created a surrogate field to stand in for the Code field that is being manually entered.  Once I set up the relationship as you described, I made the Description field on the data entry form a lookup field where the reference is the formula text field and the value is the Description from the related parent table.

    Thank you so much!  I've been racking my brain for days trying to figure out how to make this work.  Much appreciated.
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    12 years ago
    Hi Mark,  I am trying to complete this function and I'm not sure what I am doing wrong... I created the formula text field and then created the relationship and it pulls over only the KEY information and not the rest of the fields I need...Can you please help me understand exactly how to accomplish this?  Thanks!
  • MarkShnier's avatar
    MarkShnier
    Qrew Cadet
    12 years ago
    Can you please start a new thread and state your question and describe your table relationships?  
    its hard to know your exact question since you have joined an existing thread.
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    12 years ago
    Yes, I typied it in and it provided me a listing of threads to review and respond to...
  • MarkShnier's avatar
    MarkShnier
    Qrew Cadet
    12 years ago
    then it gives you a chance to ask your question.  It first tries to check for you if someone else asked the same question - but push on and it should let you ask your question.  You will need to Sign On at the top right ... maybe that is the problem.