It is so common requirement in any kind web apps – to have dependent lists of values. For example in order to choose location, first of all we choose country, then in another combo box choose state, which is already filtered out against country. MS Excel itself has such a feature. We implement lists of values in excel by adding a validation of type list to the cell. Then providing a source data. If its dependent list, we provide indirect path to the source data. But its about implementation natively in excel, which is not exactly our topic. We’ll let ADFDI (ADF Desktop Integration) plugin do it for us. We’ll just need to set up accordingly in ADF application model layer and some tweaks in excel table component.
Just reminding, that we’re using Oracle HR DB schema as a demo base. In this sample we’ll extend previous sample which covered deletion from ADFDI table – How to delete a record in table component of ADF Desktop integration (ADFDI) Excel workbook?
We’re going to enhance employee listing in excel by adding a list of values for DepartmentId column. Additionally to that – an extra column for Location. The latter is going to be a LOV as well, on which department list will depend.
So here are the steps to proceed:
- Step 1. Download sample application from the post I’ve mentioned above. It will be the base to build on.
- Step 2. Setup ADF model layer
- Add additional attribute LocationId to view object EmployeesView. Create it as Transient and set as Always Updatable.
-
- Configure list of values for LocationId
-
- Configure list of values for DepartmentId
-
- Modify DepartmentsView view object query by enabling filtering against LocationId. Create a bind variable.
-
- Configure DepartmentsView view accessor in the EmployeesView to pass LocationId attribute to LocationIdVar bind variable
- Step 3. Setup table component in Excel workbook
- Add extra column for LocationId
-
- Configure DepartmentId as a list and set as dependent on LocationId
- Step 4. Test it! Run and see how Departments are filtered out against Location. Note, that in demo data not all of locations have departments assigned.
Sample application can be downloaded here.
You might be also interested in:
- How to insert new record in table component of ADF Desktop integration (ADFDI) Excel workbook ?
- How to update a record in ADF Desktop Integration (ADFDI) Excel workbook table component?
- How to delete a record in table component of ADF Desktop integration (ADFDI) Excel workbook?
- How to install ADF Desktop Integration (ADFDI) plugin?
- How to create ADF Desktop Integration (ADFDI) Excel workbook
- How to configure ADF Desktop Integration (ADFDI) worksheet to load data automatically on opening?
- How to pass a parameter to ADF Desktop Integration (ADFDI) excel workbook?
For more information, you can check Oracle documentation – Working with ADF Desktop Integration Table-Type Components.
ADF Version 12.2.1.0.0