Answered

Sort table gallery by related table column

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hello everyone,

I would like to sort a gallery with sourcetable Trainees by a column from a related table. That column is "cref8_remainingbudget" from the table TraineeYearlyBudget.

I made a lookup field in my Trainees table. You can see this in the picture underneath.

So i tried to do this myself but i can't get it to work. The code underneath is what i currently have (and is not working).

!IsBlank(varSortByBudget),
 SortByColumns(
 AddColumns(
 Trainees,
 "cref8_remainingbudget",
 traineeyearlybudget.cref8_remainingbudget

 ),
 "cref8_remainingbudget",
 If(
 varSortByBudget,
 Ascending,
 Descending
 )
 )

What am i doing wrong here?

thanks in advance,

Anthony

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (14)

Answers (1)

v-xiaochen-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @AnthonyDo ,

I noticed that the name of the lookup column in the image is ‘new_traineeyearlybudget’.

But the name of lookup column in the formula is 'cref8_remainingbudget'.

Which one is your lookup column name?

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @v-xiaochen-msft ,

The column "Remainingbudget" from table "TraineeYearlyBudget" is the column i am trying to add and sort on.

Kind regards,

Anthony

Was this reply helpful? Yes No
v-xiaochen-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @AnthonyDo ,

What's the data type of "Remainingbudget"? Is it a text column or anything else?

Best Regards,

Wearsky

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @v-xiaochen-msft ,

It is a decimal number column.

Kind regards,

Anthony

Was this reply helpful? Yes No
v-xiaochen-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @AnthonyDo ,

I did a demo for you.

1\ Table 'TraineeYearlyBudget'

2\ Table 'Trainees'

3\ Their relationship

4\ Add a gallery control and set its Items property to:

SortByColumns(AddColumns(Trainees,"cref8_remainingbudget",ThisRecord.traineeyearlybudget.Remainingbudget),"cref8_remainingbudget",Ascending)

5\ The result is as follows:

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Dear @v-xiaochen-msft ,

I tried your code and for me it is not working. I added a label to my gallery with text property:
ThisItem.traineeyearlybudget.'Remaining Budget'. It doesn't show me any values though. I think something is not right with my lookup?

Kind regards,
Anthony

Was this reply helpful? Yes No
v-xiaochen-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @AnthonyDo ,

Could you share some screenshots?

For example : your gallery's Items formula & your label formula ...

And does the formula report an error?

Best Regards,

Wearsky

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Dear @v-xiaochen-msft

My full gallery code:

If( !varShowOutOfServiceEmployees, If( !IsBlank(varSortByName), SortByColumns( Filter( Trainees, cref8_outofservice <> 'Out Of Service (Trainees)'.'1' || cref8_outofservice = 'Out Of Service (Trainees)'.'1' && cref8_employmentenddate > Today() ), "cref8_firstname", If( varSortByName, Ascending, Descending ), "cref8_lastname", If( varSortByName, Ascending, Descending ) ), !IsBlank(varSortByBudget), SortByColumns( Filter( Trainees, cref8_outofservice <> 'Out Of Service (Trainees)'.'1' || cref8_outofservice = 'Out Of Service (Trainees)'.'1' && cref8_employmentenddate > Today() ), "cref8_trainingbudget", If( varSortByBudget, Ascending, Descending ) ), !IsBlank(varSortByTrainingDays), SortByColumns( Filter( Trainees, cref8_outofservice <> 'Out Of Service (Trainees)'.'1' || cref8_outofservice = 'Out Of Service (Trainees)'.'1' && cref8_employmentenddate > Today() ), "cref8_trainingdays", If( varSortByTrainingDays, Ascending, Descending ) ), Trainees ), If( !IsBlank(varSortByName), SortByColumns( Trainees, "cref8_firstname", If( varSortByName, Ascending, Descending ), "cref8_lastname", If( varSortByName, Ascending, Descending ) ), !IsBlank(varSortByBudget), SortByColumns( AddColumns( Trainees, "cref8_remainingbudget", ThisRecord.traineeyearlybudget.'Remaining Budget' ), "cref8_remainingbudget", If( varSortByBudget, Ascending, Descending ) ), !IsBlank(varSortByTrainingDays), SortByColumns( AddColumns( Trainees, "cref8_remainingdays", ThisRecord.traineeyearlybudget.'Remaining Days' ), "cref8_remainingdays", If( varSortByTrainingDays, Ascending, Descending ) ), Trainees ) )

My label inside gallery:

The gallery code is not giving any errors.

King Regards,
Anthony

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

i have another label in the gallery and this does show me the remainingbudget for each trainee:

Was this reply helpful? Yes No
v-xiaochen-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @AnthonyDo ,

This method is indeed better.

You could use this in your gallery Items formula.

I think it will work.

Best Regards,

Wearsky

Was this reply helpful? Yes No