Filter and Sort Calculated column in Gallery

[SYED_786]

I have a gallery which is connected to a collection, Now in the gallery I have column named Final rank which is a text input where I’m Adding the value of Autorank text input and PMO text input so here the value is 1 by default and these values so now I need to filter these final rank column 

since it only filters the data that is saved in backend and it doesn’t filter the other example if I select 1 from drop-down it shows the only the rows that has 1 which are saved in database but not the rows that have 1 in text input how to achieve this pls help me out!!

[v-mengmli-msft]

Hi @SYED_786 ,

Have you patched value of Final rank textinput to database?

You need to update the data in the gallery to the database first and then filter it.

OnChange of Autorank text input and PMO text input:
Patch(database,ThisItem,{AutorankColumn:Self.Text,PMOColumn:PMOtextinput.Text,FinalrankColumn:FinalrankTextinput.Text})

Items of gallery:Filter(database,FinalrankColumn=dropdown.Selected.Value)

Best regards,

Rimmon

[SYED_786]

Hi @v-mengmli-msft 

Yes I’m patching it on hit of save icon but it will sort and filter the data which is present in the database then what about the values that are present by default in final rank text input?? Need to filter them too 😬 pls help!!

[v-mengmli-msft]

Hi,

What does the default in final rank text input mean?

If you have not saved default in final rank text input in database, you cannot filter the unsaved value absolutely.

Could you please show me some screenshots?

[SYED_786]

Hi @v-mengmli-msft 

In the below screenshot you can see we have dropdowns  followed by autorank and final rank now in the final rank textinput it is a calculated column where we are adding the values of 2 textinputs Autorank + pmo adjustment 

The Autorank is also calculated column where the calculation is based on the dropdowns OTD,LD etc... the formula in Autorank textinput is below 

So here based on the dropdowns the default value in final rank and autorank is zero now i need to sort and filter how to achieve since we are saving only few values on hit of save but this values have by default zero how to sort or filter them?? Pls help

[v-mengmli-msft · Accepted solution]

Hi!

Sorry for late reply.

I assume that there is a control in the item of galley which has a code like this:

Patch(database,ThisItem,{ColumnName:Value})

So, the code is resulted to only record which has been edited can be updated to database, including value in Autorank text input. But for default value in Autorank text input of one item which has not been edited has not been saved into database.

I suggest you update all value in Autorank text input to database firstly when the screen appears.

OnVisible of Screen:ForAll(Gallery.AllItems As C,Patch(Database,LookUp(Database,ID=C.ID),{autoRankColumn:C.AutorankTextInput.Text}))

Then you can use the drop down to filter the zero in autorank column:

Items of gallery:
If(IsBlank(dropdown.Selected),Sort(database,"ColumnNameForSorting",SortOrder.Descending), Sort(Filter(database,autoRankColumn=dropdown.Selected.Value),"ColumnNameForSorting",SortOrder.Descending))

If my answer solves your question, please accept it as a solution.

If you have another question, you can post it in community to get instant solutions.

Thanks!😀

Rimmon

[SYED_786]

Hii @v-mengmli-msft 

I have tried the approach of patching on visible of the screen, but it is not working is there any approach?

But you can see the final rank dropdown cannot capture the other values. 

[v-mengmli-msft]

Hi!

Have you tried switching to another screen and back to this one? the code on the visible property works when the screen appears.

[SYED_786]

Yes I did but the app went to loading and nothing happens ☹️

[v-mengmli-msft]

It's weird.🤔

I can currently think of two possibilities for this code to fail:

1.The LookUp function did not find a record that matches the condition.

2.You have other code in the screen about this collection and are using the ClearCollect function.

Please put the code on a button in the screen and test.