cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
freedumz
Level: Powered On

Dynamic table

Hi everyone

I'm meeting an issue with Powerapps

I'd like to do a planning app, so the user enter the amount of hours he will study in a week and he can see his results

So this is where is my blocking point:

 

Powerapps.png

 

if the user select week 40, I can see the time he planned to work one week before and two weeks afters

if he changes, his view change too

 

So this is where I'm blocking how to do that?

For the moment; I was only able to do something like that:

 

CURRENT.png

 

So currently; I have a sharepoint List like this:

 

Topic       Object   Hours  Date

Math       Study       3        08/20/2018

English   Study       4       08/20/2018

English   Study       2       08/30/2018

 

So each line is a item in my list

 

Have you some clues to how to start it well?

I'm totally stuck

The only thing I could do it would be to do a new sharepoint list with 52 columns (one for each week) but il will be a heavy method; I'm pretty sure there is an easier way to do it

 

Thanks for your help

 

Regards

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Dynamic table

Hi @freedumz,

 

Could you please share a bit more about your scenario?

 

I have made a test on my side, please take a try with the following workaround:7.JPG

 

8.JPG

 

9.JPG

 

Add three Gallery controls within the screen -- Gallery1, Gallery2 and Gallery3 (Marked as read number in the screenshot). 

 

Set the Items property of the Gallery1 control to following formula:

 

Distinct(
Filter('20180714_case8', SelectDate=Text(DatePicker1.SelectedDate,"[$-en-US]mm/dd/yyyy")|| SelectDate=Text(DateAdd(DatePicker1.SelectedDate,-7),"[$-en-US]mm/dd/yyyy")|| SelectDate=Text(DateAdd(DatePicker1.SelectedDate,7),"[$-en-US]mm/dd/yyyy")|| SelectDate=Text(DateAdd(DatePicker1.SelectedDate,14),"[$-en-US]mm/dd/yyyy") ), Topic )

Within the Gallery1 control, add a Label control, set the Text property to following formula:

 

ThisItem.Result

10.JPG

 

Set the Items property of the Gallery2 control to following formula:

 

Distinct(
Filter('20180714_case8', SelectDate=Text(DatePicker1.SelectedDate,"[$-en-US]mm/dd/yyyy")|| SelectDate=Text(DateAdd(DatePicker1.SelectedDate,-7),"[$-en-US]mm/dd/yyyy")|| SelectDate=Text(DateAdd(DatePicker1.SelectedDate,7),"[$-en-US]mm/dd/yyyy")|| SelectDate=Text(DateAdd(DatePicker1.SelectedDate,14),"[$-en-US]mm/dd/yyyy") ), Topic )

Within the Gallery2 control, add a Label control (As that within Gallery1 control), set the Text property to following formula:

 

 

LookUp('20180714_case8',Topic=ThisItem.Result).Object

 

 

Set the Items property of the Gallery3 control to following formula:

 

Distinct(
Filter('20180714_case8', SelectDate=Text(DatePicker1.SelectedDate,"[$-en-US]mm/dd/yyyy")|| SelectDate=Text(DateAdd(DatePicker1.SelectedDate,-7),"[$-en-US]mm/dd/yyyy")|| SelectDate=Text(DateAdd(DatePicker1.SelectedDate,7),"[$-en-US]mm/dd/yyyy")|| SelectDate=Text(DateAdd(DatePicker1.SelectedDate,14),"[$-en-US]mm/dd/yyyy") ), Topic )

Within Gallery3 control, add forth Label controls. Set the Text property of the first Label control to following formula:

 

 

If(
Sum(Filter('20180714_case8',Topic=ThisItem.Result&&SelectDate=Text(DateAdd(DatePicker1.SelectedDate,-7),"[$-en-US]mm/dd/yyyy")),Hours)=Blank(),
0,
Sum(Filter('20180714_case8',Topic=ThisItem.Result&&SelectDate=Text(DateAdd(DatePicker1.SelectedDate,-7),"[$-en-US]mm/dd/yyyy")),Hours)
)

 

11.JPG

 

Set the Text property of the second Label control (Within Gallery3 control) to following formula:

If(
Sum(Filter('20180714_case8',Topic=ThisItem.Result&&SelectDate=Text(DatePicker1.SelectedDate,"[$-en-US]mm/dd/yyyy")),Hours)=Blank(),
0,
Sum(Filter('20180714_case8',Topic=ThisItem.Result&&SelectDate=Text(DatePicker1.SelectedDate,"[$-en-US]mm/dd/yyyy")),Hours)
)

12.JPG

 

Set the Text property of the Third Label control to following formula:

 

If(
Sum(Filter('20180714_case8',Topic=ThisItem.Result&&SelectDate=Text(DateAdd(DatePicker1.SelectedDate,7),"[$-en-US]mm/dd/yyyy")),Hours)=Blank(),
0,
Sum(Filter('20180714_case8',Topic=ThisItem.Result&&SelectDate=Text(DateAdd(DatePicker1.SelectedDate,7),"[$-en-US]mm/dd/yyyy")),Hours)
)

13.JPG

 

 

 

 

Set the Text property of the forth Label control to following formula:

 

If(
Sum(Filter('20180714_case8',Topic=ThisItem.Result&&SelectDate=Text(DateAdd(DatePicker1.SelectedDate,14),"[$-en-US]mm/dd/yyyy")),Hours)=Blank(),
0,
Sum(Filter('20180714_case8',Topic=ThisItem.Result&&SelectDate=Text(DateAdd(DatePicker1.SelectedDate,14),"[$-en-US]mm/dd/yyyy")),Hours)
)

14.JPG

 

Note: Within the above formulas, the '20180714_case8' represents the SP list within my app, the SelectDate represents the Date type column (On your side, it is Date).

 

 

Please take a try with above solution, then check if it would help in your scenario.

 

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Community Support Team
Community Support Team

Re: Dynamic table

Hi @freedumz,

 

Could you please share a bit more about your scenario?

 

I have made a test on my side, please take a try with the following workaround:7.JPG

 

8.JPG

 

9.JPG

 

Add three Gallery controls within the screen -- Gallery1, Gallery2 and Gallery3 (Marked as read number in the screenshot). 

 

Set the Items property of the Gallery1 control to following formula:

 

Distinct(
Filter('20180714_case8', SelectDate=Text(DatePicker1.SelectedDate,"[$-en-US]mm/dd/yyyy")|| SelectDate=Text(DateAdd(DatePicker1.SelectedDate,-7),"[$-en-US]mm/dd/yyyy")|| SelectDate=Text(DateAdd(DatePicker1.SelectedDate,7),"[$-en-US]mm/dd/yyyy")|| SelectDate=Text(DateAdd(DatePicker1.SelectedDate,14),"[$-en-US]mm/dd/yyyy") ), Topic )

Within the Gallery1 control, add a Label control, set the Text property to following formula:

 

ThisItem.Result

10.JPG

 

Set the Items property of the Gallery2 control to following formula:

 

Distinct(
Filter('20180714_case8', SelectDate=Text(DatePicker1.SelectedDate,"[$-en-US]mm/dd/yyyy")|| SelectDate=Text(DateAdd(DatePicker1.SelectedDate,-7),"[$-en-US]mm/dd/yyyy")|| SelectDate=Text(DateAdd(DatePicker1.SelectedDate,7),"[$-en-US]mm/dd/yyyy")|| SelectDate=Text(DateAdd(DatePicker1.SelectedDate,14),"[$-en-US]mm/dd/yyyy") ), Topic )

Within the Gallery2 control, add a Label control (As that within Gallery1 control), set the Text property to following formula:

 

 

LookUp('20180714_case8',Topic=ThisItem.Result).Object

 

 

Set the Items property of the Gallery3 control to following formula:

 

Distinct(
Filter('20180714_case8', SelectDate=Text(DatePicker1.SelectedDate,"[$-en-US]mm/dd/yyyy")|| SelectDate=Text(DateAdd(DatePicker1.SelectedDate,-7),"[$-en-US]mm/dd/yyyy")|| SelectDate=Text(DateAdd(DatePicker1.SelectedDate,7),"[$-en-US]mm/dd/yyyy")|| SelectDate=Text(DateAdd(DatePicker1.SelectedDate,14),"[$-en-US]mm/dd/yyyy") ), Topic )

Within Gallery3 control, add forth Label controls. Set the Text property of the first Label control to following formula:

 

 

If(
Sum(Filter('20180714_case8',Topic=ThisItem.Result&&SelectDate=Text(DateAdd(DatePicker1.SelectedDate,-7),"[$-en-US]mm/dd/yyyy")),Hours)=Blank(),
0,
Sum(Filter('20180714_case8',Topic=ThisItem.Result&&SelectDate=Text(DateAdd(DatePicker1.SelectedDate,-7),"[$-en-US]mm/dd/yyyy")),Hours)
)

 

11.JPG

 

Set the Text property of the second Label control (Within Gallery3 control) to following formula:

If(
Sum(Filter('20180714_case8',Topic=ThisItem.Result&&SelectDate=Text(DatePicker1.SelectedDate,"[$-en-US]mm/dd/yyyy")),Hours)=Blank(),
0,
Sum(Filter('20180714_case8',Topic=ThisItem.Result&&SelectDate=Text(DatePicker1.SelectedDate,"[$-en-US]mm/dd/yyyy")),Hours)
)

12.JPG

 

Set the Text property of the Third Label control to following formula:

 

If(
Sum(Filter('20180714_case8',Topic=ThisItem.Result&&SelectDate=Text(DateAdd(DatePicker1.SelectedDate,7),"[$-en-US]mm/dd/yyyy")),Hours)=Blank(),
0,
Sum(Filter('20180714_case8',Topic=ThisItem.Result&&SelectDate=Text(DateAdd(DatePicker1.SelectedDate,7),"[$-en-US]mm/dd/yyyy")),Hours)
)

13.JPG

 

 

 

 

Set the Text property of the forth Label control to following formula:

 

If(
Sum(Filter('20180714_case8',Topic=ThisItem.Result&&SelectDate=Text(DateAdd(DatePicker1.SelectedDate,14),"[$-en-US]mm/dd/yyyy")),Hours)=Blank(),
0,
Sum(Filter('20180714_case8',Topic=ThisItem.Result&&SelectDate=Text(DateAdd(DatePicker1.SelectedDate,14),"[$-en-US]mm/dd/yyyy")),Hours)
)

14.JPG

 

Note: Within the above formulas, the '20180714_case8' represents the SP list within my app, the SelectDate represents the Date type column (On your side, it is Date).

 

 

Please take a try with above solution, then check if it would help in your scenario.

 

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
freedumz
Level: Powered On

Re: Dynamic table

Hi @v-xida-msft

 

I'm sorry for the delay but I was in vacation

I tried your code, but I have the following error message:

"Incompatible type. We can't evaluate your formula because the values being compared compared in the formula aren't the same type. The left value is a Date type  and the right value is a text type"

So I transforme my Date in Text, but it's probably not the best way to do that

Do you know why I have this message and not you?

Helpful resources

Announcements
thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Kudoed Authors (Last 30 Days)
Users online (4,564)