Dynamic table

[freedumz]

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:

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:

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

[v-xida-msft · Accepted solution]

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:

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

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)
)

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)
)

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)
)

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)
)

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

[freedumz]

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?