Answered

Sort table gallery by related table column

(0) Share

Report

Posted on by Community Power Pla...

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

Tables

All responses (14)

Answers (1)

v-xiaochen-msft on at

Like (0)

Report
Re: Sort table gallery by related table column

Hi @AnthonyDo ,

What if you only use this formula in items? Will it report an error?

SortByColumns( AddColumns( Trainees As A,"cref8_remainingbudget",LookUp(TraineeYearlyBudgets,TraineeYearlyBudgets=A.traineeyearlybudget.TraineeYearlyBudgets).Remainingbudget), Ascending )

And I see an obvious error in your formula.

The addcolumns() function is missing one ')'.

Best Regards,

Wearsky
Community Power Pla... on at

Like (0)

Report
Re: Sort table gallery by related table column

hi @v-xiaochen-msft ,

that did not work either 😕

code:

SortByColumns( AddColumns( Trainees As A,"cref8_remainingbudget",LookUp(TraineeYearlyBudgets,TraineeYearlyBudgets=A.traineeyearlybudget.TraineeYearlyBudgets).Remainingbudget, If( varSortByBudget, Ascending, Descending ) )

screen of error:

Kind regards,

Anthony
Verified answer

v-xiaochen-msft on at

Like (0)

Report
Re: Sort table gallery by related table column

Hi @AnthonyDo ,

Please try this:

AddColumns(Trainees As A,"cref8_remainingbudget",LookUp(TraineeYearlyBudget,TraineeYearlyBudget=A.traineeyearlybudget.TraineeYearlyBudget).Remainingbudget)

// TraineeYearlyBudget is a guid column.

// traineeyearlybudget is a lookup column .

Best Regards,

Wearsky
Community Power Pla... on at

Like (0)

Report

Re: Sort table gallery by related table column

How would i implement this inside the gallery items formula. However i try to implement this is keeps throwing me errors.
v-xiaochen-msft on at

Like (0)

Report

Re: Sort table gallery by related table column

Hi @AnthonyDo ,

This method is indeed better.

You could use this in your gallery Items formula.

I think it will work.

Best Regards,

Wearsky
Community Power Pla... on at

Like (0)

Report

Re: Sort table gallery by related table column

i have another label in the gallery and this does show me the remainingbudget for each trainee:
Community Power Pla... on at

Like (0)

Report
Re: Sort table gallery by related table column

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
v-xiaochen-msft on at

Like (0)

Report

Re: Sort table gallery by related table column

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
Community Power Pla... on at

Like (0)

Report

Re: Sort table gallery by related table column

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
v-xiaochen-msft on at

Like (0)

Report
Re: Sort table gallery by related table column

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.