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.
Question
Formula to look up a corresponding field from a related table?
This topic has been closed for replies.
+6The 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?
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?
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?
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?
+6OK, 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.
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.
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.
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.
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!
+6Can 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.
its hard to know your exact question since you have joined an existing thread.
+6then 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.
+6I'm lost at the part about deleting the relationship and building it on the formula field. I want someone to be able to put in their account number, and have it pull up their information so they don't have to enter their name and address when submitting a form to us.
I.think you will just have to try it to see.
�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�
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.
�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�
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.
Ok, np,
You say that you want to enter an account number on a form and have the client info auto populate on the form. Presumably you do not want to expose the full list of account numbers to the person entering an application form.
1. Make a field on your data entry form called �Enter Account Number�. This would be a numeric field if the aacocunt numbers are numeric, else Text.
2. Make a formula numeric (or formula Text) field with t(e formula of [Enter Account Number]. Call it �Account Number for Relationship�
3. Make a relationship where One Account as Many application forms. On the right hand side, do not let Quick Base make a new field for you, but rather select the field �Account Number for Relationship�.
4. Lookup any fields from the account number down to the Application form.
You say that you want to enter an account number on a form and have the client info auto populate on the form. Presumably you do not want to expose the full list of account numbers to the person entering an application form.
1. Make a field on your data entry form called �Enter Account Number�. This would be a numeric field if the aacocunt numbers are numeric, else Text.
2. Make a formula numeric (or formula Text) field with t(e formula of [Enter Account Number]. Call it �Account Number for Relationship�
3. Make a relationship where One Account as Many application forms. On the right hand side, do not let Quick Base make a new field for you, but rather select the field �Account Number for Relationship�.
4. Lookup any fields from the account number down to the Application form.
+6This is where I got lost in the first scenario... I created the relationship making the 'customer' the parent, and the data entry form, the child. On the data entry form, I created a, "Enter Acct #" field, and a text formula with [Enter Acct #] only. These are both fields on the child table. How do they get pulled over to the right side of the relationship selections? I know I'm missing something probably very obvious here.
+6And wow, do I feel stupid! I had to change the reference field in the relationship. I had never touched that before. I have only changed the everything below. That makes sense! Thank you!
+6How would I allow customers to pull up their main account record using a single field? Would I need to create a mirror of the record?
If you put the field on the form which is the reference field from the relationship (on the right side of the relationship), then your users can link through that to see be full account record.
If you want the form to be not showing all the same fields that you do, you can copy your form and make changes. Then in settings and forms, and them forms usage, you can set a Role to use that form. I suggest just showing the link to the account in view mode.
We in the Account Form, you can put the report link field from the relationship and show an embedded report of application forms, so your users have a way back to their application form.
If you want the form to be not showing all the same fields that you do, you can copy your form and make changes. Then in settings and forms, and them forms usage, you can set a Role to use that form. I suggest just showing the link to the account in view mode.
We in the Account Form, you can put the report link field from the relationship and show an embedded report of application forms, so your users have a way back to their application form.
Hmmm, so are you saying that the last six characters of the account number are in fact unique and in theory could be used as the Key field of the Account Record? Have you checked that they are unique or you just know they must be due to the natur if the data.
I see two alternatives.
The first is that if you just want to display information on a child form as an embedded report, then you do not need even a relationship.
You create a Report Link field and set it up so that in the configuration, the two fields match, for example the 6 characters that the user enters compares with a formula field on the VIN table which has a formula such as Right([VIN],6)
However, while you can see the report of the parent VIN record on the child record, those fields are not able to be used in any formuals on on a child report.
The alternative is to use the last 6 characters of the VIN which according to Wikipedia are a unique serials for cars made /in the US https://en.wikipedia.org/wiki/Vehicle_identification_number
as the Key field to the VIN table.
There are two ways to make that happen. One way is to make two formula fields to separate out the parts of the VIN between the serial # portion and the identification / check digit portion. Then change them to text fields and going forward, you will input data in two fields. Then set the Key field to be the serial #.
The other way, is that in fact, you can set the key field of a table to be a formula field, which would allow you to keep your data entry the same, as it may be from some imported source.
If you wanted to do that, I suggest making a copy of your app first and testing you import process after the change is done.
To change the key field to be a formula field is a bit advanced. You would just type the API string right into the URL on your browser along with an app token (or disable app tokens while you do this). https://help.quickbase.com/api-guide/index.html#setkeyfield.html
The first is that if you just want to display information on a child form as an embedded report, then you do not need even a relationship.
You create a Report Link field and set it up so that in the configuration, the two fields match, for example the 6 characters that the user enters compares with a formula field on the VIN table which has a formula such as Right([VIN],6)
However, while you can see the report of the parent VIN record on the child record, those fields are not able to be used in any formuals on on a child report.
The alternative is to use the last 6 characters of the VIN which according to Wikipedia are a unique serials for cars made /in the US https://en.wikipedia.org/wiki/Vehicle_identification_number
as the Key field to the VIN table.
There are two ways to make that happen. One way is to make two formula fields to separate out the parts of the VIN between the serial # portion and the identification / check digit portion. Then change them to text fields and going forward, you will input data in two fields. Then set the Key field to be the serial #.
The other way, is that in fact, you can set the key field of a table to be a formula field, which would allow you to keep your data entry the same, as it may be from some imported source.
If you wanted to do that, I suggest making a copy of your app first and testing you import process after the change is done.
To change the key field to be a formula field is a bit advanced. You would just type the API string right into the URL on your browser along with an app token (or disable app tokens while you do this). https://help.quickbase.com/api-guide/index.html#setkeyfield.html
+6The report won't work because I need the data to be stored in the vehicle record. Additionally, I cannot change the key field, because it would affect my integration with another system. I'm thinking perhaps making the vehicle record table a child table to a table where the last 6 of the VIN is the key field. I'm still trying to play this through in my head, so if you can contribute any pros or cons, that would be great!
Sign up
Already have an account? Login
Login to the community
Login with Quickbase US – quickbase.com Login with Quickbase EU – quickbase.eu
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.