Complex Table Search - using "Contains"

[michelm_de]

Hi,

When using a complex table search control to select one value from a complex table, there is a lack of a “contains” search. The user is only able to search the fields (related to the selected index) using a  “begins with” search. Now, I’m looking for a workaround …
But the contains search is working in a List Tile View, so one idea would be to create a new screen where I dynamically create a collection of objects (each complex table line will be one object) and use the List Tile View. But, as we have about 5000-10000 entries in our complex table, I’m not sure if the List Tile View can handle so many objects and then search within?

Do you have other ideas to search within a complex table with “contains”  to get a value?

Best Regards,

Michel

Tags edited by: Michael Appleby

[bill_froelich · Accepted solution]

Michel,

The problem with contains is performance.  A contains search must iterate over every record in the complex table and do a wildcard find within each record looking for a match.  While this is ok on a small set of records like a tile list, for a complex table where the number of records could be in the hundred thousands it becomes a problem.

The best option I can suggest is to use a temporary collection to hold the results and have the user input their critera and click a search button to run an action to loop over the complex table looking for matches and adding them to the temporary collection that can then be displayed as resutls in a number of ways depending on your need.

The key it to make sure your users understand there will be a wait depending on the number of rows.  This also only works on detail screens and not within a transaction since you can't run a secondary action while the transaction is in progress.  You need to make sure to setup yopur screen flow accordingly.

--Bill

[Marçal_Oliveras · Accepted solution]

Hi Bill,

I'm investigating with this too. I would like to have wildcard search for Functional Locations, for instance when creating a new notification, so it's a transaction screen.

I was wondering if OpenUI (I don't have any technical knowledge about it yet) may be able to some how make a copy of the complex table and then use native code to do this kind of search.

Do you think it's possible?

[bill_froelich · Accepted solution]

Currently Open UI does not have access to Complex Table data.  Consequently you would still have to copy everything into an object collection first before an openui control would help here.

I would recommend against that approach for performance reasons.

--Bill

[Marçal_Oliveras · Accepted solution]

OK, thank you Bill.

I'll investigate if a solution like the one you proposed is suitable for us.

[michelm_de · Accepted solution]

Hi Bill,

just for the records ... I tried the solution with creating a collection for all entries within my complex table. The complex table contains 16000 entries and it took about 3 minutes to transfer this CT to my collection of objects. The search using contains is working as we need it for the project but is also a bit slow.

To sum up: For small amount of data (less 500) it might be a workaround to use an object collection, but for large data it's to slow.

Regards,

Michel

[bill_froelich · Accepted solution]

Michel,

I am not surprised as that is a HUGE object collection and way beyond what we would ever recommend.

Have you looked at my suggestion of iterating over the Complex table but only copying matching results into the temporary collection?  You will still have to loop over all 16000 records but you are only copying over those that match the search criteria.  You may also look to include an escape clause once you hit certain limit of search resutls (say 50 or 100) to help with performance.

--Bill

[stephen_kringas · Accepted solution]

Hi Marcal,

Did you manage to get anywhere with this? We currently have the same requirement with the Functional Location search which you mentioned earlier.

Bill - Have the been any changes in the SDK that allows for Wildcard / Contains searches on Complex Tables?

Thanks,

Stephen

[bill_froelich · Accepted solution]

Stephen,

Nothing yet on the SDK front for accessing a CT from OpenUI.  I am still hoping for it in a future release.

--Bill

[Marçal_Oliveras · Accepted solution]

Hi ,

I tried 2 approaches:

• A loop action with a Javascript executing rule using regular expressions for each CT entry. The matching records are added to an object collection. This approach does exactly what we want but the performance in iOS was aprox. 30 seconds for 20.000 functional locations  and in Android the time was 1000% worse. Actually the client was crashing with more than a few hundreds of entries... So I had to look for something else. If you are interested in test this solution I can share some details. Maybe with more powerful devices and recent Agentry client release the performance has improved, who knows.
• Pseudo wildcard search. Similar logic but user enters some basic mandatory parameters like plant and section (in order to reduce the matching entries as much as possible) and a string(s) field for texts fields. It can't contain *, it will only search if the text is contained in the description, FL name or another relevant text field, but the good news is that you can have multiple fields. Then you add the matching entries to an object collection, the execution rule is not a Javascript rule, it's a regular Agentry rule comparing plant and section fields directly and using RFIND for texts. Finally a reduced tile list is presented to the user. Here he only needs to pick the right entry. Remember to initialize collection and search parameters before any search.

[stephen_kringas · Accepted solution]

Thanks for the suggestions Marcal.

Cheers,

Stephen