Patch with Defaults to create record - code not working (?)

[Madumi]

I'm wondering if someone can point me to the error in my code

I am trying to add a record to an intermediary entity 'Member Agency Contact Lists'. Being an intermediary entity (creating a N:N relationship), it references the primary fields of two other entities: 'Member Agencies' and 'Member Contacts.' These primary fields are both labelled 'Autonumber' in those two entities. Within the intermediary entity, those (referenced) Autonumbers are labelled 'Agency #' and 'Contact #" respectively, and it has it's own primary field, also labelled 'Autonumber.'

I had hoped to use the selected fields within MemberAgencyGallery (which displays the entity 'Member Agencies) and ExistingContactsGallery (which displays the entity 'Member Contacts'), so that OnSelect of an icon (within ExistingContactsGallery), those selected fields would be entered in a new record in the intermediary entity 'Member Agency Contact Lists'

I am trying the following code, and when I press the icon, PowerApps seems to be doing something, but the record doesn't *seem* to be added to the intermediary entity.

Select(Parent);
Patch(
    'Member Agency Contact Lists',
    Defaults('Member Agency Contact Lists'),
    {
        'Agency #': LookUp(
            'Member Agencies',
            Autonumber = MemberAgencyGallery.Selected.Autonumber
        ),
        'Contact #': LookUp(
            'Member Contacts',
            Autonumber = ExistingContactsGallery.Selected.Autonumber
        )
    }
)

[PaulD1]

Hi

Some thoughts...

What datasource are you using? If SQL, make sure you have a primay key (can be the combination of the two fields) - you can't patch into a SQL table without a primary key.

Are those fields the only fields required? 

Are you attempting to patch a combination that already exists (violates a key or constraint)?

Have you tried hard coding values as a test?

Note that if you are referencing the Selected property of a gallery on a different screen, PowerApps has an intermittent bug where it does not always return the actual item selected (sometimes it just returns the first item in the gallery) - instead of referencing the gallery directly, set a gloabal variable (or pass a context variable to the screen that performs the patch) and set the value of the variable when a gallery item is selected.

[Madumi]

I'm using Common Data Services & yes, those Autonumbers are the only fields required to make the N:N relationship.

I tried hard coding the values as a test, but couldn't get the formula right (data type mismatch), so gave up.

and yes, the formula references items on the same screen.

[PaulD1]

If you are using CDS, you may need to use the Relate function for an N:N relationship.

[v-siky-msft]

Hi @Madumi ,

Do you miss any required fields?

'Agency #' and 'Contact #' are both lookup fields, but i cann't find any primary field.

Sik

[Madumi]

Thx @v-siky-msft 

The primary field of the entity is an autonumber on the CDS level (the datatype is set to autonumber in the field properties of the entity)... if that helps...

The code works if I shift it to a button external to the gallery (pre-selecting the record I want on the gallery) with code as follows:

Patch(
    'Member Agency Contact Lists',
    Defaults('Member Agency Contact Lists'),
    {
        'Agency #': LookUp(
            'Member Agencies',
            Autonumber = MemberAgencyGallery.Selected.Autonumber
        ),
        'Contact #': LookUp(
            'Member Contacts',
            Autonumber = ExistingContactsGallery.Selected.Autonumber
        )
    }
)

But when I use the same code on an icon (OnSelect) within the gallery, when I press the icon, it does nothing (no data has been entered when I check the 'Member Agency Contact Lists' entity records)

I tried cutting the (working) button from outside the gallery & pasting it within the gallery, but again, it refuses to work once it's in the gallery.

Hmmm, now that I think about it, the code is doing a lookup on ExistingContactsGallery within the ExistingContactsGallery gallery. Is that possibly causing a problem? If so, any suggestions how I would work around it?

Or is there some other issue causing the failure?

[v-siky-msft · Accepted solution]

@Madumi  Do you mean the code is applied to the button inside of the 'Member Contacts' gallery? 

Please try this:

Patch(
    [@'Member Agency Contact Lists'],
    Defaults([@'Member Agency Contact Lists']),
    {
        'Agency #': LookUp(
            'Member Agencies',
            Autonumber = MemberAgencyGallery.Selected.Autonumber
        ),
        'Contact #': ThisItem
        )
    }
)

Sik

[Madumi]

Brilliant! That works... (without the close bracket after ThisItem).

I had tried using ThisItem (instead of Lookup) & hadn't gotten it to work (without the initial square brackets).

Where can I learn the difference in the formula structure?

Thanks again!

[v-siky-msft]

Please check this.https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/working-with-tables#disambiguation