Answered

Sort Formula Help on Gallery w/ SharePoint Data Source and Choice Field

(0) Share

Report

Posted on by whatsthisdo

Hello,

I'm hoping someone can help me sort a gallery by a column value and escape the errors I keep getting with the formulas I've tried:

Here's what I currently have:

Filter(

'Assignments',

IsBlank(NatureComboBox.SelectedItems.Value) || IsEmpty(NatureComboBox.SelectedItems) || 'Nature of Task'.Value = NatureComboBox.Selected.Value,

IsBlank(SegmentComboBox.SelectedItems.Value) || IsEmpty(SegmentComboBox.SelectedItems) || 'Segment/Work Area'.Value = SegmentComboBox.Selected.Value,

IsBlank(StatusComboBox.SelectedItems.Value) || IsEmpty(StatusComboBox.SelectedItems) || Status.Value = StatusComboBox.Selected.Value

)

I'd like to add a SortByColumns on a choice field called "Status" with values of 1. Available, 2. Assigned, and 3. Completed but I keep getting errors when I add the SortByColumns like this:

SortByColumns(

Filter(

'Assignments',

IsBlank(NatureComboBox.SelectedItems.Value) || IsEmpty(NatureComboBox.SelectedItems) || 'Nature of Task'.Value = NatureComboBox.Selected.Value,

IsBlank(SegmentComboBox.SelectedItems.Value) || IsEmpty(SegmentComboBox.SelectedItems) || 'Segment/Work Area'.Value = SegmentComboBox.Selected.Value,

IsBlank(StatusComboBox.SelectedItems.Value) || IsEmpty(StatusComboBox.SelectedItems) || Status.Value = StatusComboBox.Selected.Value

),
"Status",
SortOrder.Ascending
)

Thank you!

Categories:

General Questions

I have the same question (0)

All responses (4)

Answers (2)

Verified answer

Ami K 15,670 Super User 2024 Season 1 on at

Like (1)

Report
Re: Sort Formula Help on Gallery w/ SharePoint Data Source and Choice Field

@whatsthisdo -

Try:

SortByColumns( Filter( AddColumns( 'Assignments', "_status_text", Status.Value ), Len(NatureComboBox.Selected.Value) = 0 || 'Nature of Task'.Value = NatureComboBox.Selected.Value, Len(SegmentComboBox.Selected.Value) = 0 || 'Segment/Work Area'.Value = SegmentComboBox.Selected.Value, Len(StatusComboBox.Selected.Value) = 0 || Status.Value = StatusComboBox.Selected.Value ), "_status_text", [ "1. Available", "2. Assigned", "3. Completed" ] )

However, it is worth pointing out that the AddColumns function is not delegable.

You could mitigate this somewhat by using the With function to create filtered table first (Filter is delegable), and then apply the AddColumns function on that filtered dataset:

With( { _pre_filtered_data: Filter( 'Assignments', Len(NatureComboBox.Selected.Value) = 0 || 'Nature of Task'.Value = NatureComboBox.Selected.Value, Len(SegmentComboBox.Selected.Value) = 0 || 'Segment/Work Area'.Value = SegmentComboBox.Selected.Value, Len(StatusComboBox.Selected.Value) = 0 || Status.Value = StatusComboBox.Selected.Value ) }, SortByColumns( AddColumns( _pre_filtered_data, "_status_text", Status.Value ), "_status_text", [ "1. Available", "2. Assigned", "3. Completed" ] ) )

Was this reply helpful? Yes No
whatsthisdo 14 on at

Like (0)

Report

Re: Sort Formula Help on Gallery w/ SharePoint Data Source and Choice Field

Thank you! The solution was able to filter the gallery in the desired manner but it broke the Detail Screen because clicking on the arrow icon on the Gallery Screen, which contained OnSelect: Select(Parent) opened the Details Screen, which said there was 'No Item to Display' when the Item was set to BrowseGallery1.Selected.

Was this reply helpful? Yes No
Verified answer

Ami K 15,670 Super User 2024 Season 1 on at

Like (1)

Report
Re: Sort Formula Help on Gallery w/ SharePoint Data Source and Choice Field

@whatsthisdo -

Typically, we populate an EditForm or DisplayForm control with a record based on a Gallery selection as you have done.

However, with an EditForm or DisplayFormcontrol, the DataSource property and the Item property must not only read from the same data source, but the data source it is reading must also have the same schema as the Items property of the Gallery control.

Whenever use a "transformative" function such as the AddColumns or GroupBy, the schema of the Items property of the Gallery is changed. Because of this, the Item property of the Form, and the Items property of the Gallery control, no longer share the same data source schema.

To address this, we can use a LookUp function directly against the unmodified data source to return the correct record.

On the Item property of the Form control, enter:

LookUp( 'Your DataSource' ID = BrowseGallery1.Selected.ID )

Or ideally use a Context or Global Variable, the most simplest being a Global Variable which is scoped to the app and therefore persistent across all screens.

On the OnSelect property of the Gallery, enter:

Set( gbl_record; ThisItem )

On the Item property of the Form, enter:

LookUp( 'Your Data Source' ID = gbl_record.ID )

Was this reply helpful? Yes No
whatsthisdo 14 on at

Like (0)

Report

Re: Sort Formula Help on Gallery w/ SharePoint Data Source and Choice Field

Got it, thank you!

Was this reply helpful? Yes No