Browse Table Filter Options Topic is solved

[Alohajoe5]

I'm trying to create an option to view a table and quickly filter through records. I've got a set of browse & edit forms, and I'm recreating them as browse forms. I am trying to figure out how to add something like a drop-down filter for specific columns at the form table level view (not in the individual records). For example:

1.) Open a form "Data", go to a column--(perhaps named Channel)
2.) Have a drop down or something similar on the column named "Channel"
3.) **Select 1**
4.) Now only records with channel=1 are displayed in the table.


How would I do this? I found out how to create a drop-down in the record edit page but not the table view.

Thanks

[kev1n]

Do you mean something like this?

[Alohajoe5]

Do you mean something like this?

Yes, I can't tell if that's supposed to be a form builder or if that's the displayed table when clicking on a form; but if that gif of type and Input are columns on the displayed table of a form then that's exactly what I'm looking for. How did you get the drop-down selections in that location and how did you populate them with data?

Thanks

[kev1n]

By "table view" do you mean a Browse Form or a Browse Form that is embedded in an Edit Screen?

My example shows a Browse Screen (The one that can be found under Setup --> Objects)

[Alohajoe5]

By "table view" do you mean a Browse Form or a Browse Form that is embedded in an Edit Screen?

My example shows a Browse Screen (The one that can be found under Setup --> Objects)

I mean I created a browse & edit form. When I launch this form I'm brought to a page that shows a table of records with pagination at the bottom. I would like for this page with a table of records to have the ability to filter and sort as you've shown.

[kev1n]

One more question: Will the drop down list have static or dynmaic content?
Static: values like item1, item2, item3
Dynamic: the drop down list contains unique distinct values from a column.

[Alohajoe5]

One more question: Will the drop down list have static or dynmaic content?
Static: values like item1, item2, item3
Dynamic: the drop down list contains unique distinct values from a column.

I'm assuming I could do it either way, for example If I had a column of tv channels--I could make the drop down static values--lets say 1,2,5,20,24,60 or it could be dynamic and pick up the unique values in the column. I guess if I had to pick, I would prefer it be dynamic that way if new values appear I wouldn't have to go hard code them into the drop down. Thank you for the help and please tell me how to get these drop-downs!

[kev1n]

Ok, here we go: You need to have some knowledge in Javascript ( / PHP for dynamic content)

Adds two dropdowns to the Setup --> Objects Form:

1. In your form's JavaScript field, add:

Code: Select all

if (nuFormType() == 'browse') {

    var data0 = ["", "input", "word", "image"]; // static values to be added to the dropdown    
    addBrowseTitleDropDown(0, data0); // add dropdown to column 1 (index 0)

    var data1 = ["", "text", "nuDate", "nuScroll"]; // static values to be added to the dropdown    
    addBrowseTitleDropDown(1, data1); // add dropdown to column 2 (index 1)

}

// Function to add a dropdown to a title of a Browse Screen 
// * @param {number} index - browse index where the dropdown should appear
// * @param {object} data -  array to populate the dropdown
function addBrowseTitleDropDown(index, data) {
	
	var dropId = "nuBrowseTitle" + index + "_dropdown";

	var list = document.createElement('select');
	
	list.setAttribute("id", dropId);
        var w = nuCurrentProperties().column_widths[index] - 10;
	list.setAttribute('style', 'width:'+ w +'px');


	for (var i = 0; i < data.length; i++) {
		var opt = document.createElement('option');
		opt.innerHTML = data[i];
		opt.value = data[i];
		list.appendChild(opt);
	}

	// append select to the browse title
	$('#nuBrowseTitle'+index).append('<br/>').append(list);

	$('#'+dropId).on('change', function (e) {
	//	var optionSelected = $("option:selected", this);
		nuSetProperty(this.id,this.value);
		nuSearchAction(1);
	});

	$('#nuBrowseTitle'+index).on('mousedown' , '> select' , function(e){
		e.stopPropagation();
	});

	 var dropValue = nuGetProperty(dropId);
	 $("#"+dropId).val(dropValue);
}

2. Modify the SQL (Pay attention to the where-clause)

Code: Select all

SELECT * FROM zzzzsys_object
JOIN #TABLE_ID# ON zzzzsys_object_id = theid
JOIN zzzzsys_tab ON zzzzsys_tab_id = sob_all_zzzzsys_tab_id
JOIN zzzzsys_form ON zzzzsys_form_id = syt_zzzzsys_form_id 
WHERE 

((sob_all_type = '#nuBrowseTitle0_dropdown#' AND LOCATE('#', '#nuBrowseTitle0_dropdown#') <> 1 ) 
OR '#nuBrowseTitle0_dropdown#' = '' OR LOCATE('#', '#nuBrowseTitle0_dropdown#') = 1)

AND

((sob_input_type = '#nuBrowseTitle1_dropdown#' AND LOCATE('#', '#nuBrowseTitle1_dropdown#') <> 1 ) 
OR '#nuBrowseTitle1_dropdown#' = '' OR LOCATE('#', '#nuBrowseTitle1_dropdown#') = 1)

If you can get this to work, I'll also show you how to dynamically fill the dropdown.

[Alohajoe5]

Ok, here we go: You need to have some knowledge in Javascript ( / PHP for dynamic content)

Adds two dropdowns to the Setup --> Objects Form:

1. In your form's JavaScript field, add:

Code: Select all

if (nuFormType() == 'browse') {

    var data0 = ["", "input", "word", "image"]; // static values to be added to the dropdown    
    addBrowseTitleDropDown(0, data0); // add dropdown to column 1 (index 0)

    var data1 = ["", "text", "nuDate", "nuScroll"]; // static values to be added to the dropdown    
    addBrowseTitleDropDown(1, data1); // add dropdown to column 2 (index 1)

}

// Function to add a dropdown to a column
function addBrowseTitleDropDown(index, data) {

    var dropId = "nuBrowseTitle" + index + "_dropdown";

    var list = document.createElement('select');

    list.setAttribute("id", dropId);
    list.setAttribute('style', 'width:' + nuCurrentProperties().column_widths[index] - 10 + 'px');

    for (var i = 0; i < data.length; i++) {
        var opt = document.createElement('option');
        opt.innerHTML = data[i];
        opt.value = data[i];
        list.appendChild(opt);
    }

    // append select to the browse title
    $('#nuBrowseTitle' + index).append('<br/>').append(list);

    // add a change event handler to the dropdown 
    $('#' + dropId).on('change', function(e) {        
        nuSetProperty(this.id, this.value);
        nuSearchAction(1);
    });

    $('#nuBrowseTitle' + index).on('mousedown', '> select', function(e) {
        e.stopPropagation();
    });

    var dropValue = nuGetProperty(dropId);
    $("#" + dropId).val(dropValue);
}

2. Modify the SQL (Pay attention to the where-clause)

Code: Select all

SELECT * FROM zzzzsys_object
JOIN #TABLE_ID# ON zzzzsys_object_id = theid
JOIN zzzzsys_tab ON zzzzsys_tab_id = sob_all_zzzzsys_tab_id
JOIN zzzzsys_form ON zzzzsys_form_id = syt_zzzzsys_form_id 
WHERE 

((sob_all_type = '#nuBrowseTitle0_dropdown#' AND LOCATE('#', '#nuBrowseTitle0_dropdown#') <> 1 ) 
OR '#nuBrowseTitle0_dropdown#' = '' OR LOCATE('#', '#nuBrowseTitle0_dropdown#') = 1)

AND

((sob_input_type = '#nuBrowseTitle1_dropdown#' AND LOCATE('#', '#nuBrowseTitle1_dropdown#') <> 1 ) 
OR '#nuBrowseTitle1_dropdown#' = '' OR LOCATE('#', '#nuBrowseTitle1_dropdown#') = 1)

If you can get this to work, I'll also show you how to dynamically fill the dropdown.

Thank you. I'm going to either try this later today or I will when I go in on Monday. I'll get back to you then! Thank you very much for the help so far.

[Alohajoe5]

So I added part one by opening my form, clicking on options---> Form Properties--->Custom Code. I added it to column 3 so I edited it to read:
vardata3={"", "1", "2", "3", "4"....etc];
addBrowseTitleDropDown(3, data3);


I checked after I added all the javascript, when I open the form no dropdown appears. Do I have to add something else or will it only appear after I edit the SQL? Also, where am I editing the SQL?