cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Farquad88
Helper I
Helper I

Creating an array with sharepoint data

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!

project_details (current state)
Project A | due date | status
Project B | due date | status
etc.

Project_Risk
Project A| Risk 1 | additional details
Project A| Risk 2 | additional details
Project A| Risk 3 | additional details
Project B| Risk 4| additional details

Project_details(future state)

Project A | due date | status | Risk 1, Risk 2, Risk 3
Project B | due date | status | Risk 4
etc.

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User III
Super User III

@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. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

5 REPLIES 5
RandyHayes
Super User III
Super User III

@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. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

@RandyHayes 

 

Thank you! Where would this code be entered?

RandyHayes
Super User III
Super User III

@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?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

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
Super User III
Super User III

@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 & ", "
           }
        )
    )
)

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (47,748)