cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Madumi
Level: Powered On

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-siky-msft
Microsoft

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

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

View solution in original post

10 REPLIES 10
dynamicsedge
Level 10

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

@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
Level: Powered On

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

Thanks so much @dynamicsedge 

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?

Microsoft v-siky-msft
Microsoft

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

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
Level: Powered On

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

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)?

Microsoft v-siky-msft
Microsoft

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

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
Level: Powered On

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

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!

dynamicsedge
Level 10

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

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

Microsoft v-siky-msft
Microsoft

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

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

View solution in original post

Madumi
Level: Powered On

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

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

Helpful resources

Announcements
New Ranks and Rank Icons in April

'New Ranks and Rank Icons in April

Read the announcement for more information!

Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (9,777)