Having trouble updating rows in a gallery

[bobgodin]

I have a Gallery which displays a summary of all records in my Sharepoint lists. From that Gallery I can select a record and navigate to a different screen which has an Edit Form and a Gallery in it. The Form and the Gallery are linked by a "tripID' field which exists in both lists.

When I click on the edit icon from the main Gallery the OnSelect property updates a collection which I continue to work with:

ClearCollect(
    colDetails,
    Filter(
        TripDetails,
        tripid = (Gallery5.Selected.tripid)
    )
);

 On the next screen the Gallery's Items property is simply:

SortByColumns(colDetails,"tripdate")

 In this Gallery I need to add days to an existing trip or change dates and other information about the trip. I have an add row icon and it's properties are:

Patch(
    colDetails,
    Defaults(colDetails),
    {
            tripid: Value(TextInput2_26.Text),
            tripdate: Gallery6_4.Selected.tripdate,
            destination: TextInput2_25.Text,
            activity: TextInput2_27.Text,
            localcontact: TextInput2_28.Text,
            accomodation: TextInput2_29.Text,
            callintimes: TextInput2_30.Text,
            hoursworked: TextInput2_31.Text
    }
);

I can add a row and edit the data but sometimes if I change the date it will change one of the other existing dates and I have to change it back or delete that row and re-add one. That's a minor problem compared with my bigger problem which is when I come to submit the records to my datasource it doesn't work - here is the code for that:

ForAll(
        colDetails,
        Patch(
            TripDetails,
            LookUp(TripDetails,ID=Value(TextInput2_32.Text)),
            {
                tripid: tripid,
                tripdate: DateValue(DatePicker1_1.SelectedDate),
                destination: TextInput2_25.Text,
                activity: TextInput2_27.Text,
                accomodation: TextInput2_29.Text,
                localcontact: TextInput2_28.Text,
                callintimes: TextInput2_30.Text,
                hoursworked: TextInput2_31.Text
            }
      )
   )
);

I don't get any error messages but it doesn't add any new records that I have added to the collection however any edits that I make to existing records do update the Sharepoint list.  I think I am missing something obvious but can't work out what it is - can anyone help please?

[WarrenBelz]

Hi @bobgodin ,

I will tackle the last one first - the only concern here I have is tripdate as you have referred to two different values in the Collection and Patch, however the fundamental error is that you are trying to update from a collection and then refer to control values (rather than Collection fields). The "long" version would look like this

Patch(
   TripDetails,
   ForAll(
      colDetails As aPatch,
      {
         tripid: aPatch.tripid,
         tripdate: aPatch.tripdate,
         destination: aPatch.destination,
         activity: aPatch.activity,
         accomodation: aPatch.accomodation,
         localcontact: aPatch.localcontact,
         callintimes: aPatch.callintimes,
         hoursworked: aPatch.hoursworked
      }
   )
);

however, if all the fields on the Collection exist in the list in both name and field type, you can do this

Collect(
   TripDetails,
   colDetails
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[bobgodin]

thanks @WarrenBelz - but that created new records instead of updating the existing ones - I should be using a LookUp I am thinking? like this:

LookUp(TripDetails,ID=Value(TextInput2_32.Text))

can you tell me where I should put this please?

[WarrenBelz]

@bobgodin ,

OK - I am now a little unclear how you are matching the record ID on each record when you are collecting new records in the collection (hence I believed new records in the list) - where in this code are you storing the ID of the record you want to update and what and where is TextInput2_32 ?

Patch(
   colDetails,
   Defaults(colDetails),
   {
      tripid: Value(TextInput2_26.Text),
      tripdate: Gallery6_4.Selected.tripdate,
      destination: TextInput2_25.Text,
      activity: TextInput2_27.Text,
      localcontact: TextInput2_28.Text,
      accomodation: TextInput2_29.Text,
      callintimes: TextInput2_30.Text,
      hoursworked: TextInput2_31.Text
   }
);

[bobgodin]

@WarrenBelz TextInput2_32 is a textbox in the Gallery which represents the ID column in the SP list

[WarrenBelz]

@bobgodin ,

Then you would do this - collection Patch

Patch(
   colDetails,
   Defaults(colDetails),
   {
      listid: Value(TextInput2_32.Text),
      tripid: Value(TextInput2_26.Text),
      tripdate: Gallery6_4.Selected.tripdate,
      destination: TextInput2_25.Text,
      activity: TextInput2_27.Text,
      localcontact: TextInput2_28.Text,
      accomodation: TextInput2_29.Text,
      callintimes: TextInput2_30.Text,
      hoursworked: TextInput2_31.Text
   }
);

List Patch

Patch(
   TripDetails,
   ForAll(
      colDetails As aPatch,
      {
         ID: aPatch.listid,
         tripid: aPatch.tripid,
         tripdate: aPatch.tripdate,
         destination: aPatch.destination,
         activity: aPatch.activity,
         accomodation: aPatch.accomodation,
         localcontact: aPatch.localcontact,
         callintimes: aPatch.callintimes,
         hoursworked: aPatch.hoursworked
      }
   )
);

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[bobgodin]

@WarrenBelz thank you Warren - it's not working but I haven't told you the full story and that's probably why...

I need to update the records in the Gallery before I Patch to the SP List so I have this code on the OnChange property of the DatePicker and TextInput controls (this is for the destination column):

Patch(
    colDetails,LookUp(colDetails,ID=Value(TextInput2_32.Text)),
    {destination: TextInput2_25.Text}
)

I know it's not working because after I make a change I can go and look at the collection and it hasn't changed so my code must be wrong - it's not giving me any errors though...

Also I don't know how I handle adding a new record (adding a new day to the trip) cos it won't have an ID until it's patched to the SP List eh?

This is my code to create a new record in the collection:

Patch(
    colDetails,
    Defaults(colDetails),
    {
            ID: Value(TextInput2_32.Text),
            tripid: Value(TextInput2_26.Text),
            tripdate: DateValue(DatePicker1_1.SelectedDate),
            destination: TextInput2_25.Text,
            activity: TextInput2_27.Text,
            localcontact: TextInput2_28.Text,
            accomodation: TextInput2_29.Text,
            callintimes: TextInput2_30.Text,
            hoursworked: TextInput2_31.Text
    }
);

 I really appreciate your assistance!

[WarrenBelz]

@bobgodin ,

This code should work assuming you have the ID in your TextInput2_32 (you might consider some control naming protocols here is make life easier)

Patch(
    colDetails,
   LookUp(
      colDetails,
      ID = Value(TextInput2_32.Text)
   ),
   {destination: TextInput2_25.Text}
)

but the only way you are going to get the ID is after the SP record is created (or it simply will not Patch anything) and of course you are creating new record in the collection, which will not have an ID. So unless the record you are editing is in the original collection

ClearCollect(
   colDetails,
   Filter(
      TripDetails,
      tripid = Gallery5.Selected.tripid
   )
);

you will not have an ID to refer to - so do you have any other unique identifying field in the collection - if so, use that for the Lookup.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[bobgodin]

@WarrenBelz I found that the following code works when patching the collection (the OnChange property):

Patch(
    colDetails,ThisItem,
    {destination: TextInput2_25.Text}
)

and I figured out that if I left out the ID field when adding a new record to the collection the Patch to the SP list would just update existing records and patch a new record and create a new ID in the SP list....these blocks of code are working:

Patch(
    colDetails,
    Defaults(colDetails),
    {
            
            tripid: Value(TextInput2_26.Text),
            tripdate: DateValue(DatePicker1_1.SelectedDate),
            destination: TextInput2_25.Text,
            activity: TextInput2_27.Text,
            localcontact: TextInput2_28.Text,
            accomodation: TextInput2_29.Text,
            callintimes: TextInput2_30.Text,
            hoursworked: TextInput2_31.Text
    }
);

Patch(
        TripDetails,
        ForAll(
            colDetails As aPatch,
            {
                ID: aPatch.ID,
                tripid: aPatch.tripid,
                tripdate: aPatch.tripdate,
                destination: aPatch.destination,
                activity: aPatch.activity,
                accomodation: aPatch.accomodation,
                localcontact: aPatch.localcontact,
                callintimes: aPatch.callintimes,
                hoursworked: aPatch.hoursworked
            }
        )
    )
);

the only thing that is not working correctly is when adding a new record to the collection it sets the date to the same as the last date in the Gallery but when I change it to "lastday+1" it changes one of the other records - how can I force it to make the new record have a tripdate which is the latest date+1 day?

my code on the OnChange property of the tripdate DatePicker control:

Patch(
    colDetails,
    ThisItem,
    {tripdate: DateValue(DatePicker1_1.SelectedDate)}
)

[WarrenBelz]

@bobgodin ,

I am a bit unclear on what date you want to use to add days to - currently you are using a Date Picker value.