Sorting Gallery based on calculated Value

[Danny_Dicaprio]

Hi ,

I have a Gallery with Items Property 

Filter(
Top_Risk_Controls,
VarTopITrisknames in Risk_name.Value
)

And a Label Inside the Gallery with following Text Property

If(
IsEmpty(
Filter(
Linked_Risks_Causes_Controls,
VarTopITrisknames = Risk_name.Value && ThisItem.Title = Control.Value
)
),
"0%",
Sum(
Filter(
Linked_Risks_Causes_Controls,
VarTopITrisknames = Risk_name.Value && ThisItem.Title = Control.Value
),
'Cause: Impact'.Value * Potential_impact_cause/100*10000) & "%"
)

 I want to Sort gallery based on Label71_1 value,

I tried the following formula to sort the Gallery. But it's not working. 

Sort(Filter(
Top_Risk_Controls,
VarTopITrisknames in Risk_name.Value
),Label71_7,SortOrder.Ascending)

The above formula is not working 

could anyone please help me to fix the issue

[mmbr1606]

hey @Danny_Dicaprio 

can u try this please:

ClearCollect(
    NewTopRiskControls,
    AddColumns(
        Top_Risk_Controls,
        "CalculatedValue", 
        Sum(
            Filter(
                Linked_Risks_Causes_Controls,
                VarTopITrisknames = Risk_name.Value && Title = Control.Value
            ),
            'Cause: Impact'.Value * Potential_impact_cause/100*10000
        )
    )
)

This code assumes you can replicate the calculation you were doing inside the label but do it for each item and add it as a new column to each item in your Top_Risk_Controls. The name "CalculatedValue" is an example; you can name this anything relevant.

Set your gallery's Items property to the new collection, NewTopRiskControls.

Sort(NewTopRiskControls, CalculatedValue, Ascending)

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.



Greetings

[Danny_Dicaprio]

@mmbr1606 

Thanks for the reply

I have some doubts, where do u want me to update the formula for the create collect. 

How am I going to do it for each item like, should it be in the on select or visible property of the screen?

[mmbr1606]

yes u can try it in the onvisible of the screen

[Danny_Dicaprio]

Hi @mmbr1606 

Thanks again ,But it is showing some error

what would be the reason  ? can you please hep me on this 

[mmbr1606]

The error message you're encountering, "error when trying to retrieve data from the network: fetching items failed. Possible invalid string in filter query," suggests that there's an issue with how the data is being queried or processed, possibly due to how the filter query is structured or how certain fields are being accessed or used within that query. This error can occur in several scenarios, but given the context of using the Sort function on a collection created with ClearCollect and AddColumns, let's consider potential causes and solutions:

First, let's simplify the collection and calculation process. This step involves creating a collection without immediately adding the calculated value. The goal here is to ensure we can collect the data successfully before adding complexities. 

 ClearCollect( BasicTopRiskControls, Top_Risk_Controls ); 

 After ensuring that Step 1 works without errors, let's attempt to add the calculated values in a separate step. This approach helps isolate issues and makes debugging easier.

 ClearCollect( NewTopRiskControlsWithCalc, ForAll( BasicTopRiskControls, Let( varCurrentItem := ThisRecord, { OriginalRecord: varCurrentItem, CalculatedValue: Sum( Filter( Linked_Risks_Causes_Controls, varCurrentItem.Risk_name.Value = Risk_name.Value && varCurrentItem.Title = Control.Value ), 'Cause: Impact'.Value * Potential_impact_cause/100*10000 ) } ) ); 

 Now that you have a collection (NewTopRiskControlsWithCalc) with the calculated values, you can attempt to sort this collection and use it in your gallery: 

Sort( NewTopRiskControlsWithCalc, CalculatedValue, Ascending ) 

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.
Greetings

[Danny_Dicaprio]

HI @mmbr1606 

Sorry for the delayed response 

The second clear connect is not taking the formula 

I tried to provide a different name also , but its not working 

Could u please have a look

[Danny_Dicaprio]

Hi @mmbr1606  I change the formula. a bit then also Iam facing the same issue 

SortByColumns(NewTopRiskControlsWithCalc,"CalculatedValue",SortOrder.Ascending)

ClearCollect(
BasicTopRiskControls, 
Top_Risk_Controls
);

ClearCollect(
NewTopRiskControlsWithCalc, 
ForAll(
BasicTopRiskControls, 
{
OriginalRecord: ThisRecord,
CalculatedValue: Sum(
Filter(
Linked_Risks_Causes_Controls, 
Risk_name.Value = ThisRecord.Risk_name.Value && 
Title = ThisRecord.Control.Value
), 
'Cause: Impact'.Value * Potential_impact_cause / 100 * 10000
)
}
)
)