Patching to lookup dataverse table

[Ludumo]

Hi, I am trying to build a product ordering (ecoomerce) model-driven app with custom command button to add product to order. The product table in dataverse is a lookup in the order table in dataverse. I would like to be able to select a product from a subgrid and add it to the order thereby allowing me to add multiple products to the order (sort of like an "Add to Cart" button). I've been advised to use a custom command but I'm struggling with the patch function. Please help. I've tried a number of different ways to get this done, please see screenshots for reference. Please let me know if my request is possible or an alternative way to accomplish the same task.

[Amik]

@Ludumo - As I understand it based on those screenshots:

You have a Dataverse Table called "Products". You have another Dataverse table called "Orders".

The Orders table contains a LookUp field to the Products table. I assume the relationship cardinality is one-to-many from the Products table (one) to the Orders table (many).

What you're trying to do

You want to Patch the LookUp column in the Orders table with a specific record in the Products table. Your formulas in both screenshots are all over the place so it is difficult to understand how you're trying to do it. In one screenshot you're trying to do it from what looks like a ComboBox/Dropdown selection, and in the other screenshot you're trying to do it by manually entering in the record.

As a basic example, suppose we have a ComboBox control and in the Items property, we enter:

Choices([@Orders].'Our LookUp Column Name')

On a Button control, we can then enter:

Patch(
    Orders,
    Defaults(Orders),
    {
        'Our LookUp Column Name': ComboBox1.Selected
    }
)

[Ludumo]

Hi @Amik , thanks for the reply. I'm not too familiar with powerfx so I was trying formulas based on youtube and other resources. Please could you guide me on how to structure the formula to populate the Order table with the Product from the Lookup. 

As mentioned, I need the functionality to be akin to adding products to cart.

Process: Select product/s from subgrid with products. When I press the "Add to Cart" button the products are added to my order table which has a lookup to products field called "Ordered Product". 

You are correct, there is a combobox, but please disregard as I was trying different ways of populating/patching to the table.

[Amik]

@Ludumo - What I wrote above already should give you the structure you need. I cannot help you further because you have not explained what the formula should be "looking up" in the first place.

[Ludumo]

@Amik , My product table has Product name, description, category, type, price and minimum quantity as fields.

When I select a product, I would like to add this product to the Order table. 

I would like to select a product from this subgrid and click on "Add to Order". When I do that, it needs to populate the Order table. The product will show as "Product Ordered"

I hope this will give more context or perhaps give you enough information to advise an alternative course of action.

Thanks

[Amik · Accepted solution]

@Ludumo - again, it is up to you to decide what the business rule/logic should be to define what record should be populated in the "Ordered Product" LookUp field in the Orders table. Once you can explain that, I can provide you with some support.

For example, suppose we open a record from the Products table, we can apply the below for example to a Custom Command (created in the Main Form) to create a new record in the Orders table:

Patch(
    Orders,
    Defaults(Orders),
    {'Our LookUp Column Name': Self.Selected.Item}
); 
Notify("Saved",NotificationType.Success) //optional

However, I do not believe it is possible to create a record in a related table based on the selection of another record in a Main Grid or Sub Grid.