cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Datanerd
Frequent Visitor

Collection collected from Sharepoint duplicating exisitng items when new items added

I have a canvas expense system connected to two SharePoint lists: Expenses and expense line items. 

Expenses Table

SharePointIDNameAmount
1Expense11000
2Expense22000
3Expense33000

 

Expenses Items Table

SharePointIDCategoryAmountDateExpenseID
1Food500 1
2Drinks500 1
3Airfare3000 2

 

The ExpenseID is created when submitting the form to relate the items. My flow;

 

  1. I have a submission gallery, that stores the expense items, and OnSelect collects from SharePoint expense items related,(ColRelatedItems). And navigates to the view items screen.
  2. The view item has the form(Expensesheader) and a gallery(Expenserelateditems) with the collection(ColRelatedItems) as the items.
  3. There is a button that adds an empty row to the gallery in case the user wants to add new items, this new item doesn't have an ExpenseID yet.
  4. There is a button that finally patches the items (New and updated) to the collection which will later be pushed to collection on submission.

I have a formula that unfortunately duplicates the items that previously existed in the collection when patching back to sharepoint.
here is my formula;

 

 

ForAll(ColRelatedItems,

<Checks the new empty row created is not uploaded and the expenseID is empty>
If(!IsBlank(Title) && IsBlank(LookUp(ExpenseRelatedItems, ExpensesID = GalleryExpensesDetail.Selected.ID)),

Patch(ExpenseRelatedItems,Defaults(ExpenseRelatedItems),{Title:Title,Amount:Amount,ExpensesID:GalleryExpensesDetails.Selected.ID}),

<Checks the new empty row created is not uploaded and the expenseID exists>
If( !IsBlank(Title) && !IsBlank(LookUp(ExpensesItems, ExpensesID = GalleryExpensesDetail.Selected.ID)), Patch(Title:Title,Amount:Amount,ExpensesID:GalleryExpensesDetails.Selected.ID})
)));

 

 

Not sure what I could be doing wrong with the formula

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@Datanerd 

Keep in mind that doing a Patch on a collection using "ThisItem" can lead to issues.  It is not what you expect.  There is not direct link of a gallery row to a collection row by itself.  Patch has to try and look up what is in the "ThisItem" record to then patch that record in the collection.

 

Let me explain.

 

So, let's say you have two records in your collection and they are shown in a gallery:

The two records:  {Title: "", Value: 0}, {Title: "", Value: 0}

Now, let's say you have a means to edit the Title in your gallery and you edit the second row in the gallery and then issue a : Patch(myCollection, ThisItem, {Title: TextInput.Text})

This will cause a problem because it will patch the first record of the collection...not the second.  This is because ThisItem is a record and that record is {Title: "", Value: 0} which is the same as the first record!

You cannot rely on "ThisItem" to be the underlying record!!

 

So, the solution I provided you accounts for all of that.  

 

This was very specifically accounted for in the OnChange action formula I provided:

If(!IsBlank(ThisItem.ID),
    UpdateIf(yourCollection, ID=ThisItem.ID, {Changed: true})
)

Everything is based on the ID.  This is the key to everything (thus the primary key)

 

If you fill your collection with existing records with a formula such as:  

ClearCollect(myCollection, Filter(myData, columnX=value))

Then every record will have an ID.  That is essentially all we care about - except for the initial display of values - like if there was a Title textinput, we would set the Default of that to: ThisItem.Title  But after that, we don't care about .Title any longer.  The TextInput now contains the value that we are interested in.

 

With the OnChange formula above on the TextInput control, it will very specifically look at the ID column.  If it exists, it will update THAT record with a Changed column set to true.  That is it.  We don't care about trying to capture all the values in the gallery because we will get them later.  Doing so is just adding more work.

 

Finally, in the Patch function:

Patch(ExpenseRelatedItems,    
    ForAll(
        Filter(yourCollection, Changed || IsBlank(ID)) As _item,
        With(LookUp(GalleryExpensesDetails.AllItem, ID=_item.ID),
            {ID: ID,
             Title: TitleTextInputControl.Text,
             Amount: Value(AmountTextInputControl.Text),
             TravelExpensesID: GalleryExpensesDetails.Selected.ID
            }
        )
   )
)

We are very specifically filtering for any record that has a Changed column that is true.  OR any record that has no ID.

We then put all of that together in a table (The ForAll) with records that correspond to the datasource record needed.  We supply the ID of the record from the gallery...if there is one, then the Patch will update that record in the datasource, if not (blank), then it will create one.

 

This can all be done in an OnSuccess action of a form and can reference any LastSubmit record data that was submitted in the form submit.

 

Hopefully that all makes sense.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

17 REPLIES 17
RandyHayes
Super User
Super User

@Datanerd 

Your formula has some errors in it...but what is patchstuff??

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Hi, It's formula inside the patch for the columns to be updates. {Total : Total,...}

RandyHayes
Super User
Super User

@Datanerd 

How is it defined?  Your formula is not defining it.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
EdViegas
Super User
Super User

I advise you to create a lookup column in Expenses Items to Expenses (selecting ID for "In this column" field)!

Uhmm not sure what that means, but I've updated my formula to show the patch example

Hi, I do have it, The expensesID.

 

It seems to be working only that it's duplicating the existing items.

RandyHayes
Super User
Super User

@Datanerd 

Ah, see I thought you were copying and pasting your exact formula...now I see you were just shorthanding it in your post.

 

So my suggestion is to ditch the collection and work from your gallery where you already have the data.  And also to use the ForAll as the function it was designed to be (it is not a For Loop!)

 

Your formula would be more like this:

Patch(ExpenseRelatedItems,    
    ForAll(
        Filter(GalleryExpensesDetails.AllItem, !IsBlank(TitleInputControl.Text)),
        {ID: ID,
         Title:Title,
         Amount:Amount,
         TravelExpensesID: GalleryExpensesDetails.Selected.ID
        }
    )
)

The point of the above being that your gallery is already populated by the records from the datasource and it will have the correct ID needed.

Patch is smart enough to know when to create and when to update.  If you supply an ID, then it will update.  If the ID is blank, then it will create.

The above creates a table of records to create and/or update (that's the purpose of the ForAll) and then provides that table to Patch to act on the records.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Thanks for this insight.

Just to clarify, I have two Galleries. 
 1. Has the expenseHeaderDetails(GalleryExpenseDetails) -  when an item on this is selected, I create a collection that pulls all the related expense line items with the expenseID.
2. The second gallery(GallerGrid), gets populated by the just pulled-in data ( The expense line items), this allows for the addition of new items that come without the expenseid and any updates on the existing ones.
so I push this data back to SP using the formula, hence I reference the first gallery's ID as the expenseID in the patch.

 

RandyHayes
Super User
Super User

@Datanerd 

So don't get caught up in the collection trap!  You will need a collection only for the purpose of being able to add records to a gallery.  Primarily, the only thing the collection needs to contain is the primary keys for the records (or blank if new).

Your formula to update and change records should base itself on the gallery - not the collection.  Otherwise you get into the wasted time of doing OnChange actions and such to update the collection...You need not waste the effort.  The gallery will have the values you want.

 

The only one suggestion is to use the OnChange actions to "tag" your record in your collection as changed.  This way you can filter just the changed and new records for your patch function.

If(!IsBlank(ThisItem.ID),
    UpdateIf(yourCollection, ID=ThisItem.ID, {Changed: true})
)

 

The formula mentioned would do what you need from that.  Based on what you have mentioned though, the following changes would/should be made:

Patch(ExpenseRelatedItems,    
    ForAll(
        Filter(yourCollection, IsChanged || IsBlank(ID)) As _item,
        With(LookUp(GalleryExpensesDetails.AllItem, ID=_item.ID),
            {ID: ID,
             Title: TitleTextInputControl.Text,
             Amount: Value(AmountTextInputControl.Text),
             TravelExpensesID: GalleryExpensesDetails.Selected.ID
            }
        )
   )
)

 

That would perform the action you need.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

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