cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jacktransform
Helper III
Helper III

Sorting Gallery by Multiple Columns if One is Blank

Hi all!

 

I have a table in Dataverse called Table that has two columns, SometimesFilled and AlwaysFilled. SometimesFilled is the more important of the two columns, but like it's name implies it only sometimes contains a value. Meanwhile, AlwaysFilled will always have a value for every record in my table Table. Think of AlwaysFilled like the primary column for this table.

 

I have a gallery in my app with the items set to [@Tables], and whenever possible I want the Title control (called TitleControl) of my gallery to show ThisItem.SometimesFilled. Whenever ThisItem.SometimesFilled is blank I want TitleControl to be instead ThisItem.AlwaysFilled. TitleControl is the following code:

 

If(

!IsBlank(ThisItem.SometimesFilled),

ThisItem.SometimesFilled,

ThisItem.AlwaysFilled

)

 

I'm having problems sorting the gallery alphanumerically by what is essentially in TitleControl. I set the Items property for the gallery to the following code, but end up with a delegation warning:

 

Sort([@Tables], "TitleControl", Ascending)

 

Has anyone had success with putting a condition in the Title control of their gallery and then sorting their gallery by what's in the Title control without getting delegation warnings? 

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

Hi @jacktransform :

Could you tell me if your sort formula is like this:

Sort(
    jacktransformTests,
    If(
        !IsBlank(SometimesFilled),
        SometimesFilled,
        AlwaysFilled
    ),
    Ascending
)

if so, let me explain why you encountered this problem.

The key is that TitleControl is not a real column, it is a "virtual" column obtained by calculation.So your Sort formula can not be delegated.So far, the deletion warning cannot be eliminated.

Finally,I have two alternatives:

Solution 1:Add an automatically calculated auxiliary column in the data source

1.JPG2.JPG

Then sort directly according to this calculated column('Auxiliary column'), the effect is the same

Sort(
    jacktransformTests,
    'Auxiliary column',
    Ascending
)

Solution 2:If your data source has less than 2000 records, you can ignore this deletion warning(Just set 'dat row limit ' to 2000).

3.JPG

 Best Regards,

Bof

View solution in original post

2 REPLIES 2
v-bofeng-msft
Community Support
Community Support

Hi @jacktransform :

Could you tell me if your sort formula is like this:

Sort(
    jacktransformTests,
    If(
        !IsBlank(SometimesFilled),
        SometimesFilled,
        AlwaysFilled
    ),
    Ascending
)

if so, let me explain why you encountered this problem.

The key is that TitleControl is not a real column, it is a "virtual" column obtained by calculation.So your Sort formula can not be delegated.So far, the deletion warning cannot be eliminated.

Finally,I have two alternatives:

Solution 1:Add an automatically calculated auxiliary column in the data source

1.JPG2.JPG

Then sort directly according to this calculated column('Auxiliary column'), the effect is the same

Sort(
    jacktransformTests,
    'Auxiliary column',
    Ascending
)

Solution 2:If your data source has less than 2000 records, you can ignore this deletion warning(Just set 'dat row limit ' to 2000).

3.JPG

 Best Regards,

Bof

View solution in original post

jacktransform
Helper III
Helper III

@v-bofeng-msft 

Wow I had no idea you could even do calculated columns! I'll give that a try!

 

This is not related to my original questions, but I have my tables in the dataverse hooked up to a PowerBI report as well. Would you recommend creating calculated columns in the dataverse for cases of simple calculations instead of doing DAX measures/calculated columns in PowerBI? Is there any benefit to one or the other?

 

Thanks!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (2,787)