Autofill multiple fields?

Question

I'm creating a database to replace an Access database we currently use. In Access, when we select an item pulling from our table, it autofills x-number of other fields in other parts of the form.

In the first example below, selecting a Jurisdiction will autofill both the Tax Rate to the left and the remittance address underneath. Jurisdiction, Tax Rate and Address are all fields in the same table within the app.

I also want to create a similar situation on the example on the right. Currently all the Association info is manually typed. I have a table ready to go with all this information included, so that when you select Braun Oaks for example, all the other fields fill in. Again, these fields are all included in the same table within the overall app.

How exactly do I do this? Is it a formula I must create, and what would be the correct syntax?

chayceduncan2 · Answer

It sounds like you just need to create a relationship in Quick Base between a Jurisdictions table and your other table(s). In this case - you have a table for Jurisdiction Data - of which you have the data for Tax rate, address, remit info etc stored in those records.

Jurisdictions would then be a parent, meaning Jurisdictions would have many associations or items (whatever child you want this information pulled into) - and with that relationship, you would select a 'Related Jurisdiction'. The items like Tax Rate, Address, Phone etc would then be lookup fields, which are built in parts of the relationship - which means they will automatically pull down once you have selected a Jurisdiction.

Chayce Duncan | Technical Lead
(720) 739-1406 | chayceduncan@quandarycg.com
Quandary Knowledge Base

quickbasecoachd · Answer

Chance gave a great answer, as look ups are the way to go.

You will also want to use snapshot fields as described here
https://help.quickbase.com/user-assis...

To prevent historical records from being changed when these tax rate change in the future.

chaseerwin · Answer

Hm. I'm still not getting the desired result. I'm likely not going through the steps correctly. I got as far as creating the relationship as suggested above, Jurisdiction as parent, etc., and selecting the lookup fields. I put the lookup fields into position in my form, but in preview mode the expected autofill is not happening.

We're creating documents we call Certs as our end product. Each cert can hold information on up to 9 separately listed Jurisdictions, and when a single Jurisdiction is chosen, that line should fill with the information as described above. That makes me wonder if I have to make nine separate relationships for this to work?

chayceduncan2 · Answer

In regards to it not working - have you opened up the form and actually selected a jurisdiction? If you've set up the relationship and the lookup fields, then when you select a Jurisdiction on the form - assuming the fields you're using have data in your parent jurisdiction, they should populate.

As for your second question - technically that is one way to do it. You could set up 9 relationships - each with their own set of lookup fields. An alternative would be to set up a join table, basically another table that is the textbook method of handling many to many relationships as you've described. Basically what it sounds like you actually have is:
Jurisdictions have many certs
Certs have many jurisdictions

With many-to-many relationships like that - the proper way is to create a join table 'Cert-Jurisdictions' - where your relationships are:
Jurisdictions have many Cert-Jurisdictions
Certs have many Cert-Jurisdictions

This join table breaks up the many-to-many problem

In this case - your Cert-Jurisdictions table is a child to both, and you can have as few or as many Jurisdictions related to your cert as you might need. This can be tricky from a reporting standpoint sometimes. That said - you could get away with doing it with 9 relationships - its just a little harder to set up and manage.

Chayce Duncan | Technical Lead
(720) 739-1406 | chayceduncan@quandarycg.com
Quandary Knowledge Base

chaseerwin · Answer

Yes, I had gone into Certs to test this out, each step of the way. Here's what I found:When I built the form for Certs, I listed all 9 jurisdiction spaces with their own name (Jurisdiction1, Jurisdiction2, etc). I had each one tie to the Jurisdiction name field and was not getting the pre-fill I was expecting.So after going thru the relationship creation process again, I realized that now I had the choice of Related Jurisdiction plus my field names from the Jursidiction table available in blue.So in the example above, I selected the all-caps JURISDICTION that was in blue, and replaced "TaxRate1" and "Address1" with the blue "Jurisdiction-TAXRATE" and "Jurisdiction-JADDRESS". That does lead to the expected pre-fill.I have since replaced JURISDICTION with the "Related Jurisdiction" field making the relationship created and getting the same result, which is what I expect I was to do in the first place.Now I have 8 Jurisdiction fields left to fix - "Jurisdiction2," etc. I know that I cannot select "Related Jurisdiction" again so what is my next step? Do I create the relationship again, do I copy Related Jurisdiction? And since I know that TAXRATE and JADDRESS in blue will copy all the way down the page if I use them in all 9 instances, how do I prevent that from happening when I select my second jurisdiction, since there were no "Related Rate" or "Related Address" fields created in the relationship?

Forum Discussion

Autofill multiple fields?

6 Replies

Related Content

Using Grid Edit to Edit Multiple Records at Once

Help with Lookup Fields from Multiple Parent Tables in Quickbase Form

Autofill user field when a checkbox is checked

Multiple reference proxy fields help

Check one field for multiple conditions