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

[Datanerd]

Thank you so much @RandyHayes, I've learned a lot.

[Datanerd]

Hi sorry, quick query where is the ISchanged coming from, from your formula?

[RandyHayes]

@Datanerd 

Sorry, that should have been "Changed"  

It was also just a suggestive column for your collection in order to keep track of changes to limit the data submitted.

[Datanerd]

So I updated the formula, and greatly it's not duplicating the only issue is it's creating new data on an update.
I have a save icon on each row in the gallery where am patching the changed column to true if an update has been carried out.

Patch(
    SharePointListName,
    ForAll(
        Filter(
            Mycollection,
            Changed = "true" || IsBlank(ExpensesID) 
        ) As _item,
        With(
            LookUp(
                GalleryGrid_4.AllItems, //Gallery with the line items
                ID = _item.ID //(This is the ID for the gallery)
            ),
            {
                Title:TitleInput.Text,
                AmountinKSh: Value(AmtInput_4.Text),
                Curr: CurrInput_4.Selected.Result,
                ExpensesID: GalleryExpensesMaster.Selected.ID, 
                Status: "Pending"
            }
        )
    )
);

[RandyHayes]

@Datanerd 

So a couple things here:

1) If you are providing a save button on each row...then why not just save to the datasource at that point?  You're kind of doubling the work for your user - they need to make the change, then click the save button then at some point submit all the saves.  I personally would just save to the datasource at that point since you have that button.  Or, I would remove the button and just have the current row marked as "changed" to then be used on the submit later.

2) If you are only getting new records created, then your collection is not including the ID properly.  How are you populating the collection to start with?

[Datanerd]

I figured to have the save button just to keep track of all the changes done per row. Onselect of the save button, I'm patching to the collection(Mycollection, ThisItem, If(IsBlank(TitleInput_4.Text),ThisItem.Title,TitleInput_4.Text, ID:ThisItem.ID, ExpenseID:0....)}).
Since am submitting both the form and the collection, I have two buttons for Draft and Submit, and onsuccess of the form is where the final patching happens.
I get the formID which relates the items by patching the previous gallery that has the mainExpense ExpenseID.

[RandyHayes · Accepted solution]

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

[Datanerd]

Thank you a million times for being detailed and patiently schooling me :).

The solution works amazing.