Issues Splitting Strings

[SLoudon]

I'm creating an app to record times that people spend on a number of projects across the month.  I have a Sharepoint Table set up to capture the user's email address, the month they are reporting then a series of columns holding a comma separated string of numbers representing the number of hours each day they spend on each project type.

Email@Address.com   |   May  |   0,1,2,3,4,5... (for up to 31 values) | (and theres 13 fields of CSVs)

The reason to hold it like this is so I can pull it back into the app and display each day, so if a user wants to record on a daily or weekly basis and just come back to update the sheet at their own pace they can do so.  I'll then also be able to take all the figures and add them to get totals of recorded time.

I can pull the information, including the string of CSV times into the app without an issue using:

ClearCollect(TimeInfo, Filter(TimeSheetv2, StartsWith(Title, User().Email)));

So now I have the information in the collection and I want to tear apart the CSV to store in a new set of collections so I can display on screen or show a total for that worktype, but when I try to split the CSV string say for instance in Work Type Absence:

ClearCollect(ABSC, ForAll(TimeInfo, Split(TimeInfo.WTABS, ","))

I'm told that the function has invalid arguments and it focuses on TimeInfo.WTABS.

The field in Sharepoint is set as a single line of text.  I've tried Split(Text(TimeInfo.WTABS), ",") and Split(Value( with no luck.

Any help would be appreciated.

[Mr-Dang-MSFT · Accepted solution]

---

@SLoudon wrote:

ClearCollect(ABSC, ForAll(TimeInfo, Split(TimeInfo.WTABS, ","))

I'm told that the function has invalid arguments and it focuses on TimeInfo.WTABS.

The field in Sharepoint is set as a single line of text.  I've tried Split(Text(TimeInfo.WTABS), ",") and Split(Value( with no luck.

---

Your top formula means, "Create a collection that includes [does not fit here]." ForAll can perform actions, but you can't reference it as an output. It doesn't have a type: it's not a table, record, string, date, etc. It works with tables and records, but it is not one itself.

Instead, you'll want to invert your thought process:

"For every record in the TimeInfo table,

• collect a record to the ABSC table that includes the columns:
  • For the first column:
    • split the string in the WTABS column every time there is a comma
    • Get 1 row from the top. Of that, get the last one in the subset
    • Set the column to the text that had resulted by splitting up WTABS
  • For the second column:
    • Repeat the same, but this time get 2 rows from the top of the split. Get the last one.
    • Set the column to the text that resulted by splitting up the same WTABS
    • The Last(FirstN()) pattern is like getting the Nth record of a table. In this case, it's helpful for the Split function which is a table of split up strings whose placement corresponds to very specific fields in the collection."

Clear(ABSC);

ForAll(TimeInfo, 
    Collect(ABSC, 
        {
            column1: Last(FirstN(Split(WTABS, ","),1)).Result,
            column2: Last(FirstN(Split(WTABS, ","),2)).Result,
            column3: Last(FirstN(Split(WTABS, ","),3)).Result,
            columnN: Last(FirstN(Split(WTABS, ","),N)).Result
        }
    )
)

Notice I did not use ClearCollect. If you attempt to use ClearCollect in a ForAll statement, you'd end up clearing the collection each time the set of actions run. I complete the clearing independently at the start, and do the collecting repeatedly thereafter.

Let me know which part you might need clarification on.

+ @darogael who has created content on the Split() function

[v-xida-msft]

HI @SLoudon ,

Based on the formula that you mentioned, I think there is something wrong with it. The first argument of the Split function is required to provide a Text value, but the result the TimeInfo.WTABS formula returns is a Table value.

I have made a test on my side, please consider take a try to modify your formula as below:

ClearCollect(
             ABSC, 
             ForAll(
                    TimeInfo, 
                    Split(WTABS, ",")   /* <-- Modify formula here. Type WTABS rather than TimeInfo.WTABS */
             )
)

Please take a try with above solution, then check if the issue is solved.

More details about Split function, please check the following article:

Split function

Best regards,

[SLoudon]

Both of these solutions work really well thank you to you both.

Reverse engineering the table going item by item is what I need to create kind of reference table I want, but I need to do 31 (32 with a month name value) lines to separate out each day's entry.  

Is there a more efficient way to write a loop like for i = 1 to 31 or do I need to specifiy each line individually?