'Agencies' & 'Contacts' N:N - How do I save a new 'Contacts' form *into* its relationship with the selected 'Agency'

[Madumi]

I have two entities, 'Agencies' and 'Contacts' related via an intermediary Entity 'AgencyContactList'... forming a N:N relationship between 'Agencies' and 'Contacts'.

In my powerapp, I have an 'Agency' gallery, from which users can select their desired agency. This populates a second gallery using the entity 'AgencyContactList.' This gallery is set to display the names cross-referenced in it from the 'Contacts' Entity. Finally, users can select a desired individual Contact in the 'AgencyContactList' gallery, which then populates a form where they can edit the Contact's details.

Edit & Save work just fine in this process, but I am stumped how I might create a new contact which can be saved not only to the 'Contacts' entity (in a stand-alone way), but also to gain a corresponding entry in the 'AgencyContactList' entity, relating it to the agency to which it belongs (the agency that was selected/displayed when the new form was triggered).

The primary field in 'Agencies' is an autonumber, as also is the primary field in 'Contacts'

These fields are collected in 'AgencyContactList' using the Columns 'Agency #' and 'Contact #' respectively. Again, 'AgencyContactList has an autonumber as it's primary field.

Is it possible, on OnSelect of a "Save" button, not only to process the new form like so (i.e. to create an orphaned 'Contacts' entity entry):

SubmitForm(ContactForm)

but also to take:

1) the autonumber from the previously selected Agency in the 'Agencies' entity

2) the newly generated autonumber in the newly submitted contact in the 'Contacts' entity

and enter them in a new record in the AgencyContactList Entity?

Any guidance would be hugely appreciated!

thanks!

[poweractivate]

@Madumi 

We are not sure about your specific scenario, but:

1) You should be able to access any attribute of the selected item: Gallery.Selected.'Your Attribute Name'

2) You should be able to access the recently submitted Form entry: Form.LastSubmit.'Your Attribute Name'

[Madumi]

Thanks so much @poweractivate 

I think I follow what you're saying... In order then to add those records to my intermediate entity 'AgencyContactList' am I right thinking that the Collect function is the right way to go about it?

[v-siky-msft]

Hi @Madumi ,

If you have got the autonumbers of  'Agencies' and 'Contacts' entities, you can use Patch function to relate them.

Patch('AgencyContactList', Defaults( 'AgencyContactList'), {'Agency #': LookUp('Agencies', Agency= Gallery1.Selected.Agency), 'Contact #' : LookUp('Contacts', Contact=Form.LastSubmit.Contact)})

Note: I assume Agency and Contact are the field name of two AutoNumber fields.

Sik

[Madumi]

Thanks so very much @v-siky-msft 

All works great with the code you gave, except if I use the formula on my save button to update an existing record. It retrieves the 'Agency #' and 'Contact #' correctly, but generates a new line in the intermediary Entity AgencyContactList which displays as a duplicate record.

Is there a way to have the patch function not generate a new record when updating AgencyContactList?

I tried adding the Autonumber of the record being patched in the intermediary entity AgencyContactList like so:

Patch('AgencyContactList', Defaults( 'AgencyContactList'), {'Agency #': LookUp('Agencies', Agency= Gallery1.Selected.Agency), 'Autonumber':AgencyContactListGallery.Selected.Autonumber, 'Contact #' : LookUp('Contacts', Contact=Form.LastSubmit.Contact)})

 But still it generates a new record.

Is there still a way to have the save button do double duty (add new records to the intermediary entity, as well as save existing records without duplicating them)?

[v-siky-msft]

Hi @Madumi ,

Do you mean you want to update instead of create?  you have to replace the Defaults() part by the record you want to update. Reference: Patch function 

So you can try: 

Patch('AgencyContactList', LookUp( 'AgencyContactList','Autonumber'= AgencyContactListGallery.Selected.Autonumber), {'Agency #': LookUp('Agencies', Agency= Gallery1.Selected.Agency), 'Contact #' : LookUp('Contacts', Contact=Form.LastSubmit.Contact)})

Or

Patch('AgencyContactList', Last( 'AgencyContactList'), {'Agency #': LookUp('Agencies', Agency= Gallery1.Selected.Agency), 'Contact #' : LookUp('Contacts', Contact=Form.LastSubmit.Contact)})

Sik

[Madumi]

Hi @v-siky-msft 

Thank you so very much for the code. I really appreciate it. I still wonder whether I could have the save button serve two tasks: add a record when the form has been set to new record, and alter a record when the form has been populated with a record from a gallery selection.

I am guessing I can toggle between the two functions using an 'if' argument. However, I can't think of a way to determine the difference (when a form has been set to 'new,' it's DisplayMode is 'edit', and similarly when a form has been populated from a gallery selection, it's DisplayMode is 'edit').

Is there a differentiation I can make in an 'if' argument for when a form has been set to NewForm as compared to when a form has been populated from a gallery selection & set to EditForm?

Thanks again!

[poweractivate]

@Madumi 

You can set a variable to track the difference and compare the variable instead if you prefer.

For example, even from the same button onSelect, you can set the variable from there.

If you would like the button to be disabled in between changing states, you can set the variable to empty string onSelect, have the variable set to the actual value from somewhere else perhaps when the other operation has finished, and you can use the DisplayMode parameter of button and put condition - if variable is blank, it is disabled, otherwise it is Edit, so in this case you can also use that same variable to determine what state the button should be in.

If variable is "1" you can have button do one thing onSelect, and if variable is "2" it can do another.

[v-siky-msft · Accepted solution]

Hi @Madumi ,

You're on the right track. Let me give you another push.

The If statement should be like this, Mode is Enumerated type, so you have to use FormMode.New.

If(Form1.Mode=FormMode.New,Patch('SP list',Defaults('SP list'),{}),Form1.Mode=FormMode.Edit, Patch('SP list',Last('SP list'),{}))

Then please try this:

If(
Form1.Mode=FormMode.New,
Patch('AgencyContactList', Defaults( 'AgencyContactList'), {'Agency #': LookUp('Agencies', Agency= Gallery1.Selected.Agency), 'Contact #' : LookUp('Contacts', Contact=Form.LastSubmit.Contact)}),
Form1.Mode=FormMode.Edit,
Patch('AgencyContactList', LookUp( 'AgencyContactList','Autonumber'= AgencyContactListGallery.Selected.Autonumber), {'Agency #': LookUp('Agencies', Agency= Gallery1.Selected.Agency), 'Contact #' : LookUp('Contacts', Contact=Form.LastSubmit.Contact)})
)

Sik

[Madumi]

@v-siky-msft 

The code was perfect (how do you keep track of clauses like that?)!

The only issue I had was where to place

SubmitForm(MemberContactForm);

When I had it prior to the If() argument, the OnSuccess of the form ran & I had code which changed the FormMode back to View, which negated the If() argument :-). I ended up placing it within the If() argument & it seems to work.

Hmmm, I also noticed your If() argument had If(condition,then,condition,then)... & changed it to If(condition,then,If(condition,then)). Is it possible to use an If() argument like that (If...Else)?

many many thanks indeed!