I know my lookup is large, but is there a way around this:
And even something that would tell the user that it is still working?
That message would be coming from the browser, and if the lookup is taking so long that the browser views the page as unresponsive, then it may be worthwhile to rethink your approach.
How many rows/columns are being returned in this lookup?
Up to 100 or so rows of 8 columns of information.
I'm thinking maybe I'll import just one column and NOT have it populate all rows, then have the user select the primary key row and let it populate one at a time with the primary key.
I may have to rethink, because doing it that way, I can't add new rows with brand new information because the lookup will be looking for that primary key to populate and it won't let me add new information.
I can say from experience that 100 rows with 8 columns is definitely going to cause slowness issues; basically, it's not just the rows, you're pulling 800 "items"
In my experience, it is kind of a cumulative thing. I've had forms that pull more than 100 rows no problem, but only with or two populated columns.
If you don't mind me asking, what is the use case for pulling back so much information on the form? Is there a reason the users need to see all of it at once?
Scale inspections at locations that have over 100 scales each. Not the norm, but it happens a few times a year when scale inspections are due. Would like all inspections on one form instead of a different form for each inspection.
That's fair. I had a budget allocation type form that had similar issues because users needed to set values for as many as 200 employees.
What I ended up doing to "work around" the issue with retrieving that much data was to create a view that concatenated the columns with pipe separators.
So, instead of populating every individual data item, I populated a hidden column with every value from the row.
Then, I used JavaScript in the Lookup Complete event to split on the separator and move each value into the appropriate column.
For example,
$(document).on('lookupcomplete',function(event){
// check the trigger field or rule
if(event.triggerId == 'Field48'){
// run function to populate data columns
formUpdate(500);
}
});
function formUpdate(n) {
// Check table row count
var rows = $('.dataTable .propCount').eq(0).val();
// Timeout to account for delay between lookup and data population
setTimeout(function(){
// iterate through each row of the concatenated lookup column
$('.dataLookup input').each(function(index){
var row = $(this).parents('tr');
var data = $(this).val().split('|');
$.each(data, function(index, value){
// assign to the target column
// custom class set on each field to match data index
// for example, item0, item1, item2, etc.
row.find('.item' + i + ' :input').val(value).change();
});
});
// remove concatenated data column
$('td[data-col="q58"], #q58').remove();
// make desired fields readonly
$('.readOnlyField input').attr('readonly','true');
},(rows*n)); // set variable timeout based on rows
}
In the SQL view you can use CONCAT_WS to mash it together with your set delimiter so you're lookup is only working with as little as 1 column.
SELECT CONCAT_WS('|',[Column1],[Column2],[Column3],[Column4])
FROM [dbo].[your_table]
How did that work out? How quickly does the form load?
Honestly, it's still pretty slow, but it was workable for what we were doing.
One other possibility, since it sounds like this may be a scheduled activity, is that you could pre-load the data rather than using lookups.
For example, we have a review process that includes hundreds of rows of data that occurs about once a month.
A lookup is totally out of the question for that because it's just too slow, but since we know when it needs to occur, I did the following instead.
A workflow runs when a new review is required. The workflow pulls all the data from the table then invokes the form process with the table prepopulated.
Instead of having to rely on a form lookup, the users just get a task assigned to them personally, or to a team, that already has the table built.
That sounds interesting. I haven't started a form from workflow yet.
It can be a great way to go if this is a form they have to fill out regardless (i.e., you don't need to worry about people choosing not to do them).
Alternatively, you could still have the users kick things off this way:
The user would have some waiting to do while the workflow runs, but it would still be a lot faster than loading the data into the form.
In the workflow, you would:
The only thing you'd need to worry about using either of these approaches is people abandoning the active tasks/instances.
To get around that, you could include timers to automatically end the active instance after a certain amount of time so they "expire" on their own.
Katy, are you by chance saving the form and re-loading it in other user tasks, as opposed to filling it out, saving it, and being done with it?
I once worked on a process that never really ended, we would just write data to a DB and then go back to the user task to wait for a later update. This led to some incredibly long page load times; I eventually had to remake the process so that it would end rather than re-loading the user task.
The data is already in a database. I am just trying to pull the data into a form, but there are a few of the selections that will pull up to 101 rows in a collection. I think there is too much information in the collection for each row. I am rethinking how to do this. If I have the user select each say device from the first field in the collection, it fills pretty quickly, but if I pull in all devices into all collections at the same time, it is not loading.