Populating form field with selected values from shared field

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • In Progress
  • (Edited)
I'm in my evaluation period and have a question.  I've built a Company and Contact table and related them.  The Company table includes a field - Company Type.  All good.  Now I'm working on another form (say Form A/Table A) which will collect information on a Company but only for a particular Company Type.  In Form A, I have a Company Field and set the input to that field as the Company Name from the Company table.  However, I only want to see Company Names that have a specific Company Type.  There will be only one record for each Company in Table A.

My background is more Access so I'm still trying to unwrap my mind from those particular conventions.

Photo of Bob Wagstaff

Bob Wagstaff

  • 110 Points 100 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,758 Points 20k badge 2x thumb
There are a few ways you can use the "Type" of company to filter or show specific things.

One way is through dynamic filters.  Those are the filters on the left of reports that allow you to filter the report, based on one or many criteria.  
YOu can have up tp 5 dynamic filters per report.

Another way you can use that 'type' is use what is called dynamic form rules.  These can show or hid other fields on your entry form, based on the criteria you pick.  
Photo of Sam C

Sam C

  • 322 Points 250 badge 2x thumb
I believe this is a classic case for what QuickBase calls 'Conditional Dropdowns.' 

Conditional Dropdowns all you to filter the choices for one dropdown based on the choice in another. In this case, filtering Companies by Company Type.

To use this feature, the first step is to make your Company Type field into its own separate table. Once have done that, you need to relate Company Type to Form A. When you have done this, go to the field properties for Related Company and check 'Conditional Dropdown', where 'Related Type' = 'Company: Related Type'. 

There is a more detailed guide on the QuickBase Help page here: 
Welcome to QuickBase. It is a great program.

Just to add some colour to the post above, if you want to be able to have a conditional drop down based on first selecting type then the post above from Sam is the correct answer for your use case.

But if in fact you always only want to see companies from companies of type A in that drop down, then you simply build a relationship where one company has many form records and you will have an opportunity on the form properties for that company selection field to choose the report which is used for the drop-down list. If you make a report which simply has a filter for only Type A then the drop-down will always only contain company type A.
Photo of Bob Wagstaff

Bob Wagstaff

  • 110 Points 100 badge 2x thumb
Okay QBCoach,

I think your approach is the way to go but having some difficulty executing.  I created the relationship between to have one Company Type to many Companies.  Within the settings of the Company Type table I can create reports and filter on Company Type to see only the types I want to see.  Now I create another table to contain the special info I want to capture.  Now I move to the form/table for  the special info I'm storing.  I open that form and select the Company Name , I can't seem to find where I select the report that filters just the Company Types I want to see.  For that matter, don't see where I can assign a report to a drop-down list at all - only see where to assign a field source.
On the form properties for the Company field, you may select a report to use for the drop down.  If you are stuck, contact me via the information on my website QuickBaseCoach.com and I will do a quick GotoMeting to show you.
Photo of Bob Wagstaff

Bob Wagstaff

  • 110 Points 100 badge 2x thumb
Appreciate the offer - may have to take you up on that but don't want to waste your time.  I feel like I'm not making the assignments in the correct places.  The setup here is so different from what I'm used to in Access.

OK, I see the problem.  You need to create an actual relationship between the tables.  Not just a shared drop down field.  

Have you created the relationship? If so, you need to put the Company Name field form the relationship the relationship on your form.  Typically, you want to set up the Relationship where the Key field of the is  the default Record ID# field and you edit the field properties for the field typically called [Related Company]  and set the Proxy field to be the Company Name.

Then you will have control over what the drop down choices look like, ie which fields show and in what sort and with what filter and potentially any set up for Conditional Drop down.
Photo of Bob Wagstaff

Bob Wagstaff

  • 110 Points 100 badge 2x thumb
And this is where I do get confused with regards to how QB is using relationships.  I understand that one Company can have many contacts and one Company Type can have many Companies.  For this table, I only have 1:1, if the Company is a CAT dealer (as designated by the Company Type), then they have one record in this table.  If I create a relationship, then is it one CATDealer to many Companies or one Company to many CATDealers?  Neither seems right to me.  Maybe Sam's approach is the most efficient?
The design of a good app should have only 1 Company table to hold all the Companies.  One would assume that there will be lots of companies and they will each belong to just one Company Type each.  is that the business situation?

If so then  the relationships are

1 Company Type has many Companies.
1 Company has many Orders ( or whatever your form is collecting.

Typically, you might want to choose a Company Type, such as Cat Dealer and then see a conditional drop down (Sam's approach) of just the dealers who are Cat Type Customers.  If the list of all Companies who are Cat Type is a short list, well then it's short, but I assume that for other types of dealers there will be more than 1 to choose from.
Photo of Bob Wagstaff

Bob Wagstaff

  • 110 Points 100 badge 2x thumb
Okay - I got it working by building a relationship and using the list report but I must admit I'm not really comfortable with it.  I agree with your comments about design and have it structured accordingly.  All I'm trying to do is to store in a separate table unique information about a particular Company if they are of a particular Company Type.  I could include the fields in the Company table but I have an aversion to having a fields in a table that are not applicable to all the Companies in that table.  
OK, so progress.

As  for the issues with fields, you are typically far better to have extra fields than to have extra tables and have different tables for Companies in different Company Types.  

One solution is to use form rules and open up sections of the form based on the company type.  You will need to put the common fields at the top of the form in a common section, and then have a section open up for say the Cat specific fields based on a form rule that says

When  Company Type is Cat, 
Show Section Cat

Not that data entry fields many not be on form twice, so that is why you need to contrive the field layout to have the common fields like name and address and phone in a common section.