Add next month to new row in gallery

[Anonymous]

Hi,

I have a gallery where the user currently is inserting new rows, where project comes automatically and then the user will have to fill out value and period (date format as a datepicker input).
I would like this datepicker to be automatically filled out with the first in the next month after the previous max row date. 

Any suggestions in how to solve this? I have tried with dateAdd and similiar things but can't seem to get it to work.

The button I have for new row has the following code:

Patch(
'[dbo].[Table]',
Defaults('[dbo].[Table]'),
{
Project: SelProj,
Client: Gallery_overview.Selected.dropclient.Selected.client,
}
)

Example of a row looks like this:

Project	Value	Date
9999	342	8/1/2021

Then I would like the next row to become like this after hitting the "add new row" button:

Project	Value	Date
9999	null	9/1/2021

Any help solving this would be really appreciated!

Thanks!

[timl]

Hi @Anonymous 

Something like this should hopefully work:

With(
     {lastRecord:
      First(FirstN(SortByColumns('[dbo].[Table]',"Date"), 1))
      },
      Patch(
           '[dbo].[Table]',
           Defaults('[dbo].[Table]'),
           {
           Project: Gallery_overview.Selected.txtProjname.Text,
           Date:lastRecord.Date
           }
       )
)

[Anonymous]

Thanks @timl,

It seems to be returning NULL for me. Not sure why. Any information I can provide that might make it easier to solve..?
My data source is SQL and my date column is data type date.

[timl]

Hi @Anonymous 

Looking at this further, one thing I missed here was to sort the records by date descending order whilst retrieving the last record. Maybe this will work better for you.

With(
     {lastRecord:
      First(FirstN(SortByColumns('[dbo].[Table]',"Date", Descending), 1))
      },
      Patch(
           '[dbo].[Table]',
           Defaults('[dbo].[Table]'),
           {
           Project: Gallery_overview.Selected.txtProjname.Text,
           Date:lastRecord.Date
           }
       )
)

[Anonymous]

Hi @timl 

This seems to work on the whole table, but only for one row.
Something I think I forgot to mention and changed in my post is that you select a project based on another gallery, like this: 

Project: SelProj.

Looks like it is able to add the next month after the greatest month in my table, but looks like it keeps repeating that and does not filter on the selected project. 

Sorry to bother you, but really appreciate the help!

[timl · Accepted solution]

Hi @Anonymous 

To take into account the project and to fix the month problem, this type of logic should take you closer:

With(
     {lastRecord:
      LookUp(Filter(SortByColumns('[dbo].[Table]',"Date", Descending), 
                    Project = Gallery_overview.Selected.txtProjname.Text
                    )
      )
      },
      Patch(
           '[dbo].[Table]',
           Defaults('[dbo].[Table]'),
           {
           Project: Gallery_overview.Selected.txtProjname.Text,
           Date:
              DateAdd(
               Date(Year(lastRecord.Date),Month(lastRecord.Date),1),
               1,
               Months
              )
           }
       )
)

[Anonymous]

Great! added the project lookup in filter section as well and it worked just as wanted! thanks!

[timl]

You're welcome @Anonymous, glad that worked for you.