Create same record with different dates

[Flashback]

Hello,

i have application where i choose basic information - Analyst, Method and date range From/Till.

By button (SAVE) are data send to SQL table.

In this step i need save same records with different dates based on the date range which i choosed.

Example record in app

Analyst - Tomas

Method - CFA

From - 25.5.2020

Till - 28.5.2020

I need save 4 record in SQL

Tomas / CFA / 25.5.2020

Tomas / CFA / 26.5.2020

Tomas / CFA / 27.5.2020

Tomas / CFA / 28.5.2020

Thank you!

Jakub

[pontusofsweden · Accepted solution]

Hi!

I made a test on my end where I tried to create a collection with multiple lines depending on the date selections and it worked. 

1. First, in the OnVisible property for your screen, or OnStart property for the app, create a dummy collection with numbers. You need to have as many numbers as you can have a date difference between from and to date.

ClearCollect(NumberCollection,[0,1,2,3,4,5,6,7,8,9,10])

2. On the OnSelect property of your SAVE button, the following formula will create one record per day between those dates. NOTE! Im using collections, you need to change it to Patch instead to create records in SQL. You might also change the name of the columns and control names of course 🙂

ForAll(
    FirstN(
        NumberCollection,
        DateDiff(
            DatePicker1.SelectedDate,
            DatePicker2.SelectedDate + 1
        )
    ),
    Collect(
        TestDates,
        {
            Name: TextInput1.Text,
            Date: DatePicker1.SelectedDate + Value
        }
    )
)

BR

Pontus

[v-bofeng-msft]

Hi @Flashback :

Do you want to add a corresponding number of records based on the start date and end date?

Using loop statements is the best choice, but powerapps does not have a sufficiently flexible loop algorithm. Therefore, the key is to find an alternative solution that can flexibly execute the loop algorithm,.

Thanks for @pontusofsweden 's solution.

In addition,I have another solution for your reference

The key is using timer control.

I've made a test for your reference:

In order to facilitate the experiment, I used a collection(sss) as my data source.

1\Add Text Input control(TextInput3)

2\Add two DatePicker control(DatePicker1/DatePicker1_1)

3\Add a timer control:

Duration:

10 /*loop in 0.01s*/

OnSelect:

Set(var,0)

OnTimerEnd:

Collect(
    sss,
    {
        title: TextInput3.Text,
        date: DateAdd(
            DatePicker1.SelectedDate,
            var,       /*var is my custom variable*/
            Days
        )     /*Calculation date*/
    }
);/*Collect record*/
Set(
    var,
    var + 1
) /*var is my custom variable*/

Repeat:

If(
    var < DateDiff(
        DatePicker1.SelectedDate,
        DatePicker1_1.SelectedDate
    ),
    true,
    false
)  /*End the loop after executing the specified number of times*/

Text:

"Submit" /*Make this timer look like a button*/

Best Regards,

Bof