Chaining parameters in HTML for large data sets

[Diana Watkins]

I have an HTML that shows several drop-downs related to an employee. The first drop-down allows the user to select an employee from the list and the ones below it populate based off of the selected employee in the first drop-down. I did this by chaining the sub-sequent drop-downs to the first. The employee select list is generated by an embedded request and each drop-down is an Explicit request off of that. 

The drop-downs populate like they should, but it takes quite a long time (upwards of a minute) to populate them all. It's like it's running the embedded request for each one as the more I chain to the first, the longer it takes. Is there a way to chain the drop-downs to the first without running the embedded request for each one? 

[David Beagan]

The Employee Name drop down lists all the emploee's names I assume. And does each of the other dropdowns, before you make a selection, show a full list, all Employee Nums, all Titles, all Departments, etc. ?

[Diana Watkins]

Hi David! So, I made a change yesterday and changed the drop-downs to edit boxes - thinking that the drop-downs were being populated with the full lists as well and that might be part of the problem. It still took around a minute to populate all of those edit boxes once the employee name was selected. Since those will never be manually selected I just left them as edit boxes. 

[Martin Yergeau]

Chaining is a great way to populate controls but the more you have chained and/or the larger number of data each control has, the longer it takes to have them populated.

There is option using js but it needs you to code.

An option that you can try is to check the option "Cache Run Time Data" for each involved controls. At first load it will takes longer to  populate but then it's going to be faster while you stay in that report

Edited April 4 by Martin Yergeau

[Clayton Peacock]

We encountered a similar scenario involving a large workforce of over 30k employees. To address this, we implemented the following changes:

Employee Name Search
We replaced the droplist/combobox with a text box - free text or consider typeahead custom solution 
The text box performs a fuzzy search on employee names, allowing for more flexible and accurate searches.

Conditional Loading of Supervisor and Class
In your example, the Supervisor and Class fields load dynamically only after the user selects a specific Department(onChange event). This approach optimizes performance by fetching relevant data only when necessary to load Supervisor/Class which will be a smaller subset of data.
 
Performance Optimization FOCCACHE File Creation:
Consider creating the necessary files in FOCCACHE before the HTML loads so they wait only once for the unique values, these files can be referenced by your filters, leading to potential speed improvements.

ReportCaster Job for XFOCUS Table:
Alternatively, can run a daily ReportCaster job to create an XFOCUS table for faster data retrieval with unique combinations for controls.

We use JQuery to create our magical filters. 

Edited April 4 by Clayton Peacock

[David Beagan]

Diana, I'm not sure that changing them to edit boxes stops it from doing all the work. 

I wonder if chaining Employee Name directly to Title, Department, Supervisor and Class rather than all in a line (assumption on my part) maybe it would make some difference. 

[David Beagan]

I used the employee sample table, put something together:

The first dropdown has empid_dd.fex to populate it. Each of the others get populated by the other one emp_dd.fex. 

Like this so that it doesn't try to pull the other ones until an EMP_ID is selected.

And then chained like this:

When I run the page, it works but it is a bit slow. Seems like nearly a second to populate the boxes after selecting an EMP_ID. There are only about 19 records in employee.

 

[Diana Watkins]

On 4/4/2024 at 10:26 AM, Clayton Peacock said:

ReportCaster Job for XFOCUS Table:
Alternatively, can run a daily ReportCaster job to create an XFOCUS table for faster data retrieval with unique combinations for controls.

This worked great! Way better response time - thank you!