I followed the instructions said in this link https://success.outsystems.com/Documentation/How-to_Guides/Development/How_to_use_the_List_Sort_Column_Widget_with_a_SQL_query, to sort my Table records that is an output of my SQL Query. When I click the sort button on my table I receive an error  and when I test the SQL query I receive


Hi Wilbye Descalsota ,

Can you provide your SQL query so can find where syntax missing.


Regards

Rahul

Hi Wilbye Descalsota,

Let me start by pointing out that MariaDB is not supported by OutSystems (MySQL is, for external databases).

That being said, if you can show us your SQL tool, namely the SQL statement itself and the configurations of its input parameters, we may be able to better help you.

Hope this helps!

Rahul Sahu wrote:

Hi Wilbye Descalsota ,

Can you provide your SQL query so can find where syntax missing.


Regards

Rahul

 

 

SELECT AD.[id], AD.[first_name], AD.[last_name], AD.[primary_email], AD.[primary_contact_number],
        AD.[education], AD.[current_previous_position], AD.[current_previous_company],
        AD.[active], AD.[applicant_id], AD.[platform], AD.[platform_source], AD.[job_posting_id], C.[category], C.[level], C.[capability], C.[technologies]
        
FROM {applicant_details} AD


left JOIN {internal_tagging} IT
ON AD.[id]=IT.[applicant_detail_id]

left JOIN {categories} C
ON IT.[category_id]=C.[id]

left JOIN {job_postings} JP
ON AD.[id]=JP.[id]

LEFT JOIN {applicant_job_experiences} AJ
ON AD.[id]=AJ.[id]

LEFT JOIN {applicant_skills} AS1
ON AD.[id]=AS1.[applicant_detail_id]


WHERE    
            AD.[first_name] LIKE CONCAT('%',@FirstName,'%') 
        AND AD.[last_name] LIKE CONCAT('%',@LastName,'%') 
        AND AD.[primary_email] LIKE CONCAT('%',@Email,'%') 
        AND AD.[primary_contact_number] LIKE CONCAT('%',@Mobile,'%') 
        
        AND (C.[technologies] LIKE CONCAT('%',@Technologies,'%') OR (C.[technologies] IS NULL)) 
        AND (C.[capability] LIKE CONCAT('%',@Capability,'%') OR (C.[capability] IS NULL))        
        AND (C.[level] LIKE CONCAT('%',@Level,'%') OR (C.[level] IS NULL)) 
        AND (C.[category] LIKE CONCAT('%',@Category,'%') OR (C.[category] IS NULL) )  
        
        AND AD.[current_previous_position] LIKE CONCAT('%',@CurrentPosition,'%') 
        AND JP.[job_posting_name] LIKE CONCAT('%',@JobPostingName,'%')
        AND (if (@WithInactive = true,not AD.[active] or AD.[active], AD.[active]))
        
        AND (((AS1.[skill_name] LIKE CONCAT('%',@Skill1,'%') OR AS1.[skill_name] is NULL) 
        AND (AS1.[years_of_experience] BETWEEN @SkillMin1 AND @SkillMax1 or AS1.[years_of_experience] is NULL) 
        AND (NOT AS1.[is_deleted] OR AS1.[is_deleted] IS NULL))        
 
        OR ((AS1.[skill_name] LIKE CONCAT('%',@Skill2,'%') OR AS1.[skill_name] is NULL) 
        AND (AS1.[years_of_experience] BETWEEN @SkillMin2 AND @SkillMax2 or AS1.[years_of_experience] is NULL) 
        AND (NOT AS1.[is_deleted] OR AS1.[is_deleted] IS NULL)) 
        
        OR ((AS1.[skill_name] LIKE CONCAT('%',@Skill3,'%') OR AS1.[skill_name] is NULL) 
        AND (AS1.[years_of_experience] BETWEEN @SkillMin3 AND @SkillMax3 or AS1.[years_of_experience] is NULL) 
        AND (NOT AS1.[is_deleted] OR AS1.[is_deleted] IS NULL)))         

        AND AD.[education] LIKE CONCAT('%',@Education,'%')           
        AND AD.[current_previous_company] LIKE CONCAT('%',@CurrentCompany,'%')
        AND (AD.[asking_rate] BETWEEN @MinSalary AND @MaxSalary OR AD.[asking_rate] IS NULL)        
        AND AD.[asking_rate_currency] LIKE CONCAT('%',@SalaryType,'%')          
              
        
ORDER BY @Sort

Jorge Martins wrote:

Hi Wilbye Descalsota,

Let me start by pointing out that MariaDB is not supported by OutSystems (MySQL is, for external databases).

That being said, if you can show us your SQL tool, namely the SQL statement itself and the configurations of its input parameters, we may be able to better help you.

Hope this helps!

 

 

SELECT AD.[id], AD.[first_name], AD.[last_name], AD.[primary_email], AD.[primary_contact_number],
        AD.[education], AD.[current_previous_position], AD.[current_previous_company],
        AD.[active], AD.[applicant_id], AD.[platform], AD.[platform_source], AD.[job_posting_id], C.[category], C.[level], C.[capability], C.[technologies]
        
FROM {applicant_details} AD


left JOIN {internal_tagging} IT
ON AD.[id]=IT.[applicant_detail_id]

left JOIN {categories} C
ON IT.[category_id]=C.[id]

left JOIN {job_postings} JP
ON AD.[id]=JP.[id]

LEFT JOIN {applicant_job_experiences} AJ
ON AD.[id]=AJ.[id]

LEFT JOIN {applicant_skills} AS1
ON AD.[id]=AS1.[applicant_detail_id]


WHERE    
            AD.[first_name] LIKE CONCAT('%',@FirstName,'%') 
        AND AD.[last_name] LIKE CONCAT('%',@LastName,'%') 
        AND AD.[primary_email] LIKE CONCAT('%',@Email,'%') 
        AND AD.[primary_contact_number] LIKE CONCAT('%',@Mobile,'%') 
        
        AND (C.[technologies] LIKE CONCAT('%',@Technologies,'%') OR (C.[technologies] IS NULL)) 
        AND (C.[capability] LIKE CONCAT('%',@Capability,'%') OR (C.[capability] IS NULL))        
        AND (C.[level] LIKE CONCAT('%',@Level,'%') OR (C.[level] IS NULL)) 
        AND (C.[category] LIKE CONCAT('%',@Category,'%') OR (C.[category] IS NULL) )  
        
        AND AD.[current_previous_position] LIKE CONCAT('%',@CurrentPosition,'%') 
        AND JP.[job_posting_name] LIKE CONCAT('%',@JobPostingName,'%')
        AND (if (@WithInactive = true,not AD.[active] or AD.[active], AD.[active]))
        
        AND (((AS1.[skill_name] LIKE CONCAT('%',@Skill1,'%') OR AS1.[skill_name] is NULL) 
        AND (AS1.[years_of_experience] BETWEEN @SkillMin1 AND @SkillMax1 or AS1.[years_of_experience] is NULL) 
        AND (NOT AS1.[is_deleted] OR AS1.[is_deleted] IS NULL))        
 
        OR ((AS1.[skill_name] LIKE CONCAT('%',@Skill2,'%') OR AS1.[skill_name] is NULL) 
        AND (AS1.[years_of_experience] BETWEEN @SkillMin2 AND @SkillMax2 or AS1.[years_of_experience] is NULL) 
        AND (NOT AS1.[is_deleted] OR AS1.[is_deleted] IS NULL)) 
        
        OR ((AS1.[skill_name] LIKE CONCAT('%',@Skill3,'%') OR AS1.[skill_name] is NULL) 
        AND (AS1.[years_of_experience] BETWEEN @SkillMin3 AND @SkillMax3 or AS1.[years_of_experience] is NULL) 
        AND (NOT AS1.[is_deleted] OR AS1.[is_deleted] IS NULL)))         

        AND AD.[education] LIKE CONCAT('%',@Education,'%')           
        AND AD.[current_previous_company] LIKE CONCAT('%',@CurrentCompany,'%')
        AND (AD.[asking_rate] BETWEEN @MinSalary AND @MaxSalary OR AD.[asking_rate] IS NULL)        
        AND AD.[asking_rate_currency] LIKE CONCAT('%',@SalaryType,'%')          
              
        
ORDER BY @Sort

Wilbye Descalsota,

for this sort of query we definitely need to know the configurations of each of those input parameters, in particular the Sort input parameter.

Jorge Martins wrote:

Wilbye Descalsota,

for this sort of query we definitely need to know the configurations of each of those input parameters, in particular the Sort input parameter.

 

 As a parameter:

As an input:


Your List_SortColumn_GetOrderBy second attribute is wrong. it should be something like:

"{ApplicantSQLStructure}.[LastName]".

You can learn more here.

Hope this helps!

Jorge Martins wrote:

Your List_SortColumn_GetOrderBy second attribute is wrong. it should be something like:

"{ApplicantSQLStructure}.[LastName]".

You can learn more here.

Hope this helps!

 

The result and link that is attached is the same with my first message despite the changes.

Please close all your browser windows (and the browser itself), use a different browser, or use an anonymous window, as the wrong information is still in session and will still be used.

Jorge Martins wrote:

Please close all your browser windows (and the browser itself), use a different browser, or use an anonymous window, as the wrong information is still in session and will still be used.

 

 Tried this solution as well, didn't work

Solution

Hi Willbye,


Your List_SortColumn_GetOrderBy second attribute should be:


"AD.[last_name]"

And as Jorge mentioned, "Please close all your browser windows (and the browser itself), use a different browser, or use an anonymous window, as the wrong information is still in session and will still be used." 

I hope it helps.

Let me know how it goes. 



Cheers,

João

Solution

Hi Wilbye Descalsota,

Sorry, I was initially mislead by your output structure there, and failed to take into account the table alias you used in your SQL statement. João's solution should work.

Do note that using aliases for Entities can reduce the amount of typing you do, but also means that Service Studio will not help you with auto-completion (using CTRL+Space) of Entity or Attribute names when writing your SQL... I tend to only use it for CTEs.

João Marques wrote:

Hi Willbye,


Your List_SortColumn_GetOrderBy second attribute should be:


"AD.[last_name]"

And as Jorge mentioned, "Please close all your browser windows (and the browser itself), use a different browser, or use an anonymous window, as the wrong information is still in session and will still be used." 

I hope it helps.

Let me know how it goes. 



Cheers,

João

 

The code was accepted and also when I check it in the app, it displays the data, but when I click the column header to sort the the table by that column, I get this error.

This is the column input of my Sort by column widget

I switched it to 

and it seems to work, is my input okay?


The outputs:

after clicking column header "last name"

Hi Wilbye Descalsota,

Yes, since you are using aliases you will need to use the same syntax on the List_SortColumn parameter as you used in your List_SortColumn_GetOrderBy second input parameter, namely use your Alias name, without the curly braces. For the example on the screenshot that would be:

"AD.[last_name]"

Also, remember everytime you face issues with List_SortColumn to completely close your browser, as you now have wrong data in session that will be used during the preparation.

Glad you sorted out your issue! I’d also ask you to please mark the post that helped you solve your issue, so others can more easily find it if they face the same problem. Thanks!

Jorge Martins wrote:

Hi Wilbye Descalsota,

Yes, since you are using aliases you will need to use the same syntax on the List_SortColumn parameter as you used in your List_SortColumn_GetOrderBy second input parameter, namely use your Alias name, without the curly braces. For the example on the screenshot that would be:

"AD.[last_name]"

Also, remember everytime you face issues with List_SortColumn to completely close your browser, as you now have wrong data in session that will be used during the preparation.

Glad you sorted out your issue! I’d also ask you to please mark the post that helped you solve your issue, so others can more easily find it if they face the same problem. Thanks!

 Thank you. this was fun.

 

I have a follow up question, How do I implement this on multiple columns?

Hi Willbye,


You can use multiple sort options, in the end you only need to provide the correct SQL syntax for your query.

So if you want your LastName column to sort by First and Last name, your List_SortColumn input should look something like this:

"AD.[first_name], AD.[last_name]"


Then it's up to you to organize the screen and use the widgets to your use case.

As a hint, in order to test quickly without publishing if your syntax is ok, you can test your values in the query itself, going to Tab Test Inputs and filling the Sort parameter there:

and then click on Test:


Hope it helps.


Cheers,

João

Wilbye Descalsota wrote:

I have a follow up question, How do I implement this on multiple columns?

If you mean multiple Table Record's columns, it's as simple as dragging and dropping another List_SortColumn to other header cells and filling their Column input parameter with the right value (for instance "C.{category}"). Clicking on different header that have a List_SortColumn will change the sorting criteria based on the header's List_SortColumn input. Clicking again the same header will revert the order (instead of A to Z, it will then be from Z to A, and then again A to Z, and so forth).

If you want to click on a header and sort by multiple entity attributes at the same time, then it's as João mentions, you can use multiple attributes (using the same syntax as previously) separated by commas in your List_SortColumn input parameter.

Be warned however that if you do this, clicking again on the header to reverse the sorting order will only affect the last column. This means that if you have, for instance, "AD.[first_name], AD.[last_name]" as the List_SortColumn input parameter, when you click that table column's header for the first time if will sort first by first name, and then by last name, both ascending (from A to Z), when you click the same header again, it will sort first by first name ascending (from A to Z), and then by last name descending (from Z to A), and will continue to swap between these two sort orders only.

Hope this helps!

Cheers!