Patching collection to SharePoint list and add a collection upload ID

[Ebbe1988]

Hello fellow developers 🙂

I have a Power App with a Collection called offerCol with the following columns:

LicensName
TermDuration
BillingPlan
SkuId
Price
Quantity
LineDiscountRate
Total

The collection is connected to a power apps gallery.
I perform different modifications to the collection while playing the app. When all my modifications are completed, I want to be able to bulk save all records in the collection, into a SharePoint List called OfferList. For this purpose, I have created a button outside the gallery, that takes all records in offerCol and patch it as new records into the SharePoint list. The code in the buttons OnSelect property looks like this.

ForAll(

    offerCol,

    Patch(

        OfferList,

        Defaults(‘OfferList '),

        {

            LicenseName: LicenseName,

            TermDuration: TermDuration,

            BillingPlan: BillingPlan,

            SkuId: SkuId,

            Price: Price,

            Quantity: Quantity,

            LineDiscountRate: LineDiscountRate,

            Total: Total

        }

    )

)
The SharePoint list (OfferList) have the following columns:
LicensName
TermDuration
BillingPlan
SkuId
Price
Quantity
LineDiscountRate
Total
OfferId

I now want you to modify the code above so that all records in offerCol gets the exact same unique ID called OfferId.

For every record in offerCol to be patched into the SharePoint list, this ID should be applied and loaded into the “OfferId” column in the list. The ID should be unique in the sense that it should only exist once for a group of records in the SharePoint list (OfferList).
The idea here is, to be able to detect a group of multiple rows in the SharePoint list, by their single collective collection upload ID (in this case called (offerId).
The idea looks like this.

Since I’m stuck and have had a hard time finding a solution for this, I would really appreciate if a gentle person would help me modify the code.

[poweractivate]

Certainly! I understand the requirement, and I'm here to help you modify the formula to achieve the desired functionality.

First, you'll need to generate a unique OfferId that can be applied to all items in the offerCol collection. One way to do this is to use the Now() function to create a timestamp, which can be used as a unique ID. You can then use ClearCollect to create a temporary collection with the same data from offerCol but with the additional OfferId column.

Here's the updated formula:

1. Create the OfferId using the current timestamp.
2. Use ClearCollect to create a temporary collection with the OfferId.
3. Patch the temporary collection to the SharePoint list.

// Generate unique OfferId based on the current timestamp
Set(gloOfferId, Text(Now(), "[$-en-US]yyyymmddhhmmss"))

// Create a temporary collection with the same data from offerCol and the additional OfferId
ClearCollect(
    colTemp,
    AddColumns(
        offerCol,
        "OfferId", gloOfferId
    )
)

// Go through the temporary collection and Patch it to the SharePoint list
ForAll(
    colTemp,
    Patch(
        OfferList,
        Defaults(OfferList),
        {
            LicenseName: LicenseName,
            TermDuration: TermDuration,
            BillingPlan: BillingPlan,
            SkuId: SkuId,
            Price: Price,
            Quantity: Quantity,
            LineDiscountRate: LineDiscountRate,
            Total: Total,
            OfferId: OfferId // Using the unique OfferId for the collection upload
        }
    )
)

However, I recommend Patch outside ForAll - to do so, try below:

1. Generate the unique OfferId as before.
2. Use ClearCollect to create a temporary collection (colTemp) with the OfferId.
3. Create another temporary collection (colChanges) using ForAll to go through the colTemp and prepare the records you want to patch.
4. Use Patch outside of ForAll to apply the changes to the SharePoint list.

// Generate unique OfferId based on the current timestamp
Set(gloOfferId, Text(Now(), "[$-en-US]yyyymmddhhmmss"))

// Create a temporary collection with the same data from offerCol and the additional OfferId
ClearCollect(
    colTemp,
    AddColumns(
        offerCol,
        "OfferId", gloOfferId
    )
)

// Go through the temporary collection and prepare the records for patching
ClearCollect(
    colChanges,
    ForAll(
        colTemp,
        {
            LicenseName: LicenseName,
            TermDuration: TermDuration,
            BillingPlan: BillingPlan,
            SkuId: SkuId,
            Price: Price,
            Quantity: Quantity,
            LineDiscountRate: LineDiscountRate,
            Total: Total,
            OfferId: OfferId // Using the unique OfferId for the collection upload
        }
    )
)

// Patch the prepared records to the SharePoint list
Patch(
    OfferList,
    Defaults(OfferList),
    colChanges
)

See if it helps @Ebbe1988 

[Prabhakar_S · Accepted solution]

Hi @Ebbe1988 ,

Please try this formula,

ClearCollect(
offerColWithOfferId,
AddColumns(
offerCol,
"OfferId",
Text(Today(), "[$-en-US]yyyymmdd") & Text(Now(), "hhmmss")
)
);

ForAll(
offerColWithOfferId,
Patch(
OfferList,
Defaults(OfferList),
{
LicenseName: LicenseName,
TermDuration: TermDuration,
BillingPlan: BillingPlan,
SkuId: SkuId,
Price: Price,
Quantity: Quantity,
LineDiscountRate: LineDiscountRate,
Total: Total,
OfferId: OfferId
}
)
);

Thanks!!!

[Ebbe1988]

Hi Prabhakar_S

Thank you for your very quick and helpful response. Now my problem is solved and it creates a unique upload ID (OfferId) 😄