cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gregc72
Frequent Visitor

Save Collection as Single Text Field

I have a procurement form. Within that form is another form where people can add individual items to that procurement including description, qty, manufacturer, unit, unit cost and total cost (calculated). They fill in the form, click the add button and it adds an item to a collection.

What I would like to do is Patch that collection to a single multiline plain text field in a SharePoint List and then when needed for editing pull it out, edit, re-patch.

just patching the collection to that field doesn't work, tried using the Text function that doesn't work. Any way to do this?

1 ACCEPTED SOLUTION

Accepted Solutions

Thanks, didn't work as posted but got me there. It wouldn't let me reference the collection directly with the JSON function. I had to set it to a variable first then reference it.

Set(itemsJSON, JSON( col_Items ))

View solution in original post

6 REPLIES 6
StalinPonnusamy
Community Champion
Community Champion

Hi @Gregc72 

 

We can convert the collection to string using concatenate function with the key. Make sure to store as Key : Value pair, so that we can retrieve properly.

ClearCollect(
    colProcurement,
    {
        ID: 2,
        Title: "Test2",
        Qty: 15
    }
);
Set(
    FinalString,
    ForAll(
        colProcurement,
        Concatenate(            
            "ID:", Text(ThisRecord.ID),
            ",",
            "Title:", Text(ThisRecord.Title), ",",
            "Qty:", Text(ThisRecord.Qty)
        )
    )
)

 

StalinPonnusamy_0-1632426629103.png

 

BCLS776
Super User
Super User

You can use Concat() to create a large string from a collection or other data source, see example code:

Set(ExportCSV,""); // Initiate and clear the output string

Set(ExportCSV,
    Concat(colOutput,
        Name&","&
        Date&","&
        Income&","&
        Hours&","&
        Pieces&","&
        Amount&","&
        Project&","&
        Comment&Char(10)
    )
);

This one sets up a CSV for import into another package. It is then possible to use Patch() to save this string into a Sharepoint list.

 

For later editing, will you need to parse this info? If so, I recommend you don't mash it all together into a string until you really need to do it. Is there a reason you don't want to store individual values in unique columns?

 

Also, be aware a Sharepoint 'multiple lines of text' column is limited to 63,999 characters per field. That may not be enough for your purposes.

 

Hope that helps,

Bryan

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

I tried cutting and pasting exactly as you posted. 

I added the FinalString Variable as the default in the text box but it's telling me it expects a text value.

Feel like I'm missing something simple here. What is the formula you are putting in the text box to get the text?

I also tried

FinalString.Value
Text(FinalString)

Gregc72_0-1632486047374.png

 

Gregc72
Frequent Visitor

A Bit more info, I have a main form, then and itemized form that writes to a collection. The secondary form I use to write to the collection looks like:

Gregc72_1-1632486430700.png

 

The Items + button OnSelect writes to the col_Items:

Collect(
    col_Items,
    {
        intendedcustodian: txtIntendedCustodian.Text,
        manufacturer: txtManufacturer.Text,
        itemdesc: txtDesc.Text,
        partnumber: txtPN.Text,
        quantity: txtQty,
        unitissue: txtUnitIssue.Text,
        costea:txtUnitCost,
        costext:txtExtCost
    }

)

The Submit Button:

Patch(
    'Procurement-Requests',
    Defaults('Procurement-Requests'),
    {
        Description: val_Title.Text,
    Requestor:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                       Claims:Concatenate("i:0#.f|membership|",val_Requestor.Selected.Email),
                   Department:"",
                  DisplayName:"",
                        Email:val_Requestor.Selected.Email,
                     JobTitle:"",
                      Picture:""
             },
             RQPhone:val_RQPhone.Text,
             RQEmail:val_RQEmail.Text,
    
    SBPhone:val_SBPhone.Text,
    SBEmail:val_SBEmail.Text,
    pri: val_pri.Selected.Value,
    CON: val_CON.Text,
    'CON Exp': val_conExp.SelectedDate,
    'Priority Justification': 'val_Priority Justification'.Text,
    purchJust: val_purchJust.Text,
    'Order Total': Value(val_ordTotal.Text),
    Method: {Value: val_method.Selected.Value},
    Project: val_proj.Text,
    typePurch: val_typePurch.Selected.Value,
    NAMP: val_Namp.Value,
    'Ready Issue': val_readyIss.Value,
    shipTo: val_shipTo.Selected.Title,
    Vendor: {Value: val_vendor.Selected.Value,Id: val_vendor.Selected.Id},
    'Appr Choice':{Value:val_appr.Selected.Value,Id:val_appr.Selected.Id}
    });

Just want to add one field at the end of the Final Patch that achieves something like:

'Items': col_Items
StalinPonnusamy
Community Champion
Community Champion

Hi @Gregc72 

 

Got it. I recommend converting to JSON and reuse it like

'Items': JSON( col_Items )

 

StalinPonnusamy_0-1632487455759.png

 

For Testing, Set Text property of Label to col_ItemsJSON

Thanks, didn't work as posted but got me there. It wouldn't let me reference the collection directly with the JSON function. I had to set it to a variable first then reference it.

Set(itemsJSON, JSON( col_Items ))

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (3,158)