cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rvdhorst
Helper III
Helper III

Collection to SQL and sum dates.

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.

10 REPLIES 10
berofeev
Helper I
Helper I

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
Helper III
Helper III

Hello berofeev,

 

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

 

 

berofeev
Helper I
Helper I

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
Helper III
Helper III

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
Helper I
Helper I

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

 

 

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?

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
Community Support
Community Support

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.

vxiaochenmsft_0-1638953003016.png

// My datasource 

 

vxiaochenmsft_1-1638953027140.png

vxiaochenmsft_2-1638953037349.png

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

vxiaochenmsft_3-1638953068400.png

 

vxiaochenmsft_4-1638953076795.png

 

Best Regards,

Wearsky

 

 

 

 

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?

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (3,388)