Unanswered

Sorting Gallery based on calculated Value

(0) Share

Report

Posted on by 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

Categories:

All responses (7)

Answers (0)

Danny_Dicaprio on at

Like (0)

Report
Re: Sorting Gallery based on calculated Value

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
)
}
)
)
Danny_Dicaprio on at

Like (0)

Report

Re: Sorting Gallery based on calculated Value

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
mmbr1606 12,121 Super User 2025 Season 1 on at

Like (1)

Report

Re: Sorting Gallery based on calculated Value

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 on at

Like (0)

Report

Re: Sorting Gallery based on calculated Value

Hi @mmbr1606

Thanks again ,But it is showing some error

what would be the reason ? can you please hep me on this
mmbr1606 12,121 Super User 2025 Season 1 on at

Like (1)

Report

Re: Sorting Gallery based on calculated Value

yes u can try it in the onvisible of the screen
Danny_Dicaprio on at

Like (0)

Report

Re: Sorting Gallery based on calculated Value

@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 12,121 Super User 2025 Season 1 on at

Like (0)

Report
Re: Sorting Gallery based on calculated Value

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