Populate an empty table with predefined values

1

Hi,

i have three elements on my page.

  1. date range picker
  2. button
  3. empty table with three columns (weeknumber, description, person)

I want to do the following.
When i select a date range e.g. from 12/8/2020 to 12/24/2020
and press the button each weeknumber should be filled in the empty table -> column weeknumber as a new row

Like

mytable
weeknumber | description | person
50
51
52

I do not found a formula to do that
AddRow(mytable,mytable.weeknumber,[daterangepicker])

Is there something like for each?

2

@Stefan you can use the WeekNumber() formula to pull the date out of the date control. It will pair nicely with your formula, however I it would be MyTable.Weeknumber (you have weekday). Then extract the date from the control with:

[daterangepicker].First().WeekNumber()

Where First() takes the first date from the range or use Last() to grab the ending value.

3

Hi @Johg_Ananda, yeah that was a mistake by me i mean mytable.weeknumber :slight_smile:
but with your formula i only have one row as result which is 50. But i preselected three weeks in the date range picker which should populate the table with three rows 50, 51 and 52.

The button formula
AddRow(mytable,mytable.weeknumber,[daterangepicker].First().WeekNumber())

4

I also tried Split() and List() to get each weeknumber but then i only run in this error WeekNumber expects parameter dateTime to be a date/time value, but found value ('12/8/2020, 12/24/2020'), which is a list of date/time values

5

OK so you want to get all of the week numbers between the two dates (first() and last()) from the control?

6

@Johg_Ananda
First stage: get all weeknumbers between the given date range.
Second stage: add a row for each weeknumber

if one of these should work i would be happy enough :slight_smile:

7

Hi @Stefan,

have a look at this and tell me if it helps:

Cheers!

8

Wow thanks! I try to understand your formula first :smiley: but this is exactly what i needed :pray:

1 Like
9

Hi @Stefan,
happy it helped.

A quick overview of the formula:

Sequence(DatePicker.First(),DatePicker.Last()). // Create a sequence of dates from first to last date adding one day
    FormulaMap( // for each date (currentValue is here the date in number
        ToDate(CurrentValue).WeekNumber()). // get the Week number
    Unique(). // and keep only unique occurrences 
    FormulaMap( // for each week occurrence... (CurrentValue is here the week number)
        [Weekly Data].AddRow([Weekly Data].[Week Number], CurrentValue)) // create a row
2 Likes
10

one day in my dreams we can put comments in our formulas…

3 Likes
11

Indeed!!! :smiley::smiley::smiley:
Missing them so much

12

@Federico_Stefanato thanks for your explaining :slight_smile: