Conditional Drop Downs

  • 0
  • 1
  • Question
  • Updated 5 years ago
  • Answered
I'm having trouble figuring out how to construct a particular set of conditional drop downs.

My Goal: Under a Task record, select a Type and a Topic, and then choose a Writer that supports the Type & Topic variables.

Background: I have a list of Writers that support a set of Types and Topics. For example, John can write a Blog Type on a Sports Topic. Peter can also write a Blog Type on a Sports Topic.

That being said, when a user selects Type - Blog and Topic - Sports, they should be given a choice of John and Peter, along with any other writer that supports the variables.

Writers are also assigned to multiple Tasks.

Problem: I can't seem to figure out the correct way of creating the Table-Relationships. Writers can have many Tasks, Types, and Topics but I need to filter the Writers based on Type and Topic from the Task level.

Any advice would be appreciated, thank you!
Photo of NP

NP

  • 10 Points

Posted 5 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
I'm sure that this can be dome, but the solution depends on this.  Are these Types are Topics inherently hierarchial?  For example, would you see loading up this table with a list of "writer capability" records for Type Blog and then proceed to list every writer- capability for that Type.

Or is it a case that a write might be able to do a Type Blog for sports, for for a different topic they could only do a Word document article.  Or can a Blogger blog on any topic they are qualified for as a writer from a content perspective.
Photo of NP

NP

  • 10 Points
If I understand your question correctly, the answer is hierarchical?

For example: John writes about Business, Technology, Sports, and Medicine. He only writes Blogs, Articles, and Press Releases. Therefore, John can write:

Blogs | Business
Blogs | Technology
Blogs | Sports
Blogs | Medicine
Articles | Business
Articles | Technology
Articles | Sports
Articles | Medicine
Press Releases | Business
Press Releases | Technology
Press Releases | Sports
Press Releases | Medicine
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
OK, I think that this is the setup you you want.

There will need to be a middle table I will call Writer Capabilities - that is the details, for example which you listed in your response above. So, John would end up with 12 entries. As an aside, there could be various URL buttons to help speed up the data entry..., but that would be icing on the cake we need to bake.

I will use the shorthand


One < Many

I will also use the shorthand for a WC "Writer Capability" those 12 records are each an WC

Each relationship will just use the traditional [Record ID#] as the key field to the table and we always want to select the first lookup to be the most meaningful one so it will default to being the "Reference Proxy" setting for the field [Related xxx]. You can set he Proxy field later, but its easier to DIRTFT (Do It Right The First Time)


One Writer < WC


One WC < Many Tasks (ref related WC, Proxy is probably Writer Name)


One Type < Writer Capabilities


One Topic < Writer Capabilities

One Type < Tasks

One Topic < Tasks

So I think that is the setup that would allow you to Enter task and select a Type and Topic and WC.

But, you ask, what about the conditional drop down aspect? No problem.

Make a formula text concatenated field called [Type-Topic] of the Type and Topic such as List("-", [Type Name],[Topic Name]). Do that field on the WC table.

Make that same formula field on the Task Table and show it on the Task Table (I find that Condional drop down needs the pre-condition field to show on the form)



So the workflow is that you enter a Task and say select Type = Blog and Topic = Business. The formula calculates to


Blog-Business

and you set the field for [related WC] in the Relationship

One WC < Many Tasks


to be conditional on after the field [Type-Topic] is chosen match where equals WC: [Type-Topic]

Let me know how you make out. I almost feel like building the little app myself as this will be quick to get working especially if you have the forms defaulting initially to automatically add fields to the form and get you first lookup field correct so it defaults to a good reference proxy.
Photo of NP

NP

  • 10 Points
I will try this and let you know, thanks!
Photo of NP

NP

  • 10 Points
It works but I need the [Related Writer Record] to be selected so I can keep track of workload.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
Sorry, I'm not understanding the question. Or maybe I do.  You also need to have a relationship
One Writer  < Tasks.

Then you can do a summary roll up of the # of open tasks.
Photo of NP

NP

  • 10 Points
Okay, I have that there but is there any way to have it automatically pick the matching Writer - Name?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
Oh, I think I understand.  You should be able to do a lookup of the [Related Writer] from the selected WC. and show that on the form.  Then have a form rule set to fire all the time (there is a checkbox at the button to uncheck which says

When  [related writer from WC lookup] is not equal to blank, change [Related Writer] to the "value in the field" [Related writer from WC lookup]
Photo of NP

NP

  • 10 Points
Hmm, so I created that form rule but it still isn't automatically changing the [Related Writer].
Photo of NP

NP

  • 10 Points
I attached the fields that I currently have set up. Maybe that will help?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
Contact me via the info in my profile and I will have a quick look and get you over the hump.
Photo of NP

NP

  • 10 Points
Thanks for all the help but we've decided that our users will just manually select the filtered out Writer - Name.