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

 

Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.

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

 

Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.

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
          }
    }
)
Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.

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?

Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

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.

Users online (1,796)