Creating dynamic dropdown lists

Created by Damien Zwillinger, Modified on Mon, 4 Sep, 2023 at 3:21 AM by Damien Zwillinger

Applies to:

  • Velixo NX
  • Velixo Classic
  • Every ERP


TABLE OF CONTENTS


Overview

A not uncommon question is how to create reports that have filters that dynamic update based on selections in other filter dropdown lists.


Consider an organization that is set up as a tree structure such as the below:

 

tree_structure.png


In this example, each Department may have multiple Sub-departments, each Sub-department might have multiple Programs, and each Program is assigned a General Ledger subaccount.

 

We may want to report on an entire Department... or limit a report to just a single Program within that Department.

 

Let's take a look at how this might be accomplished.

 

How-To

We will want to set up our report with pulldowns showing the available values for each of our categories - and determine the appropriate subaccount(s) to use, based on the value of each category:

 

Tree_structure.gif


We can then use the subaccount filter as part of Velixo GL functions to return the data we need.



1. Create a List of "Units"


Since Excel is really good a working with lists of data, let's make a list showing each Department along with its Sub-departments and Programs - along with those all-important GL subaccounts:


tree_list.png

We'll refer to this as our "Units" list.

By adding an "ALL" option to each Department (and/or Sub-department), we can combine all of the individual GL subaccounts that are associated with each unit.

For example, you can see that that subaccount list for Community Based Services - All - All (3????;4????) encompasses all of the subaccounts for each of the possible Sub-departments and Programs under that Department.

Now that we have this list, let's build the report to use it.


2. Create a Lookup List of Departments


We can start our report by using some simple Excel functionality to create a list of the Department names:

tree_depts.png

The Excel functions look at the list of Departments (column A in our Units list) and show each of them only once.

Next, let's build a pulldown list that allows the viewer to select the desired Department.

We will create an area where the viewer can specify the Department, and then we will use Excel's Data Validation took (on the Data ribbon):

tree_dataval01.png

On the Data Validation dialog, we set the cell to display a list, and the source for that list is whatever is specified in cell K3:

tree_dataval02.png

(note that we used the # prefix after the cell reference, so that the size of our list can shrink and grow as the number of Departments shown in column K shrinks and grows).

Once we press OK, we can now select from our list:

tree_depts2.png


3. Create a Lookup List of Sub-departments


Next, we need to build a list of sub-departments that belong to the selected Department.

tree_subdept2.png

This function is similar to what we used earlier to return the Department names. This one, however, only retrieves those sub-departments which match up with the Department listed in our dropdown in cell B2.

Next, we can again use Excel's Data Validation feature to create a dropdown for the Sub-departments (pointed to the list we just created in cell L3):

tree_dataval03.png
tree_subdept3.png



4. Create a Lookup List of Programs


Next we can use the same techniques we applied for Sub-departments to create a list of Programs that correspond to both the selected Department and Sub-department...

tree_dataval04.png

... and create the dropdown with Data Validation:

tree_program1.png



5. Lookup our GL Subaccounts


We are now ready to use all the information that has been gathered to lookup the GL subaccount(s) that we need to use.

We can employ the Excel 365 XLOOKUP function to do this:

=XLOOKUP(B2&B3&B4,Units!A2:A999&Units!B2:B9999&Units!C2:C9999,Units!D2:D9999):


tree_units2.png



6. Add a Macro to make things work even better


By adding an Excel macro to automatically reset the other dropdowns whenever there is a change to a higher dropdown, we can ensure that the settings are always valid:


tree_macro.png


We now have a working basis for our report and can build our Velixo functions to use the determined GL subaccounts.



Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article