Patching/Updating an MTM Table

(0) Share

Report

Posted on by DCHammer

716 Moderator

I'm really close here but my ForAll is always creating new records in my MTM table.

Goal, only create 'new' records in the MTM table when the form is successfully submitted.

A gallery is populated with rows as text is input in a text control. Thanks to WarrenBelz for an amazing With statement to get this done.

When the form is submitted, I want to record in an MTM table which specific FAQ items were in the gallery at the time.

Logic is in the OnSuccess of the form. It actually works as it is now but saves new records every single time rather than respecting any that exist and only adding new values.

// Clear existing collection
ClearCollect(
    colQFAQ,
    galItemFAQ.AllItems
);
// Collect existing records
ClearCollect(
   colExistingFAQ,
   Filter('Comp Query - QueryMTMFAQ', QueryID = varItem.ID)
);
// Add/Update records in QueryMTMFAQ
ForAll(
   Filter(colQFAQ, IsBlank(LookUp(colExistingFAQ, FAQID = Value(ID)))),
   Patch(
      'Comp Query - QueryMTMFAQ',
      Defaults('Comp Query - QueryMTMFAQ'),
      {
         QueryID: varItem.ID,
         FAQID: Value(ID)
      }
   )
);

// Remove records not in current submission
ForAll(
    Filter(
        colExistingFAQ,
        Not(FAQID in colQFAQ.ID)
    ),
    Remove(
        'Comp Query - QueryMTMFAQ',
        ThisRecord
    )
);

My problem is in the ForAll loop that does the Patch I'm sure but cannot figure out what is wrong. I suspect it is in the Filter. It's not filtering correctly and then creating a duplicate for a row that already exists.

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Sort by

TrevorMorrice 81 on at

Like
a
(0)

Report
Copy link

Link copied!
How does your colQFAQ records get their ID?

Id assume its ID is blank so your filter logic will return true

Filter(colQFAQ, IsBlank(LookUp(colExistingFAQ, FAQID = Value(ID)))),

Was this reply helpful? Yes No
ronaldwalcott 3,906 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
You are explicitly telling it to create new records by using Defaults.

Patch( 'Comp Query - QueryMTMFAQ', Defaults('Comp Query - QueryMTMFAQ'), { QueryID: varItem.ID, FAQID: Value(ID) }

Was this reply helpful? Yes No
TrevorMorrice 81 on at

Like
a
(1)

Report
Copy link

Link copied!

@ronaldwalcott He is trying to filter out the old records first but its failing to work is my understanding.

Was this reply helpful? Yes No
DCHammer 716 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@TrevorMorrice You're right about the filter. I built it one little step at a time and added that Filter inside the ForAll to limit the records to process.

ID is in fact there in the colQFAQ.

It's populated inside the Items property of the gallery that feeds the ClearCollect.

I'm sure that Filter statement is wrong.

Now that I reread what I wrote, I can't make sense of it. :D

Filter(colQFAQ, IsBlank(LookUp(colExistingFAQ, FAQID = Value(ID))))

Is the problem that I'm trying to reference ID which is a value from the collection inside the ForAll? I know there is a problem with doing that somehow but I don't really understand.

Maybe what I should do is filter colQFAQ into a filtered collection and then use that in the ForAll.

Or maybe there is a better way to do this entirely.

Was this reply helpful? Yes No
DCHammer 716 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
It is definitely my Filter. I added a ClearCollect to create a filtered version of colQFAQ with this:

ClearCollect( colFilteredQFAQ, Filter( colQFAQ, IsBlank( LookUp( colExistingFAQ, FAQID = Value(ID) ) ) ) );

And it's not filtering anything out. I'm struggling with the comparison of the two collections. What I want it to do is toss out any record in colQFAQ where the ID equals the FAQID in colExistingFAQ which holds the MTM records.

Was this reply helpful? Yes No
Verified answer

DCHammer 716 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
I win. LOL.

//Clear existing collection ClearCollect( colQFAQ, galItemFAQ.AllItems ); //Collect existing MTM records ClearCollect( colExistingFAQ, Filter( 'Comp Query - QueryMTMFAQ', QueryID = varItem.ID ) ); //Filter the gallery collection for existing MTM records ClearCollect( colFilteredQFAQ, Filter( colQFAQ, !(ID in Filter('Comp Query - QueryMTMFAQ', QueryID = varItem.ID).FAQID) ) ); //Add records in QueryMTMFAQ ForAll( colFilteredQFAQ, Patch( 'Comp Query - QueryMTMFAQ', Defaults('Comp Query - QueryMTMFAQ'), { QueryID: varItem.ID, FAQID: Value(ID) } ) ); //Remove records not in current submission ForAll( Filter( colExistingFAQ, !(FAQID in colQFAQ.ID) ), Remove( 'Comp Query - QueryMTMFAQ', ThisRecord ) );

Since I couldn't figure out how to do it inside the ForAll, I filtered the collection and made a new one. I know this is a performance issue possibly but I can't write the With statement to make it all work in memory.

This now works as desired.

Was this reply helpful? Yes No