Expand all | Collapse all

Autofill multiple fields?

  • 1.  Autofill multiple fields?

    Posted 05-10-2019 12:53
    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?

  • 2.  RE: Autofill multiple fields?

    Posted 05-10-2019 13:19
    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

  • 3.  RE: Autofill multiple fields?

    Posted 05-10-2019 13:22
    Chance gave a great answer, as look ups are the way to go.

    You will also want to use snapshot fields as described here

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

  • 4.  RE: Autofill multiple fields?

    Posted 05-11-2019 21:50
    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?

  • 5.  RE: Autofill multiple fields?

    Posted 05-13-2019 13:09
    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

  • 6.  RE: Autofill multiple fields?

    Posted 05-13-2019 13:56
    Yes, I had gone into Certs to test this out, each step of the way. Here's what I found:
    https://d2r1vs3d9006ap.cloudfront.net/s3_images/1789042/RackMultipart20190513-94048-1lfyx8b-Cert6_inline.JPG?1557754840" width="194">
    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.
    https://d2r1vs3d9006ap.cloudfront.net/s3_images/1789045/RackMultipart20190513-128916-1efintd-Cert7_inline.JPG?1557755703" width="240">">https://d2r1vs3d9006ap.cloudfront.net/s3_images/1789046/RackMultipart20190513-54513-35rtob-cert8_inline.JPG?1557755783">

    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?

  • 7.  RE: Autofill multiple fields?

    Posted 05-14-2019 02:53
    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

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