Collection to SQL and sum dates.

[rvdhorst]

Hi Allen,

How can I manage if I have a start date (date picker) and must be added to this.

For example my first date is 12/07/2021. After this, the following dates should be generated automatically.

07-12-2021
08-12-2021
21-12-2021
22-12-2021
04-01-2022
05-01-2022

So first + 1 day then +14, days. then again + 1 day and again 14, etc. and that in a set of 16x (see picture)

I have the following Collection

ClearCollect(
DateRanges,
AddColumns(
firstN(
[0,1,14,15,28,29,42,43,56,57,70,71,84,85,98,99],
DateDiff(DatePicker1_1.SelectedDate, DatePicker2_1.SelectedDate, Days) + 1
),
"day",
"Day" & (Value + 1),
"date",

DateAdd(DatePicker1_1.SelectedDate, Value, Days)

)

)

My question is how can I write the dates in a SQL database.
Database is called cycle
Date column fields are, date1, date2, date3 to date16

Hope you can help me.

[berofeev]

Are you already connected to your SQL table? 

If they are columns in a table, it would be easier to write 16 lines in a patch statement (provided the +days numbers are always the same) than try to transform a collection to patch. 

[rvdhorst]

Hello berofeev,

Can you give me an example with the source above? The columns are Date, Date2, Date3 etc.

[berofeev]

I'll try to get to it later today. I'm on mobile right now and am having trouble understanding your collect statement on this small screen. 

[rvdhorst]

I have the code from this site

https://powerusers.microsoft.com/t5/Building-Power-Apps/Consecutive-date-range-into-collection/td-p/...

and modified it for my dates formula

[berofeev]

Ok, assuming your table name is "cycle", your patch would look like the following:

Patch(cycle,Defaults(cycle),{
    Date: DatePicker1_1.SelectedDate,
    Date2: DateAdd(DatePicker1_1.SelectedDate, 1, Days),
    Date3: DateAdd(DatePicker1_1.SelectedDate, 14, Days),
    Date4: DateAdd(DatePicker1_1.SelectedDate, 15, Days),
    Date5: DateAdd(DatePicker1_1.SelectedDate, 28, Days),
    Date6: DateAdd(DatePicker1_1.SelectedDate, 29, Days)
})

Another note: I am not sure if you are working with an existing legacy database or designing your own from scratch, but if you are designing from scratch I would caution against the idea of having Date, Date2, Date3 etc... columns.

You would be better off having a related table with date records, and referencing it instead.  As devs though, we don't always get the luxury of designing the underlying data ourselves though...

Cheers,

Brendan

[rvdhorst]

Hi Brendan, Thanks for your answer and help.
Where can I use the patch command in my source? Or should there be a separate button for this?

[berofeev]

Hi @rvdhorst,

Without understanding the full breadth of what you are trying to achieve and what you've built its hard to say where to use it.

A simple solution would be a button thiugh. Place it in the OnSelect property for the button. 

Cheers, 

Brendan 

[v-xiaochen-msft]

Hi @rvdhorst ,

I agree with @rvdhorst .

I don't recommend you to create too many columns.

If you create too many columns, although you can save a lot of information in a record, the canvas formula will be very redundant. And this will affect performance.

If you don’t want to modify your data source, I will make a demo for you based on the collection.

// My datasource 

// My collection 

Add another button and set its Items property to:

Patch(LIST199,Defaults(LIST199),{Col1:First(Col).Value,Col2:Last(FirstN(Col,2)).Value,Col3:Last(FirstN(Col,3)).Value})

Best Regards,

Wearsky

[rvdhorst]

Hi Brendan,

What I want to achieve is when a start date is chosen that it is written to a SQL database cycle with columns Date1, Date2, Date3, Date4.

For example, if the user chooses the date 12/07/2021 that date will be written to Date1, 12/8/2021to Date2 , 12/12/2021 to Date3 , and 12/22/2021 to Date4 .

If the User chooses a different start date, other dates will of course result. Is this easier to make than the way I do this?