Dependant Dropdown

[_Dave_]

Hi everyone,

Looking for some help with a dependant dropdown setup, I have;

a PROJECTS table

a WORK ORDERS table, workorders are assigned to the PROJECTS table

a TASKS table, tasks are assigned to a single PROJECTS and one or multiple WORK ORDERS.

When creating a task, a single project is selected, and a second dependant dropdown shows only the WORK ORDERS for the project, multiple can be selected.

I have searched the dorums, and tried so many various answers, however there appear to be so many variations, I can't find anything that works for me.

I need to know what the field types should be for both dropdowns and the Valid_If for the dependant.

As always, any help is greatly appreciated.

[WillowMobileSys]

The answer depends on your intended usage of Tasks.   They could be used as just an informative list of items and then assigning Task rows directly to a Project Or Work Order is perfectly fine. 

But most of the time, apps of this nature need to be able to interact with the Tasks by:

• assigning them to a responsible party
• being able to track progress of the task
• Track dates of creation, assignment and/or completion
• Time spent on Task
• etc

In this later case you can't simply assign Task A to multiple Projects/Work Order because each Task A would require different details about that specific Task in that specific Project/Work Order.  There would need to be an interim Table such as "Project Tasks" or "Work Order Tasks".  These tables would take in a Task column but then also supply the other Task related details.

How do you intend to use Tasks in your app?

[_Dave_]

Thanks for your message, I'm unsure how to describe this in further detail, other than what I've put above.

A task would have a creator, assignee and completed by field.

Most of the information like time spent and expenses spent are assigned against the work order.

I'm fairly confident the way I have it, with the Projects table Work Orders table and Tasks table is the way I want it. This way when a project is selected, amongst the other inline table's, we can clearly see the Work Orders and Tasks inline view inside the project view.

In regards to the ENUMList dependant drop down. I have created something like this previously, however I no longer have that app and cannot remember how I did it. I'm unsure if the Tasks Project field needs to be a ref, enum or ENUMList for the single select and then whether the Work Order field needs to be an ENUMList for the multiple select. And what the Valid_If field needs to be.

Thanks again for your help, any further info would be great 

[WillowMobileSys]

I apologize, the way I understood Tasks table was a pre-determined list of Tasks that are then assigned to Projects and/or Work Orders.  Reading again, I'm not sure why I thought that.

So on to the dropdowns... 

My understanding is that you have a Tasks table with a "Project" column where a single project is selected.  Then a "Work Orders" column where you can select one or more Work Orders associated with the selected Project

The Project column should be defined as "Ref" with the table source selected as Projects.  Thed Valid_If expression would simply be Projects[Project ID]  (or whatever the name of the key column is)...to list all of the Projects in the dropdown.

The Work Orders column would be defined as EnumList (to allow multi-selection),  The Base Type would be set to "Ref" and the source table set as Work Orders.

Assuming your Work Orders table has a "Project" column as well, the Valid_If expression would be:

FILTER("Work Orders", ([Project] = [_THISROW].[Project]))

This should provide a list of Work Orders for the selected Project with check boxes so you can select more than one.

[_Dave_]

Thanks for your reply, this still doesn't work, it doesn't show any errors, but also won't show the second dropdown box after the project has been selected.

Is there anyway I could add you into have a look? Or would you have any further suggestions?

[_Dave_]

I've now solved this;

My solution was;

Changed the Project field to REF the Projects table, no VALID IF.

Changed the Milestone field to ENUMLIST base REF the Project Milestones table with the VALIDIF of FILTER("Project Milestones", [Project] = [_THISROW].[Project])