Page 1 of 1

Custom Filters

Posted: 2019-04-12 16:49
by uchhavi
Hello,

I am creating a custom filter page by seeing the tutorial from udemy.

I am facing an issue.

Table Name: enquiries
Table Field: co_type
This field I am autofilling from another table companies, when the user selects the company, its category is autofilled.

Issue:
I have used the below code as suggested in the udemy:
Issue is that one category name comes multiple number of times... attaching the error


<script>
$j(function(){
/* display a drop down of User Category that populates its contents from ajax_combo.php */
$j('#CategoryDropDown').select2({
ajax: {
url: 'ajax_combo.php',
dataType: 'json',
cache: true,
data: function(term, page){ return { s: term, p: page, t: 'enquiries', f: 'co_type' }; },
results: function(resp, page){ return resp; }
},
width: 200
}).on('change', function(e){
$j('#CategoryID').val(e.added.text);
});

/* preserve the applies category filter and show it when re-opening the filters page */
if($j('#CategoryID').val().length){
$j.ajax({
url: 'ajax_combo.php',
dataType: 'json',
data: { s: $j('#CategoryID').val(), p: 1, t: 'enquiries', f: 'co_type' }
}).done(function(resp){
if(resp.results.length){
$j('#CategoryDropDown').select2('data', {
id: resp.results[1].id,
text: resp.results[1].text
});
}
});
}
})
</script>

Re: Custom Filters

Posted: 2019-04-12 20:58
by pböttcher
Hi,

as a guess, in the page you show, there is no category-id but you refere to that this should be the selected Item to use for the auto-fill,
so your query joins all companies to all categories which give these multiples.
What would you like the user to see here? For what would this entry be used?

Re: Custom Filters

Posted: 2019-04-13 08:52
by uchhavi
Dear pböttcher,

Yes you are right. I my enquiries table I have a field company_id (which i am selecting from the table companies and the another field user category (which I am autofilling from the companies table based on the company user selects.

I would like the users to see unique values of the category id... in the customised filter page
so Consultant should come only one time and so should the other values in that field....

Please can you advise what changes I can make in the code to do it.

Re: Custom Filters

Posted: 2019-04-15 11:16
by uchhavi
Please help me solve this.....

uchhavi wrote:
2019-04-13 08:52
Dear pböttcher,

Yes you are right. I my enquiries table I have a field company_id (which i am selecting from the table companies and the another field user category (which I am autofilling from the companies table based on the company user selects.

I would like the users to see unique values of the category id... in the customised filter page
so Consultant should come only one time and so should the other values in that field....

Please can you advise what changes I can make in the code to do it.

Re: Custom Filters

Posted: 2019-04-15 17:28
by pböttcher
Hi,

can you post the query you have in the lookup for the field where the user selects the company.

Re: Custom Filters

Posted: 2019-04-16 08:11
by uchhavi
Hello,

I have attached the screen shot of the Enquiry table, where we are capturing the company from the user.
Based on the selection that is done by the user, the company category/ type as well as the location/ country of the company is autofilled.

Now in the customised search section, I want to filter the enquiry table using company category as well as company location.
I used the code that was shown in the Udemy.. but in the dropdown ... i am getting multiple values.. as I have shown already.

I want to get unique values... i have already shown the picture of how my search query works.

Please can you help me solve this.

Re: Custom Filters

Posted: 2019-04-16 18:28
by pböttcher
Hi,

thanks, can you show which fields map to the CATEGORY and COUNTRY in you APPGini Setting, also can you post the SQL statement.
Unbenannt.GIF
Unbenannt.GIF (57.81 KiB) Viewed 93 times

Re: Custom Filters

Posted: 2019-04-17 12:53
by uchhavi
Hi

I am attaching a file that shows the enquirires table in the appgini database.

There is no sql query for this... I am hoping this is what u have asked for.

Re: Custom Filters

Posted: 2019-04-17 19:32
by pböttcher
Hi,

sorry to ask again,

can you please post the SQL for the company_id field, as I assume that this is the one which causes the auto-fill.

Re: Custom Filters

Posted: 2019-04-18 06:45
by uchhavi
Hi,

I have pasted the code below

SELECT `companies`.`id`, `companies`.`name` FROM `companies` LEFT JOIN `user_categories` as user_categories1 ON `user_categories1`.`id`=`companies`.`user_cat_id` LEFT JOIN `countries` as countries1 ON `countries1`.`id`=`companies`.`country_id` ORDER BY 2

Re: Custom Filters

Posted: 2019-04-18 10:07
by pböttcher
Hi,

can you change the SQL to :

SELECT `companies`.`id`, `companies`.`name` FROM `companies` ORDER BY 2

and try again.

Why do you need user_categories and countries in your SQL?

Re: Custom Filters

Posted: 2019-04-19 05:39
by uchhavi
Hi,

I did the above but no change. The dropdown is still showing duplicate values.

Is the code that I use okay?
I have used 3 same dropdown codes on the same file. only changing the DropDown name and ID.