Create multiple items in Dataverse thru canvas app

[PetarHr]

Hi All, 

I'm trying to build a Canvas app that will allow multiple items to be created from 1 screen. The items should be created in Dataverse. 

The structure I have in Dataverse is 1:N setup between two entities: Order (1) : OrderItems (N).

One order can have multiple order items. The end users should be able to enter all the orders & linked order items to them. Afterwards, once they press a button, all the records should be created in Dataverse with the entered values. 

For that purpose I use 2 galleries with 2 collection data sources: GalleryOrders (source: colOrders) & GalleryOrderItems(source colOrderItems). 

I'm initializing the collections via:

ClearCollect(
    colOrders,
    {
        rowGUID: Text(GUID()),
        rowNumber: 1,
        orderObject: Defaults(Orders) // Defaults(OrderItems) for the colOrderItems init
    }
);

I use the rowGUID to link each order to it's order items and rowNumber is used just to provide a number in the UI that will help the users see the number of rows. 

What I see is when I toggle between the orders in the list, the orderItem values are lost. Values appear to be saved in GalleryOrderItems and the colOrderItems remains with empty rows in the background.

If I enter the values on the screen & patch dataverse, only the values that are currently visible in the UI will be persisted. Everything else will be created with blank rows. 

So, question is - how can I persist the values from the UI in the collections on the background in order to push them to Dataverse? 

For the Dataverse update I also use the following logic:

ForAll(
        GalleryOrders.AllItems,
        Patch(
            'Orders',
            Defaults('Orders'),
            {
                Name: rowGUID,
                'Order': LookUp(
                    Orders,
                    'Multiple Entry GUID Reference' = rowGUID
                ),
                Account: ThisRecord.inputAccount.Text,
                Quantity: Value(ThisRecord.inputQuantity.Text),
                Position: ThisRecord.inputPosition.Selected.Value,
                Location: LookUp(
                    Locations,
                    Name = inputLocation.SelectedText.Name
                )
            }
        )
    );

[v-jefferni]

Hi @PetarHr ,

For you scenario, the LookUp column should be located in the OrderItems table. So in the colOrderItems collection there need be a column reference the Orders table as well, so as to apply the 1:N relationship between current order item and a specific order. As the result, Items of Gallery2 will become a Filter, to get the items that link to an order that selected in Gallery1.

Filter(colOrderItems, Order.GUID = Gallery1.Selected.GUID)

To create orders and order items in the two tables, you need to use two ForAll/Patch functions, update items of the two Galleries to respective tables.

ForAll(
        GalleryOrders.AllItems,
        Patch(
            'Orders',
            Defaults('Orders'),
            {
                Name: rowGUID,
                Account: ThisRecord.inputAccount.Text,
                Quantity: Value(ThisRecord.inputQuantity.Text),
                Position: ThisRecord.inputPosition.Selected.Value,
                Location: LookUp(
                    Locations,
                    Name = inputLocation.SelectedText.Name
                )
            }
        )
);
ForAll(
        GalleryOrderItems.AllItems,
        Patch(
            'OrderItems',
            Defaults('OrderItems'),
            {
                Name: ThisRecord.NameLabel.Text,
                Order: ThisRecord.OrderComboBox.Selected 
//I assume you will use a Combobox in Gallery2 to select an order and build the connection with the Orders table
            }
        )
)

Actually there may be another situation. The OrderItems table will only have unique items and you will select items in an order and set purchase quantity for them? If so, it will be a multiple selections LookUp column in the Orders table. For this scenario, you will need only one collection, but the orderObject column will hold tables that includes several items as well as their quantities for this order. Gallery2 Items will become Gallery1.Selected.orderObject to display all the items for the selected order in Gallery1.

As you can see, the two scenarios are quite different and tables have totally different structures. So please double check and make sure you have built the two tables correctly.

Best regards,

[PetarHr]

Hi Jeffer Ni, 

Thanks for reaching out. 

I'm actually using the suggested two Patch functions to create the items in Dataverse and that works as expected - I create the count of items I've added into the Orders & OrderItems collections. 

The issue is that the values of the items are not retained once I switch between items. 

Here is an example (apologies for the bad quality screenshots, I needed to blank out some of the fields that are company specific to simplify the case):

1. I start by entering the order on the left afterwards I click on the arrow which executes the below function to create & link the order item rows. I then enter the order item details on the screen. 

Entry of first order details

Code of the arrow:

//Select the item from the gallery
Select(Parent);
//Set the rowSelector var to the rowNumber
Set(
    varRowGUIDSelection,
    ThisItem.rowGUID
);
//If there are no items for this row, create an empty one, otherwise - do not modify the collection
    If(
        CountRows(
            Filter(
                colOrderItems,
                rowGUID = varRowGUIDSelection
            )
        ) = 0,
        Collect(
            colOrderItems,
            {
                //Set the row number of the item to equal the row number of the order. It's used for subsequent filtering.
                rowGUID: ThisItem.rowGUID,
                rowNumber: ThisItem.rowNumber,
                orderItemsObject: Defaults(OrderItems)
            },
            {
                //Set the row number of the sides to equal the row number of the trade. It's used for subsequent filtering.
                rowGUID: ThisItem.rowGUID,
                rowNumber: ThisItem.rowNumber,
                orderItemsObject: Defaults(OrderItems)
            }
        )
    )

2. I then click on the "+" button to create a new order item & perform the same steps for entering data:

Second order data entry

The code that the "+" button executes is the following: 

Collect(
    colOrders,
    {
        rowNumber: Last(colOrders).rowNumber+1,
        rowGUID: Text(GUID()), 
        orderItemsObject: Defaults(OrderItems)
    }
);

At this point if I click on "Create Items" it will correctly create 2 Orders - one with 2 order items & one with 1 order item. However only the data that is visible in the screen will be populated in Dataverse - the order item details entered for Order 1 will be blank. 

Testing the app I see that the values are present in the UI, but not saved in the colOrderItems that is used in the second gallery on the right. If I toggle between the order items for order 1 & 2 will also cause the values to be discarded and the fields just appear blank. 

So, question is how to persists these values to be saved in the collections?

[v-jefferni]

Hi @PetarHr ,

You collections seems correct. But for Items of the Gallery on the right hand, there should be a Filter function the get order items corresponding to the selected order in the left Gallery. For Patch function, you will need to ForAll the collections instead of Gallery.AllItems so that all the order items in colOrderItems will be updated to data source.

Best regards,

[PetarHr]

Hi Jeffer Ni, 

Thanks for the suggestion. 

I'm using the Gallery items instead of the collection items because the latter are empty. I've raised a ticket to MS to assist in resolving this, so I'll keep you posted once we find a resolution. 

Cheers!