How do sort on a column that contains IF widget

How do sort on a column that contains IF widget

  
I have a table where each row has either a value for MachineId or MachineGroupId, but not both.  I've created a column called "Systems" with an IF widget.  It will display the MachineId or MachineGroupId depending on whether the MachineGroupId field is null.

How do I configure the sort widget to sort on the value of the IF widget?

Thx.

Craig
Hi Craig,

I don't think you can use the List_SortColumn from RichWidgets directly in the scenario you have. For this widget, the Column property has to map to a column in the DB because it is used as the dynamic order by used by the query, so you would probably need to make a custom List_SortColumn that would allow multiple "columns" in the property to be injected into the query.

The only way I can think of you doing this without any RichWidgets changes would be to have an additional column in the table where you would synthesize its value to be a textual concatenation of MachineId and MachineGroupId for that row (one would be zero and the other would have an id). Then you could feed that "{MyEntity}.[MyIdSortColumn]" as the Column to sort.

Cheers,


Miguel
You can do that by using an advanced query. See the attached .oml file. It's a list of contacts where the third column shows emails for Sales Agents and phones for all other rows. For that to work you have to use an advanced query to do the condition inside the query instead of using an if widget in the screen, like this:

SELECT {Contact}.Name,
{Contact}.[JobTitle],
(case when {Contact}.[JobTitle] = 'Sales Agent' then {Contact}.[Email] else {Contact}.[Phone] end) as PhoneOrEmail
FROM {Contact}
WHERE {Contact}.[Name] like '%' + @SearchKeyword + '%' or @SearchKeyword is NULL
ORDER BY @OrderBy

Then in the List_SortColum web block widget for that column use "PhoneOrEmail", which is the alias given to the case column

Best Regards,
Gustavo Guerra


Thanks, Gustavo!  You've definitely helped me get on the right track.  The query I need to run references a number of other tables and the one thing I can't figure out is how to make the search bar work.  Here is my query:

SELECT {Applications}.[Name], {Applications}.Vendor, {Applications}.[Version], {Products}.[Name], {User}.[Name], 
case (when {Applications}.[MachineGroupId] = NULL then {Machines}.[Name] else {MachineGroups}.[Name] end)
FROM {Applications}, {Products}, {Machines}, {MachineGroups}, {User}
WHERE {Applications}.[ProductId] = {Products}.[Id] AND {Applications}.[MachineId] = {Machines}.[Id] AND {Applications}.[MachineGroupId] = {MachineGroups}.[Id] AND {Applications}.[Manager] = {User}.[Id]

How do I modify the WHERE statement to take into account search inputs?

Thx.
For example, if you want to search on Applications.Name and Products.Name, add this where clause:

WHERE @SearchKeyword is NULL or {Applications}.[Name] like '%' + @SearchKeyword + '%' or {Products}.[Name] like '%' + @SearchKeyword + '%'

Don't forget the is NULL or else when you don't have any search filter nothing will show up. Simple queries do that automatically for you when you specify Is Search Parameter to Yes on the query parameters that correspond to search filters.

Best Regards,
Gustavo Guerra