Searching list with logical checkbox

[gianfior85]

I am trying to recreate a list from a grid and am having trouble replicating a search functionality I had set up with the grid's logical controls, which worked like a charm in the grid. The grid contains clients who have some combination of four products: A, B, C, and D. Some clients have all four, some have one or two, and when a logical checkbox is selected for a product in the search part, only the clients who have that product remain in the grid. So, if you check A, everyone who has A is in the grid. If you check A and B, clients who have both A and B are in the grid (but, importantly, not clients who ONLY have A and B). Likewise, when you uncheck a product, it removes that particular filter, but leaves the rest. There is also a dropdown filter for geographic counties that could further filter the grid. Pretty basic stuff.

The grid is linked to a SQL table where each product is defined with a bit field, true for yes and false for no. The following fires in an onChange event in the grid's search part for each logical field:

{grid.Object}.submitSearchForm();

I am able to recreate this filter for a list when checking one of the logical checkbox filters, however, when I do multiple checks, or when I uncheck my logical checkboxes filters for the list, it works incorrectly. When unchecking, it filters the list so that it displays only clients who do not have the product. So, if I had checked A and B, then uncheck A, rather than displaying all clients who have B (but also might have other products), it displays clients who only have B and no other products at all. Likewise, it does not incorporate the dropdown county filter properly, only when used by itself to filter the list.

The way I have set this up right now is by creating a unique Javascript Action for each checkbox (and the dropdown) called filter_A, filter_B, etc. which runs the action javascript 'Filter Records in a List Control' that only filters the one dialog and its corresponding field with search option 1. This action is set to run onChange.

2020-03-07_20-00-43.png

I have also tried combining the javascript actions into one 'Filter Records in a List Control' action and tying it all to an onClick event for a Filter button, but it doesn't help.

I have also tried the built-in list search option, but that has an issue where if I check product A, it will yield results with customers who have ONLY product A, not all customers who have product A. Changing the list's 'multiple search criteria' from AND to OR does not fix it, but rather yields no results from the checkboxes.

I am sure this is a fairly simple thing to fix but I can't quite grasp how. Thanks in advance for any assistance on this lengthy post.

[Davidk]

Re: Searching list with logical checkbox

You'd need to provide a simple UX component using static data. There are too many properties and options involved to guess at what you have set.

[gianfior85]

Re: Searching list with logical checkbox

Hi David, fair enough. However I am using server-side filters so I'm afraid I can't use static data to replicate it. I think I can pinpoint the issue and simplify the question though. Using the list debugger, I found that when the list is rendered, the following is generated:

{'page': 1, 'recordCount': 266, 'recordCountFormatted': '266', 'lastBreakValue': '', 'pageCount': 1, 'pageSize': 20, 'paginated': false, 'highlight': {}, 'whereClause': 'terminated = .f.', 'whereParameters': '', 'order': 'client_name', 'baseFilter': 'terminated = .f.', 'userFilter': '', 'sortColumn': 1, 'sortDirection': 'A'}

When I check a filter (FA__user), the list state correctly changes to:

{'page': 1, 'recordCount': 256, 'recordCountFormatted': '256', 'lastBreakValue': '', 'pageCount': 1, 'pageSize': 20, 'paginated': false, 'highlight': {}, 'whereClause': 'terminated = .f.  AND  [B]fa_user = :FA_user_fa_user', 'whereParameters': 'True|||L|fa_user_fa_user\n',[/B] 'order': 'client_name', 'baseFilter': 'terminated = .f.', 'userFilter': '(fa_user = :FA_user_fa_user)', 'sortColumn': 1, 'sortDirection': 'A'}

When I uncheck the 'FA_user' filter, the list state incorrectly changes to:

{'page': 1, 'recordCount': 10, 'recordCountFormatted': '10', 'lastBreakValue': '', 'pageCount': 1, 'pageSize': 20, 'paginated': false, 'highlight': {}, 'whereClause': 'terminated = .f.  AND  [B]fa_user = :FA_user_fa_user', 'whereParameters': 'False|||L|fa_user_fa_user\n',[/B] 'order': 'client_name', 'baseFilter': 'terminated = .f.', 'userFilter': '(fa_user = :FA_user_fa_user)', 'sortColumn': 1, 'sortDirection': 'A'}

Is it possible that upon un-checking 'FA_user', the list reverts back to its original state instead of creating a WHERE parameter of FALSE for the field?

[Davidk]

Re: Searching list with logical checkbox

Then post a sample UX with a sample table with just a few records. It'd be far easier for you to create something to test than guess at how you'd put it together and the features used.

[gianfior85]

Re: Searching list with logical checkbox

Ok no problem. Attached is a sample UX set up the same way. I'm not able to attach the sample SQL file here, but you can download it at: https://www.dropbox.com/s/weay401n2p..._test.sql?dl=0

The UX is a little stripped down in design (no dynamic images in the list) but it functions the same way for testing and will show you what I mean about the undesirable logical checkbox filtering. If you have any issues accessing the SQL table from Dropbox please let me know. Thank you.

[Davidk]

Re: Searching list with logical checkbox

Thanks for the sample... this makes it much easier.
If, for a simple example, the HR checkbox is checked, then you want to see rows where hr_user is 1
If, the HR Checkbox is not checked, then you want to see all rows. Not the rows where hr_users is 0. Is this right?

If FA and HR are checked, then you want to see all rows where FA AND HR = 1. If HR is unchecked, then you want to see all rows were FA = 1. HR no longer matters.

[gianfior85]

Re: Searching list with logical checkbox

Yes, that is exactly right on both examples. Thanks so much for looking into this, I appreciate your help.

[CharlesParker]

Re: Searching list with logical checkbox

its a "contains" search, might be the way to think about it...get the (checked) values into a string, use a js search function return true rows matching. I probably can't do it, lol but I think that would be an efficient method.

[Davidk]

Re: Searching list with logical checkbox

Yes, that is exactly right on both examples.

You may have to roll your own search on this one since you're looking for just 1 of the possible 2 values of a checkbox. I'm not sure how you'd go about using Alpha's search dialogs to accomplish it.

This is just partial code. You're going to have to flesh the rest out.

Call one search function. You don't need to call an action to call a function... just call the function directly from the onChange event. This code looks after fa_user and hr_user if either, or both, are checked.

function searchList(){

debugger;

var params = '';
var listQry = '';

var fa = {dialog.Object}.getValue('FA_user');
var hr = {dialog.Object}.getValue('HR_user');
var py = {dialog.Object}.getValue('Payroll_user');
var ht = {dialog.Object}.getValue('hosted_user');

//fa
if(fa){
	params = '1|||N|faUser\n';
	listQry = 'fa_user = :faUser';
}

//hr
if(params){
	if(hr){
		params = params + '1|||N|hrUser\n';		
		listQry = listQry + ' AND hr_user = :hrUser';
	}		
}else{
	if(hr){
		params = '1|||N|hrUser\n';		
		listQry = 'hr_user = :hrUser';
	}		
}

{dialog.object}._filterList('CLIENT',listQry,'',params);

Is there a reason you want to use a Server-side search? This makes a call to the server for every search.

[CharlesParker]

Re: Searching list with logical checkbox

doing something similar, I use contains searching a concatenated field where in your case you would add in one and then use the built in AS function to search THAT field.

[gianfior85]

Re: Searching list with logical checkbox

David, many many thanks for that code. I can certainly flesh the rest out, but what is the proper syntax for calling the function in the onChange event? I thought it was simply entering the function name in the Javascript Attribute tab 'searchList()' but that doesn't seem to work. Sorry, I'm still learning some basic syntax in Alpha and otherwise.

As for it being server-side, you're correct that it doesn't need to be. I had set it up that way in trying to solve it with Alpha's search dialogs, and the client-side filtering dialog did not do anything.

Charles, thanks so much for your helpful input as well. I see what you're saying about it being a 'contains' search. I will have to look into a bit further how to execute what you described (I'm still a bit of a novice in this respect) but the principle behind it makes sense.

[Davidk]

Re: Searching list with logical checkbox

but what is the proper syntax for calling the function in the onChange event? I thought it was simply entering the function name in the Javascript Attribute tab 'searchList()' but that doesn't seem to work. Sorry, I'm still learning some basic syntax in Alpha and otherwise.

It is simply searchList();. If you have a Javascript function searchList() in your Javascript functions section, then it will run.

[Davidk]

Re: Searching list with logical checkbox

If you want to use a client-side filter, then use .setFilter. The data, by default, is not represented the same.

var lObj = {dialog.object}.getControl('client');
//search using a single function. data for the entire current row is passed into the function
lObj.setFilter(function(data){
	debugger;
    if(data.fa_user == 'T' && data.hr_user == 'T') return true; // yes, show this row
    else return false // no, hide this row
});