AddColumns using with If function and variables

[TiphaineFt]

Hello everybody,

I need your help in the app I'm developping 😉

I want to create a selector where my user can select 3 criterias (in more than 10 columns name) via dropdown list. Then the user can select from each criteria the value searched (via a new dropdown list).

The app should then show a gallery sorting my items by closeness (1 if true, 0 is false for each criteria) to the customer requirements. Enclosed an example of what I'm trying to do.

Important : I don't want to Filter my items as I still want to see all the results and not only those which are fitting my 3 criterias.

I can make the calculus with labels inside each item (which is already great) but I cannot sort the gallery by the values obtained in the label. Is there a way?

To make it work, I'm thinking of creating a new Column and sort by it in my new tab. But I am not figuring how to do it since my values are text that I want to convert in 1 or 0.

Here is the code idea:

UpdateContext({TableauSort:AddColumns (Tableau1;"Criteria1"; If(Dropdown4.Selected.Value="Professor";1;0))})

If you have any idea to solve my problem that would be perfect!

Many thanks in advance

[v-bofeng-msft · Accepted solution]

Hi @TiphaineFt :

Your needs may be difficult to achieve, because most functions do not support field names stored in the form of variables.(The only way I can think of is to use a three-level nested switch to list each possibility)

If you need this feature, I suggest you make some adjustments to the app's features.Here my solution:

1. Create more than 10 dropdowns for each field to select the value for the user.

2.Set the gallery's items property to

Sort(YourDataSource,
     If(
       IsBlank(Dropdown1.Selected), /*If Dropdown1.Selected is empty, it means that this column is not used as one of the filter conditions, and the default value is 1*/
       1,
       If(Column1=Dropdown1.Selected.Value,/**/
          1,
          0
       )
     )+/*Perform the same judgment on each field that may participate in the screening, and add their results to get the sorting weight*/
     If(
       IsBlank(Dropdown2.Selected),
       1,
       If(Column2=Dropdown2.Selected.Value,
          1,
          0
       )
     )+
     If(
       IsBlank(Dropdown3.Selected),
       1,
       If(Column3=Dropdown3.Selected.Value,
          1,
          0
       )
     )+
     If(
       IsBlank(Dropdown4.Selected),
       1,
       If(Column4=Dropdown4.Selected.Value,
          1,
          0
       )
     )
........,/*Sort according to the last obtained sort weight*/
Descending
)

If you need to beautify the interface, you can use a dropdown to list all the field names for the user to choose, and then set other dropdown visible to make the filter of the field not selected by the user invisible.

Best Regards,

Bof

[TiphaineFt]

Thank you very much Bof, it's working!

It will not be the most beautiful line of code but at least its working 😉

I hope they will soon tackle the topic of dynamix strings in the column names, that would really facilitate some functions.