Browse Table Filter Options

Questions related to using nuBuilder Forte.

Browse Table Filter Options

Postby Alohajoe5 » Thu May 02, 2019 4:14 am

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
Alohajoe5
 
Posts: 47
Joined: Tue Apr 16, 2019 9:02 pm

Re: Browse Table Filter Options

Postby kev1n » Thu May 02, 2019 7:59 pm

Do you mean something like this?
Attachments
Video_2019-05-02_143424.gif
Video_2019-05-02_143424.gif (947 KiB) Viewed 243 times
kev1n
 
Posts: 185
Joined: Mon Oct 15, 2018 2:13 am

Re: Browse Table Filter Options

Postby Alohajoe5 » Fri May 03, 2019 3:22 am

kev1n wrote: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
Alohajoe5
 
Posts: 47
Joined: Tue Apr 16, 2019 9:02 pm

Re: Browse Table Filter Options

Postby kev1n » Fri May 03, 2019 3:30 pm

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)
kev1n
 
Posts: 185
Joined: Mon Oct 15, 2018 2:13 am

Re: Browse Table Filter Options

Postby Alohajoe5 » Fri May 03, 2019 11:49 pm

kev1n wrote: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.
Alohajoe5
 
Posts: 47
Joined: Tue Apr 16, 2019 9:02 pm

Re: Browse Table Filter Options

Postby kev1n » Sat May 04, 2019 1:31 am

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.
kev1n
 
Posts: 185
Joined: Mon Oct 15, 2018 2:13 am

Re: Browse Table Filter Options

Postby Alohajoe5 » Sat May 04, 2019 1:53 am

kev1n wrote: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!
Alohajoe5
 
Posts: 47
Joined: Tue Apr 16, 2019 9:02 pm

Re: Browse Table Filter Options

Postby kev1n » Sat May 04, 2019 2:09 am

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.
Last edited by kev1n on Wed May 08, 2019 9:36 pm, edited 1 time in total.
kev1n
 
Posts: 185
Joined: Mon Oct 15, 2018 2:13 am

Re: Browse Table Filter Options

Postby Alohajoe5 » Sat May 04, 2019 3:18 am

kev1n wrote: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
 
Posts: 47
Joined: Tue Apr 16, 2019 9:02 pm

Re: Browse Table Filter Options

Postby Alohajoe5 » Sat May 04, 2019 4:33 am

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?
Alohajoe5
 
Posts: 47
Joined: Tue Apr 16, 2019 9:02 pm

Next

Return to General