Save Collection as Single Text Field

[Gregc72]

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?

[StalinPonnusamy]

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

[BCLS776]

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

[Gregc72]

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]

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:

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]

Hi @Gregc72 

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

'Items': JSON( col_Items )

For Testing, Set Text property of Label to col_ItemsJSON

[Gregc72 · Accepted solution]

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