SharePoint List changed data type from record to table

[Nusserdt]

I create a PowerApp based on a SharePoint list. After one month I will check something an recognize that the App doesn't work anymore. And I am pretty sure, that's cause of a datatype change inside the SharePoint list.

Lets compare two columns I use: (list.JPG)

• Team = Choice
• Projektstatus = Choice

But PowerApps these column have two different data types:

• Team = Table
• Projektstatus = Record

I am pretty sure Team was a record to before. Cause of this change my function do not works anymore:

Collect(
    _filterProjektTeam;
    Sort(
        Filter(
            Distinct(
                ForAll(
                    List.Team;
                   Team
                );
                Value
            );
            !IsBlank(Result)
        );
        Result
    )
);;

PowerApps can't process the variable List.Team. The same function with Projektstatus works totally fine likes before, because the data type here is a record not a table.

I try to fix that but I failed. What I really wondering about was that the following code give back a value: (list team.JPG)

List.Team.Team.Team.Team.Team.Team.Team.Team.Team.Team.Team.Team.Team;  ->  returns Datatyp:Table

What is here broken?

[Nusserdt]

Sorry again @WarrenBelz, I had a break.

The name of the list is Projects & Tasks. So in PowerApps I have to write: (no good syntax, I know, but it was never a problem before)

ClearCollect(
    List;
    'Project & Tasks'
);;

The result of ClearCollect(colList;List);; is in the attached screenshot. (colList.JPG) Field Team is a Table in colList.

[WarrenBelz]

Hi @Nusserdt ,

Yes, I suspected that would be the case.

Can you please do this again and then from the top menu View > Collections > colList >Team - you will see a table symbol. Click on it to see what is inside. If it is another table, keep clicking down until you get a value list then take a screenshot and send this.

[v-xida-msft]

Hi @Nusserdt ,

Have you enabled "Allow Multiple Selections" for the Team field in your SP List?

Based on the issue that you mentioned, I think you have enabled "Allow Multiple Selections" for the Team field in your SP List. As an fixed solution (direct solution), you could consider turn off the "Allow Multiple Selections" option for the Team field in your SP List:

then re-load your canvas app, then check if the issue is solved.

In addition, as another solution, you could consider modify your Collect formula as below:

Clear(TempCollection);;
Clear(_filterProjektTeam);;
ForAll(
       'Your SP List'.Team;
       ForAll(
              Team.Value;
              Collect(TempCollection; Value)
       )
);;
Collect(
    _filterProjektTeam;
    Sort(
        Filter(
            Distinct(
                     TempCollection;     // Put above generated TempCollection here
                     Value
            );
            !IsBlank(Result)
        );
        Result
    )
);;

Please take a try with above formula, then you could use the _filterProjektTeam collection in your canvas app.

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

Best regards,

[WarrenBelz]

@v-xida-msft ,

Thank you for the additional information on the 14th post of this thread.

@Nusserdt I will have an idea after you answer my question where the issue may lie.

[Nusserdt]

@v-xida-msft thank you for your reply. I check out table settings on sharepoint and it seems we use an older version. Take a look at my screenshot, there is no such a option: "Allow Multiple Selections"

Second: your formula using a tempCollection solve the Error for the first time. As result the SharePoint List could load as Table again and there I could see the following:

The Table it self is not able anymore to display the Team column. It looks like a nested table.

How could I solve this?

[Nusserdt]

@WarrenBelz I do what you suggest and I think your intention is correct. A nested Table:

And the Deatils:

But, in this overview you could find more Table Symbols, not only for the Team column. But the Team column is the only one which could'nt displayed correctly.

For example:

ThisItem.Projektstatus.Value has also a Table Symbol, in SharePoint it is also defined as choice with 4 options -> works

ThisItem.Team.Value choice with 5 options (the Details looks exactly like from Team) -> works not.

What is in your mind?

[WarrenBelz]

Hi @Nusserdt ,

I suspect there is something wrong with your Team field in SharePoint as Team.Value should retrieve the information as it is a Choice column (I suggested this in an early post). In your collection (colList.Team.Value), you are getting Na where it should show either a value chosen, or if the field is empty, you would not have the "table" icon displaying.

Try making another lookup field in SharePoint with the same choices and see if the .Value works on this as per my earlier post.

[v-xida-msft · Accepted solution]

Hi @Nusserdt ,

Firstly, based on the screenshot that you provided, I think you have already enabled "Allow Multiple Selections" option for the Team field already:

If you want to store all Team options from all records in your SP List into a collection, I think the formula I provided above could achieve your needs:

Clear(TempCollection);;
Clear(_filterProjektTeam);;
ForAll(
       'Your SP List'.Team;
       ForAll(
              Team.Value;
              Collect(TempCollection; Value)
       )
);;
Collect(
    _filterProjektTeam;
    Sort(
        Filter(
            Distinct(
                     TempCollection;     // Put above generated TempCollection here
                     Value
            );
            !IsBlank(Result)
        );
        Result
    )
);;

For your second question, does the Data Table connect to your SP List directly? Do you also want to display the Teams column value within the Data Table?

Because you have enabled "Allow Multiple selections" for the Team field in your SP List, so the Team field value of each record would be a Table value rather than a record value. If you want to display the Teams field value within your Data Table, please take a try with the following workaround:

On your side, please set the Text property of the Teams column in your Data Table control to following:

Concat(ThisItem.Team, Value & ";")

then all selected Team options for each record would be displayed as concatenated string value within the Data Table (separated with ";").

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

Best regards, 

[WarrenBelz]

Hi @Nusserdt ,

Can you please tag whoever you want to help you here - three-way conversations just do not work for anyone.

[WarrenBelz]

Hi @Nusserdt ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.