cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
monatech
New Member

Dataverse - Many to Many - Patching

Hi Everyone,

 

I am just dipping my toe into the dataverse and have come across a little issue that I am hopefully someone can help guide me on.

I have created two tables, a Department table and a Courses table

 

A department can be assigned many courses and a course can have many departments.

 

I believe i have all configured correctly on the dataverse side, however when i add a patch command in a canvas app strange things happen.

 

1. I use the code below on a button to patch a record into the departments table and then relate that to selected courses

 

ClearCollect(
coltraining,
Gallery4.AllItems
);
Patch(
Departments,
{
Name: TextInput1.Text,
Manager: Dropdown1.SelectedText
}
);
Refresh(Departments);Set(
newDepartment,
Last(Departments)
);ForAll(
coltraining,
Relate(
newDepartment.Trainings,
ThisRecord
)
);
Reset(TextInput1);
Reset(Dropdown1);
Reset(lstboxavailablecourses);
Clear(colselectedcourses);
Clear(coltraining);

 

However I am getting strange behaviour with this, as when I add a new department and relate some courses to it, the previously added department also gets those courses. I think this maybe due to a timing issue as the newDepartment variable may not have time to update before the patch kicks in. to get around this i split the command up.

 

First I put this code in a button

 

ClearCollect(
coltraining,
Gallery4.AllItems
);
Patch(
Departments,
{
Name: TextInput1.Text,
Manager: Dropdown1.SelectedText
}
);
Refresh(Departments);Set(
newDepartment,
Last(Departments)
);Set(timerreset,false);Set(timerstart,true);

 

Then in the Timer I put this code.

 

ForAll(
coltraining,
Relate(
newDepartment.Trainings,
ThisRecord
)
);
Reset(TextInput1);
Reset(Dropdown1);
Reset(lstboxavailablecourses);
Clear(colselectedcourses);
Clear(coltraining);Refresh(Departments);Refresh(Trainings);Set(timerreset,true);Set(timerstart,false)

 

The theory been that, the button would be pressed the timer would run for 2 seconds, giving enough time for the refresh to complete and the last record to be returned, and it seems to work. Not sure if it is logically but it works. Could someone tell me if this is expected behaviour or perhaps I am missing something obvious.

 

2. The second issue may well stem from the above setup. But when the timer ends I get the following error on it.

 

powerapp_error.JPG

 

 

The records seem to add OK, but this error always appears. Is it telling me that a I am trying to add a record to the departments table that already exists, I dont see how as I am using unique values for the departments.

 

Again, if anyone can help with this it would be much appreciated.

 

Thanks

 

Ronan

1 ACCEPTED SOLUTION

Accepted Solutions
v-xiaochen-msft
Community Support
Community Support

Hi @monatech ,

 

Please use table directly instead of using a collection (collection storage table).

 

So please try this formula:

Patch(
Departments,
{
Name: TextInput1.Text,
Manager: Dropdown1.SelectedText
}
);
Refresh(Departments);Set(
newDepartment,
Last(Departments)
);ForAll(
Trainings,
Relate(
newDepartment.Trainings,
ThisRecord
)
);
Reset(TextInput1);
Reset(Dropdown1);
Reset(lstboxavailablecourses);
Clear(colselectedcourses);
Clear(coltraining);

 

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

View solution in original post

2 REPLIES 2
v-xiaochen-msft
Community Support
Community Support

Hi @monatech ,

 

Please use table directly instead of using a collection (collection storage table).

 

So please try this formula:

Patch(
Departments,
{
Name: TextInput1.Text,
Manager: Dropdown1.SelectedText
}
);
Refresh(Departments);Set(
newDepartment,
Last(Departments)
);ForAll(
Trainings,
Relate(
newDepartment.Trainings,
ThisRecord
)
);
Reset(TextInput1);
Reset(Dropdown1);
Reset(lstboxavailablecourses);
Clear(colselectedcourses);
Clear(coltraining);

 

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

View solution in original post

monatech
New Member

Sorry for the long wait, thanks so much thats working the best

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (2,517)