Collection collected from Sharepoint duplicating exisitng items when new items added

[Datanerd]

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

Expenses Table

SharePointID	Name	Amount
1	Expense1	1000
2	Expense2	2000
3	Expense3	3000

Expenses Items Table

SharePointID	Category	Amount	Date	ExpenseID
1	Food	500		1
2	Drinks	500		1
3	Airfare	3000		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

[RandyHayes]

@Datanerd 

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

[Datanerd]

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

[RandyHayes]

@Datanerd 

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

[EdViegas]

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

[Datanerd]

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

[Datanerd]

Hi, I do have it, The expensesID.

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

[RandyHayes]

@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.

[Datanerd]

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]

@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.