Need help creating collection out of Choice values from Sharepoint List columns

[DJanssen]

Hello,

I want to do the following:

I have 2 Sharepoint Choice columns that each contain 3 text choices. During my Power App OnStart I want a collection being built with 2 columns with these values. So:

Code	Description
WTD	Transmission....
NWA	Water.....
WIN	Water...

I know this can be achieved by doing this the manual way:

ClearCollect(
    colExtendedDescriptions,
    { Code: "WTD", Description: "Transmission..." },
    { Code: "NWA", Description: "Water.." },
    { Code: "WIN", Description: "Water..." },
);

But since I have a lot of choices (80+) and all info already preset in the Sharepoint columns I prefer not do it this way if possible.
I have tried a lot of approaches, but the collection is not being built how I want. Is this even possible?

Thank you so much for helping!

[WiZey]

Hello @DJanssen ,

You can use "Choices()" function to quickly get a table of all choices from your choice-type column.

https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-choices

[trjackson77 · Accepted solution]

Hi @DJanssen ,

For your requirement to build a collection in PowerApps with values from SharePoint Choice columns, you can use the Choices() function in PowerApps. This function simplifies the process by directly accessing the choice options from SharePoint. Here's how you can do it:

1. Use the Choices() Function: To retrieve the available choices from your SharePoint list, use the Choices() function. For example:

   Choices(YourSharePointList.YourChoiceColumn)

   Replace 'YourSharePointList' and 'YourChoiceColumn' with your actual list and column names

2. Split the Data: Since your choices are in the format '{Description} - {Code}', you can use the 'Split' function in PowerApps to separate these values:

   ClearCollect( colExtendedDescriptions, ForAll( Choices(YourSharePointList.YourChoiceColumn), { Code: Last(Split(Value, " - ")).Result, Description: First(Split(Value, " - ")).Result } ) );

3. Create the Collection: This formula will create a collection colExtendedDescriptions with 'Code' and 'Description' as separate columns.

Using the Choices() function is efficient and avoids the complexity of setting up a flow in Power Automate, especially for a large number of choices.

Cheers,

Tim

[DJanssen]

@WiZey  Thank you for your quick response. I think I could be getting close but I'm only working for 3 months with the Power Platform so far. I have tried this (my real use case is with multiple columns but for my question I only used 2):

ClearCollect(
    colExtendedDescriptions,
    {
        TeamCode: Choices(TESTDEV.TeamCode)
    },
    {
        TeamCodeDescription: Choices(TESTDEV.TeamCodeDescription)
    },
    {
        DisciplineCode: Choices(TESTDEV.DisciplineCode)
    },
    {
        DisciplineCodeDescription: Choices(TESTDEV.DisciplineCodeDescription)
    }
)

This gave me the following collection:

When clicking on the table icon I see:  

How can I transfer these text values to the TeamCode column?

[DJanssen]

@trjackson77 Hey thanks for your reply. Didn't see it before, but I will try your solution!

[DJanssen]

@trjackson77 Hey Tim, your solution worked for my 2 columns provided in my question. Had to revise it a bit since Result was not being recognized:

ClearCollect( colExtendedDescriptions, 
ForAll( Choices(TESTDEV.TeamCodeDescription), 
{ TeamCode: Last(Split(Value, " - ")).Value, TeamCodeDescription: Value } ) );

Fact is that I actually need to create 8 columns in total, but I only used the 2 columns in my question for demo purposes. Sorry should be more clear about this in the future. To me it seems that your ForAll() only works to create 2 columns revolving the Team Code info right? But I also have my Discipline Code, Document Code and Document Type Code choice columns, each also with its own description in choice value.

I could go for 4 different collections perhaps, but this would mean my App would have around 8 collections OnStart. Is this bad for performance? The collections are not that big datawise. Maybe the biggest will be 300 entries max 

[trjackson77]

Hi @DJanssen,

To handle the additional columns (Discipline Code, Document Code, and Document Type Code) alongside the Team Code, you can integrate the lookup directly within the original ForAll statement. This will allow you to fetch and display all necessary information.  I'm guessing this info will be held elsewhere other than the original Choice table, so you'll have to do a Lookup.

Here's the revised Power Apps code:

        ClearCollect(
            colExtendedData,
            ForAll(
                Choices(TESTDEV.TeamCodeDescription),
                {
                    TeamCode: Last(Split(Value, " - ")).Value,
                    TeamCodeDescription: Value,
                    // Integrated lookups for additional information
                    DisciplineCodeInfo: LookUp(AnotherDataSource, Code = Last(Split(Value, " - ")).Value).Description,
                    DocumentCodeInfo: LookUp(AnotherDataSource, Code = Last(Split(Value, " - ")).Value).Description,
                    DocumentTypeInfo: LookUp(AnotherDataSource, Code = Last(Split(Value, " - ")).Value).Description
                }
            )
        );
        

This should point you in the right direction, but without a full description of what you are looking for it's hard to be more specific with the code.

Cheers,

Tim.

[DJanssen]

Hey @trjackson77 
Thank you so much for taking time to point me in the right direction. Luckily for me the DisciplineCode, DocumentTypeCode etc is all in the same DataSource. This is the actual format that my Sharepoint List has:

TeamCode	TeamCodeDescription	DisciplineCode	DisciplineCodeDescription	DocumentCode	DocumentCodeDescription	DocumentCodeAttachment	DocumentCodeAttachment
(12 choices)	(12 choices)	(50 choices)	(50 choices)	(73 choices)	(73 choices)	(10 choices)	(10 choices)

A bit more background why I'm building this collection: My Power App has several dropdowns (containing the Choices values of the TeamCode for example) that the users can choose from when creating a document number. But they were complaining that they need more descriptive names, so that's why I added the XCodeDescription in the same list and build a collection to display these description values but to actually use the TeamCode value for building a document number in another field.

Would this be enough information for you? 

[trjackson77 · Accepted solution]

Ah, I think I see.  These are not supplementary to the original choices column, they are in fact discrete choices in themselves and need to be selected on different dropdowns.

That being the case you can simply modify your code to reflect the other columns:

ClearCollect( colExtendedDescriptions, 
ForAll( Choices(TESTDEV.TeamCodeDescription), 
{ TeamCode: Last(Split(Value, " - ")).Value, TeamCodeDescription: Value } ) );

In the case of DisciplineCode for example 

TESTDEV.TeamCodeDescription Would change to TESTDEV.DisciplineCodeDescription

and in the record be sure to name your New fields as such ( {DisciplineCode: & DisciplineCodeDescription:

It's a bit more work but it will get you there.

Tim.