Problem with relationships and Patch function

[daruom]

Hello,

I can't use relationships in PowerApps.

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

Order list

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

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

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

[Maceo]

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

[daruom]

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]

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

[daruom]

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

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

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

[BCBuizer]

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:

[daruom]

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]

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

[daruom]

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]

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?