Answered

Sort table gallery by related table column

Like (0) Share

Report

Posted on 15 Aug 2021 16:08:43 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

I have the same question (0)

All responses (14)

Answers (1)

v-xiaochen-msft on 17 Aug 2021 at 01:19:24

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

Was this reply helpful? Yes No
Community Power Pla... on 16 Aug 2021 at 10:02:39

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

Was this reply helpful? Yes No
Verified answer

v-xiaochen-msft on 16 Aug 2021 at 07:23:22

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

Was this reply helpful? Yes No
Community Power Pla... on 16 Aug 2021 at 07:05:20

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.

Was this reply helpful? Yes No
v-xiaochen-msft on 16 Aug 2021 at 06:57:54

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

Was this reply helpful? Yes No
Community Power Pla... on 16 Aug 2021 at 06:55:56

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:

Was this reply helpful? Yes No
Community Power Pla... on 16 Aug 2021 at 06:46:17

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

Was this reply helpful? Yes No
v-xiaochen-msft on 16 Aug 2021 at 06:40:59

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

Was this reply helpful? Yes No
Community Power Pla... on 16 Aug 2021 at 06:37:11

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

Was this reply helpful? Yes No
v-xiaochen-msft on 16 Aug 2021 at 06:20:23

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.

Was this reply helpful? Yes No