cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AdrianPeterson
Frequent Visitor

Single Form to update multiple source data

Hi Experts,

 

I'm hoping you can give me some tips trying to resolve following issue.

 

Overview

I'm trying to build a form. I also have few data sources in Dataverse. You can find details below:

 

Image 1.jpg

 

As you can see, the Form is connected to Table 1. Item number and Colour are derived on current selection in Table 1.

 

What I wanted to do is add another field to the form Size.

 

  1. Default value in the Size field should be derived from Table 2 (item number is the same across Table 1 and Table 2). Because FirstItem is selected, I'd expect default value for this field to be M. If SecondItem was selected in Table 1, I'd expect this value to be S
  2. When user clicks on Size field, drop down should display values in Sizes table. At the moment I have it working by adding combo-box with 'Items' property pointing to Sizes table
  3. I think this is most important - when user selects the Size in the form (and the form is submitted), I'd like field Size in Table 2 to be updated for selected item number. For example, when I update the form with Size = L for item SecondItem, I want the Table 2 to have the value in the field Size set to L for selected item number.

 

At the moment I think I could get away with creating 2 separate forms and play with the layout to make an 'illusion of a single' form. Is there a better way to achieve that?

 

I should note that the solution doesn't necessarily have to be isolated to the canvas app design, if there is a better way to address this issue by e.g. creating new fields/relations in Dataverse.

1 ACCEPTED SOLUTION

Accepted Solutions
StalinPonnusamy
Community Champion
Community Champion

Hi @AdrianPeterson 

 

Option 1: Submitform(Editform). Submit form updates dataverse. And another Patch command on OnSuccess property of the form to update Source2 (ERP)

 

Option 2: Have 2 Patch commands OnSelect property of Save button (1 for table1(Dataverse) and another one Table2[ERP])

Patch(
    SPList,
    LookUp(
            SPList,
            ID = SelectedId
    ),
    {
        ItemNumber: DataCardValue1.Text,
        Colour: ComboboColour.Selected.Value
    }
);

Patch(
    ERPSource,
    LookUp(
            ERPSource,
            ID = SelectedId
    ),
    {
        Size: ComboboSize.Selected.Value
    }
);

 

View solution in original post

6 REPLIES 6
StalinPonnusamy
Community Champion
Community Champion

Hi @AdrianPeterson 

 

Color and Size may not grow much. So this could be the Choice field. No table is necessary. By looking at your requirement, only one table is sufficient. The table should have Item Number, Color, and Size.

 

StalinPonnusamy_0-1632543293960.png

 

Let me know if I misunderstood anything.

I might've oversimplified the example.  I should've said that table breakdown as in the picture above was done on purpose.

 

In the real life implementation, I will be connecting to the ERP (D365 Fin Ops) tables. Sizes won't really be sizes but setup tables where new values can be constantly added, replicating this as a choice table in Dataverse is not an option.

 

Table 1 is a custom table I've created in Dataverse. I can play with all the fields, however, Table 2 and Table Sizes are D365FO tables. I don't have the ability to change/redefine them but rather I want to interact with them via canvas app.

 

In a nutshell, I want to add new fields (Table 1) and update native ERP tables (Table 2) - hence the question whether I can have a single form updating data in both tables.

 

I'll gladly welcome any suggestions

StalinPonnusamy
Community Champion
Community Champion

Hi @AdrianPeterson 

 

Option 1: Submitform(Editform). Submit form updates dataverse. And another Patch command on OnSuccess property of the form to update Source2 (ERP)

 

Option 2: Have 2 Patch commands OnSelect property of Save button (1 for table1(Dataverse) and another one Table2[ERP])

Patch(
    SPList,
    LookUp(
            SPList,
            ID = SelectedId
    ),
    {
        ItemNumber: DataCardValue1.Text,
        Colour: ComboboColour.Selected.Value
    }
);

Patch(
    ERPSource,
    LookUp(
            ERPSource,
            ID = SelectedId
    ),
    {
        Size: ComboboSize.Selected.Value
    }
);

 

View solution in original post

AdrianPeterson
Frequent Visitor

Thanks @StalinPonnusamy this is a really good suggestion. However, before I try it out, I seem to be stuck on 'Default'.

 

The field in a form should display the value from Table 2 as in the picture below. Do you know if 'LookUp' is the right way?

 

AdrianPeterson_0-1632918303492.png

 

StalinPonnusamy
Community Champion
Community Champion

Hi @AdrianPeterson

 

Remove ",Size" on the lookup

 

Default expects Record

LookUp('Table 2S'.'Item Number' = varSelectedItemId)
LookUp(Table2, Condition)

 

AdrianPeterson
Frequent Visitor

Thank you @StalinPonnusamy everything including Patch suggestions you made earlier worked like a charm, impressive 😊

 

I've marked your comment as accepted solution. Have a great day.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,213)