cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
daruom
Helper II
Helper II

Problem with relationships and Patch function

Hello,

I can't use relationships in PowerApps.

I have 3 lists: "Product", "Order" and "ProductOrder".

 

Order listOrder list

"Product" and "Order" only contain the "Title" column with required value.

In "Product", I insert a record to perform the tests.

 

ProductOrder listProductOrder list"ProductOrder" contains 2 columns. their type is "Search" with required value. This search points to the "Id" column of the "Product" and "Order" lists.

 

 

FkProductId fieldFkProductId field

 

 

 

 

I would like to insert a record in the "ProductOrder" list when I create a record in the "Order" list.

 

I have tried with the following formula :

 

Set(newId; Patch(Order; Defaults(Order); {Title: DataCardValue1.Text}).ID);;
Patch(ProductOrder; Defaults(ProductOrder); {FkProductId: 2 ;FkOrderId: newId})

 

 

Here is the error message: "The type of this argument 'FkOrderId' does not correspond to the expected type 'Record'. Found type 'Number'".

How do you manage relationships the right way ?

 

Thanks.

11 REPLIES 11
Maceo
Frequent Visitor

Hi daruom,

 

Problem here is because the FkOrderId is the LookUp type.

The variable 'newId' you set in the previous formula, it's number type.

So when you patch an different type for this field, it will occur error message.

 

Here is my solution;

1.

 

Set(
    newId, 
    Patch(
        Order,
        Defaults(Order),
        {Title: DataCardValue1.Text}
    )
);
Patch(
    ProductOrder, 
    Defaults(ProductOrder), 
    {
        FkProductId: 2,
        FkOrderId: newId
    }
)

 

2.

 

Set(
    newId, 
    Patch(
        Order,
        Defaults(Order),
        {Title: DataCardValue1.Text}
    ).ID
);
Patch(
    ProductOrder, 
    Defaults(ProductOrder), 
    {
        FkProductId: 2,
        FkOrderId: LookUp(Order, ID = newId)
    }
)

 

 

Maybe there will be some spelling issue, please correct them. Hope this can help you

Thanks,

Maceo

 

Hello,

I tried the formula (with the corrections for French), but I still have 2 errors (on the 2nd Patch).

"Invalid argument type. Record values expected but with a different schema."
and
"Missing column. Your formula does not have an 'Id' column with a type 'Number'

BCBuizer
Super User
Super User

Seems similar to the below topic:

 

Solved: Missing a column 'Id' with a type of 'Number' - Power Platform Community (microsoft.com)

 

Can you try:

Set(
    newId, 
    Patch(
        Order,
        Defaults(Order),
        {Title: DataCardValue1.Text}
    )
);
Patch(
    ProductOrder, 
    Defaults(ProductOrder), 
    {
        FkProductId: 2,
        FkOrderId:
          {'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
          Id: NewID.ID
          Value: NewID.value
          }
    }
)

 

I had seen this thread, but it didn't work for me.

The error are:
"Function 'Patch' contains invalid arguments".

formula.PNGformula2.PNG

I have also searched for documentation regarding this syntax, but I only find threads that show examples in different contexts.

BCBuizer
Super User
Super User

Remove the highlighted ".id" as it sets the datatype of the variable to just the ID whereas you need to capture the record. Also check your interpunctions:

 

BCBuizer_0-1625071930041.png

 

Hello,

I was only able to test now.
I deleted the ".id" but the problem is still there.

Set(
    newId;
    Patch(
        Order;
        Defaults(Order);
        {Title: TextInput1.Text}
    )
);;
Patch(
    ProductOrder; 
    Defaults(ProductOrder);
    {
        FkProductId: 2;
        FkOrderId:
          {'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference";
          Id: newID.ID
          Value: newID.value
          }
    }
)


interpunctions is normal. In French, we don't use commas.

BCBuizer
Super User
Super User

Added an extra ";" in your second patch:

Set(
    newId;
    Patch(
        Order;
        Defaults(Order);
        {Title: TextInput1.Text}
    )
);;
Patch(
    ProductOrder; 
    Defaults(ProductOrder);
    {
        FkProductId: 2;
        FkOrderId:
          {'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference";
          Id: newID.ID;
          Value: newID.value
          }
    }
)

Same, I had already tried

Set(
    newId;
    Patch(
        Order;
        Defaults(Order);
        {Title: TextInput1.Text}
    )
);;
Patch(
    ProductOrder; 
    Defaults(ProductOrder);
    {
        FkProductId: 2;
        FkOrderId:
          {'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference";
          Id: newID.ID;
          Value: newID.value
          }
    }
)

 

I tried a lookup on the product part but without success.

Set(
    newId;
    Patch(
        Order;
        Defaults(Order);
        {Title: TextInput1.Text}
    )
);;

Set(product1; LookUp(Product; ID = 2));;

Patch(
    ProductOrder; 
    Defaults(ProductOrder);
    {
        FkProductId:
          {'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference";
          Id: product1.ID;
          Value: product1.value
          };
        FkOrderId:
          {'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference";
          Id: newID.ID;
          Value: newID.value
          }
    }
)
BCBuizer
Super User
Super User

Set(
    newId;
    Patch(
        Order;
        Defaults(Order);
        {Title: TextInput1.Text}
    )
);;

Set(product1; LookUp(Product; ID = 2));;

Patch(
    ProductOrder; 
    Defaults(ProductOrder);
    {
        FkProductId:
          {'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference";
          Id: product1.ID;
          Title: product1.Title
          };
        FkOrderId:
          {'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference";
          Id: newID.ID;
          Title: newID.Title
          }
    }
)

 

If the above doesn't work, can you please include the errors you are receiving?

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (895)