Creating an array with sharepoint data

(0) Share

Report

Posted on by Farquad88

I'll explain my data set up first:

I have two lists, the first is project_details, the Second is Project_Risk

In Project_details, each record is an individual project, with various details

In Project_Risk, each record is an individual risk - some projects have multiple risks associated with them, some have none. Each record in this list has additional details that are not needed for the solution I am looking for, I will only need to pull one field (risk_name)

The data is all set up in sharepoint.

My question is, is there a way to extract the risk_name based on the common project name, into a single field in the project_details? Even further, it would be great if it could be formatted into bulleted, or separated by a comma.

For example, Project A has 3 risks in the project_risks list, I would like to pull the risk_name of each of these into a single field in project_details.

ex- of what the data looks like today and how i would like it to look!

Categories:

Building Power Apps

All responses (5)

Answers (1)

Sort by

Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@Farquad88

Sure, consider the following:

AddColumns( ProjectDetails As _details, "risks", Concat( Filter(Proejct_Risk, ProjectName = _details.ProjectName ), risk_name & ", " ) )

This will give you a table result with a column called risks that will have the values as you indicated in your sample.

I hope this is helpful for you.
Farquad88 61 on at

Like
a
(0)

Report
Copy link

Link copied!

@RandyHayes

Thank you! Where would this code be entered?
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Farquad88

Depends on what you are trying to do. The Formula could be entered on any control with an Items property (Gallery, Table, etc.)

Where are you trying to go with it?
Farquad88 61 on at

Like
a
(0)

Report
Copy link

Link copied!

Got it.

I think I would like to run it manually about one time/month. Will it add a new column each time it is run?
If it does not add a new column but instead updates the existing column , it could be added to the onselect of a button that is used when a new risk is added or an existing risk is updated.
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@Farquad88

I believe you might be misunderstanding...this is NOT going to add a column to your DataSource.

The function will RETURN a table that has an added column.

PowerApps cannot add or remove columns to a list. It can only (using formulas such as ShowColumns, AddColumns, DropColumns, etc.) shape the resultant table in your app the way you want.

If you are planning to try to persist this back to your datasource, you will need a column in the DataSource to contain the results. Then, if, as you say, you are going to manually perform this monthly, your App would need to persist this back to your datasource.

A pure example would be - Say you have a column in your Project_details called "Risks". This formula would update them all.

Collect(Project_details, ForAll( Project_details As _details, Patch(_details, {Risks: Concat( Filter(Proeject_Risk, ProjectName = _details.ProjectName ), risk_name & ", " } ) ) )