Autogenerate date from button into gallery, while restricting number of rows.

[Anonymous]

Hi experts!

Hi have a problem I would like to see if is possible to solve. 
I have a gallery with a Project, datefield and a number field. The last two fields are user inputs.

A new row in my gallery is added from a button using Patch.
I'm wondering if its possible to automatically add new periods with set dates. 

What I would like to achieve is something like this:
After a user selects a project, the gallery automatically fills up with 12 rows, the next 12 months. And its not possible to add new rows with period greater than that. 
As an example, user selects project 999, then gallery will add 12 rows like this:

Project	Period	Sales
999	4/1/2021
999	5/1/2021
999	6/1/2021
999	7/1/2021
999	8/1/2021
999	9/1/2021
999	10/1/2021
999	11/1/2021
999	12/1/2021
999	1/1/2022
999	2/1/2022
999	3/1/2022

Is this possible, or if not, something similar or close this this.

Thank you so much.

[JR-BejeweledOne]

Is your gallery using a collection or direct data from a SharePoint list or other data source?

[Anonymous]

Hi @JR-BejeweledOne, I'm using SQL as my data source.

[JR-BejeweledOne]

Ok, is your gallery items property getting it's information directly from your SQL data source or have you brought the data into a collection and displaying the collection in the gallery?

[Anonymous]

@JR-BejeweledOne 
Yes, its getting information directly from SQL data source. Right now I have a button to add a new row, but looking for tips on how to change this.

[JR-BejeweledOne · Accepted solution]

On your button try this:   This will find the start of the next month and then create a collection of the next 12 months.   Then it will add 12 rows to the data source with the date of each month already populated.

Set(varEnd, Month(Today()) + 1);

ForAll(
  Sequence(12, varEnd),
   Collect(DateColl,
    Date(Year(Today()), Value, 1)
  )
);

ForAll(DateColl,
  Patch(DataSource,
   Defaults(DataSource),
    {Date: Value}
 ) 
)

[Anonymous]

Hi, This looks really good! For some reason it looks like it adds three rows per month. Not sure why it does that..?

My button now looks like this:

Set(varEnd, Month(Today()) + 1);

ForAll(
  Sequence(12, varEnd),
   Collect(DateColl,
    Date(Year(Today()), Value, 1)
  )
);

ForAll(DateColl,
  Patch('[dbo].[table]',
   Defaults('[dbo].[table]'),
    {
        Dato:Value,
         Projectname: Gallery_overview.Selected.txtProjname.Text,
        Client: Gallery__overview.Selected.dropclientKBN_1.Selected.client,
        Maincategory:Gallery_overview.Selected.Label_kategori.Text,
        ClientProjectID:SelProj
    }
 ) 
)

[JR-BejeweledOne]

I am not sure, It didn't do that for me.   I am not working with sql though.   I don't know if that would cause it or not.

[JR-BejeweledOne]

If you display DateColl in a table does it only have one of each date?

[Anonymous]

works now! thank you so much!