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. ...
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.
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?
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.
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?
So the next steps if you're going to go this way is to make another relationship to Related Jurisdiction 2 - and all the lookup fields that you would need from that relationship. For each of the 9 jurisdictions - you'll need a new relationship / related field and all associated lookups that will replace what you currently have set up which are all standard input fields.
The alternative is what I commented above, that if you want - you could also set up a join table to eliminate the need to go through setting up 9 relationships, but you can certainly continue down this path with minimal to no issue. Just create a new relationship and swap those relationship fields onto your form to replace the prior ones