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

Perform update on Gallery for dates - Powerapps

Hi,

 

When a new record is added, the previous record’s End Date should get updated to one day, prior to the current record’s start date for particular location. Any suggestion how can i achieve this would be helpful. I have SharePoint as backend and the records are populated in Gallery

 

For example, in the below screenshot of Gallery, for the Limited Open status, the End Date should change to 4/30/2020.

 

1.PNG

 

2.PNG

 
1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Iantaylor2050 ,

Do you want to :
update your precious record's end date to the new created record's start date-1 when you submit a new record?

Do you use form to submit a new record?

Could you tell me Location field's data type? I assume it is choice data type.

If so, please try this formula:

SubmitForm(Form1);//create new record
Patch(listname,
Last(
      FirstN(
              Filter(listname,Location.Value=Form1.LastSubmit.Location.Value),
              CountRows(listname)-1
             )
      ),
//get the previous record
{'End Date': DateAdd(Form1.LastSubmit.'Start Date',-1,Days)}
//the last submited record's start date-1
)

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
ZabiBabar
Continued Contributor
Continued Contributor

@Iantaylor2050 , You can modify your save button onSelect logic to edit the last record for a specific city. You can do this using the patch function. You can get the last item from the list like this:

Set(LastRecordCity, Last(Filter(<Enter List Name>, Location = "Dallas"))); 

In your case, you might do ThisItem.Location instead of Dallas. 

However you need to decide what the primary key field is in your Sharepoint list. Once you know you can use that:

Patch(
  <List Name>, 
  <Primary Field> = LastRecordCity.<Primary Field>, 
  EndDate : DateAdd( ThisItem.StartDate, -1)
);

 

You will need to change your variables of course. You can do this before you submit the form to add the new record. 

 

Best,
Zabi
Mark this post as a solution, or kudo it if you found it be helpful. You can choose more than one answer as solutions, including your own answer. 🙂

v-yutliu-msft
Community Support
Community Support

Hi @Iantaylor2050 ,

Do you want to modify the previous record's end date to current record's start date-1 when you create a new record?

If so, you just need to use Last function to get the last created record's date, then use DateAdd function to update the previous record.

The submit function should be like this:

SubmitForm(Form1);//create new record
Patch(listname,
Last(FirstN(listname,CountRows(listname)-1)),
//get the previous record
{'End Date': DateAdd(Last(listname).'Start Date',-1,Days)}
//the last submited record's start date-1
)

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yutliu-msft @ZabiBabar ,

 

The end date should get updated, based on the location the dropdown. In the below screenshot, for Dallas, the end date for 1st

record should be updated to 30/04/2020. 

 

@ZabiBabar ,

 

I tried the formula you provided. However it throws error on ThisItem. in the form

 

2.PNG

ZabiBabar
Continued Contributor
Continued Contributor

@Iantaylor2050  Can you please show a screenshot of the formulas I provided in your app? I want to see which fields you are using. And what error you are getting?

Hi @Iantaylor2050 ,

Do you want to :
update your precious record's end date to the new created record's start date-1 when you submit a new record?

Do you use form to submit a new record?

Could you tell me Location field's data type? I assume it is choice data type.

If so, please try this formula:

SubmitForm(Form1);//create new record
Patch(listname,
Last(
      FirstN(
              Filter(listname,Location.Value=Form1.LastSubmit.Location.Value),
              CountRows(listname)-1
             )
      ),
//get the previous record
{'End Date': DateAdd(Form1.LastSubmit.'Start Date',-1,Days)}
//the last submited record's start date-1
)

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi @v-yutliu-msft ,

 

The above formula is working fine. However, instead of updating current record End Date, it should update previous record End Date for that location.

 

For Amsterdam, the 2nd record date End Date should get updated to 22 July instead of the same record EndDate.

 

Picu.PNG

 

 

 

PFB code

 

picu1.PNG

 

 

ZabiBabar
Continued Contributor
Continued Contributor

Hi @Iantaylor2050 , 

Try this logic:

//Saving last added record via filtering based on Location dropdown value
Set(
  LastCityRecord,
  Last(
    Filter(
      'Office Open Status', 
       Location.Value = LocationDropdown.Selected.Value
    ),
  )
);
//Submit the form
SubmitForm(Form2);
//Use the saved record and Form2.LastSubmit to update the 2nd to the last record
Patch(
  'Office Open Status',
   LastCityRecord
  {
     EndDate: DateAdd(
       Form2.LastSubmit.StateDate,
       -1,
     )
   } 
)

 

Best,
Zabi
Mark this post as a solution, or kudo it if you found it be helpful. You can choose more than multiple answers as solutions, including your own answer. 🙂

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,800)