Merge two sharpoint list into powerapp data table

[Anonymous]

Hello,

I have two SharePoint lists:

The First list is:  ApplicationRegistration

ApplicationName	ApplicationAccessLevel	Functional Manager
Application 1	Admin	John
Application2	Readonly	Mira
Application3	Admin	John
Application 4	Admin	Simon

The Second List is: StaffApplicationRegistration

StaffName	ApplicationName
Staff 1	Application 1
Staff 2	Application 1
Staff 3	Application 2
Staff 4	Application 3
Staff 5	Application 4

In StaffApplicationRegistration, the ApplicationName is based on the Application Name from the ApplicationRegistration table.

In the power app I have dropdown control of Operational Manager if I select One operational manager (John) I will get the output like below:

StaffName	ApplicationName	ApplicationAccessLevel
Staff 1	Application 1	Admin
Staff 2	Application 1	Admin
Staff 4	Application 3	Admin

That means If I select Operational manager I will get all staff names who use the application which is under that Operational manager.

I want this data into the power app data table. Is It Possible to get the data like this? Can I merge two SharePoint lists into a Datatable?

@RandyHayes @WarrenBelz @Shanescows 

[WarrenBelz]

Hi @Anonymous ,

The manager would be better in the other list, however this may work for you.

Clear(colTemp);
With(
   {wReg:ApplicationRegistration},
   ForAll(
      StaffApplicationRegistration As aStaff,
      Collect(
         colTemp,
         AddColumns(
            aStaff,
           "Access",
           Lookup(
              wReg,
              ApplicationName=aStaff.ApplicationName
           ).ApplicationAccessLevel,
           "Manager"
           Lookup(
              wReg,
              ApplicationName=aStaff.ApplicationName
            ).'Functional Manager'
         )
      )
   )
);
ClearCollect(
   colMyCol,
   Filter(
      colTemp,
      Manager = YourDropDown.Selected.xxxx
   )
)

Note xxxx will depend on the Items of the drop-down.

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.

[Anonymous]

Hello @WarrenBelz ,

Thank you for your reply.

I can't use Manager filed in others SharePoint list because this is my requirement.

In which properties I have to write this formula? If is it on select Button properties? I am not using Any button in this form. It is just a Dropdown. If I select Functional manager(you can say as an Operational Manager) from the dropdown the data will display on Datatable.

[WarrenBelz]

@Anonymous ,

You have to initiate the collection from somewhere and it will form the Items of your gallery. Probably the OnChange of your manager drop-down would do the job.

Please note you have caused yourself a complex issue with your data structure - you might have a think about de-normalizing it and the solution would be much shorter. Also note the code was free-typed as syntax guidance, so you may have to clean up commas/brackets etc.

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.

[Anonymous]

@WarrenBelz 

It work. But in Data Table , It display all staff members, not only that who use application.

My formula is as like yours but I make some changes.

My Formula:

Clear(colTemp);

With({wReg:ApplicationAccess},

ForAll(StaffApplicationRegistration As astaff,

Collect(colTemp,

AddColumns(StaffApplicationRegistration.StaffName,"Application_Name",LookUp(wReg,ApplicationName=aStaff.ApplicationName.Value).Application Name,"Manager",Lookup(wReg,ApplicationName=aStaff.ApplicationName.Value).'operationalmanagers '))));ClearCollect(colMyCol,Filter(colTemp, Manager=DataCardValue1.Selected.Result))

I make Changes: 

 I tried this

 StaffApplicationRegistration As aStaff

but I got error there so I used Addcolumn(StaffApplicationRegistration.StaffName,....)

Damini

[WarrenBelz]

@Anonymous ,

Firstly, your code has the following that needs addressing (line references in the code below)

1. You need colMyCol as the gallery Items
2. You have ApplicationAccess instead of the list name ApplicationRegistration you posted on line 2
3. You have astaff instead of aStaff on Line 5
4. You have to add columns to a list not a field, so you cannot have StaffApplicationRegistration.StaffName on line 9. You can refer to the List as aStaff as it has been defined as this above.
5. You had .Application Name instead of .ApplicationName on line 14, but that is not the issue as you have changed Access (which is what you wanted) to ApplicationName and put in a field you already have (no need to add) - you need to add the ApplicationAccessLevel for your view in the output you posted.

Clear(colTemp);
With(
   {wReg:ApplicationRegistration},
   ForAll(
      StaffApplicationRegistration As aStaff,
      Collect(
         colTemp,
         AddColumns(
            aStaff,
            "Access",
            LookUp(
               wReg,
               ApplicationName=aStaff.ApplicationName.Value
            ).ApplicationAccessLevel,
            "Manager",
            Lookup(
               wReg,
               ApplicationName=aStaff.ApplicationName.Value
            ).'operationalmanagers '
         )
      )
   )
);
ClearCollect(
   colMyCol,
   Filter(
      colTemp, 
      Manager=DataCardValue1.Selected.Result
   )
)

Please take a moment to try to understand the logic of this code.

1. The With() statement is only there to avoid the Delegation error you will get on the AddColumns lookup. Note the number of list items in ApplicationRegistration needs to be less than your Delegation limit.
2. The As statement is to avoid the ambiguity that ApplicationName=ApplicationName.Value would cause. I assume that this is a Lookup field in StaffApplicationRegistration? If so you are causing yourself unwanted complexity. You might take a moment to watch this video from Shane Young @Shanescows on recommended field types to use. Also if one of these fields were named differently in one of the lists, you would not need the As statement.
3. After that you are making a collection (colTemp) using all the records from ApplicationRegistration and adding the matching (on ApplicationName) for the Manager and Access level.
4. The final collection (colMyCol - call it whatever you want) then filters all of this on the manager name.

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.

[RandyHayes · Accepted solution]

@Anonymous 

Sorry to chime in late.  As I understood your requirement, you have the two lists as described.  You have one dropdown to choose the Manager (as it appears in the Application Registration table).  You want the results to appear as you have shown in a DataTable.

If the above is correct, then I can offer another potential solution to try.

1) Set the Items property of your Dropdown to the following:

RenameColumns(Distinct(ApplicationRegistration, 'Functional Manager'), "Result", "FunctionalManager")

2) Set the Items property of the DataTable to the following Formula:

With({filteredList: Filter(ApplicationRegistration, 'Functional Manager'='Operational Manager'.Selected.FunctionalManager)},
    ForAll(    
        Filter(StaffApplicationRegistration, ApplicationName in filteredList.ApplicationName) As staff, 
        {StaffName:staff.StaffName, ApplicationName:staff.ApplicationName, ApplicationAccessLevel:LookUp(filteredList, ApplicationName=staff.ApplicationName && 'Functional Manager'='Operational Manager'.Selected.FunctionalManager, ApplicationAccessLevel) }
    )
)

That should give you the results you are looking for. (Note: I changed the access level on John for Application 3 to ReadOnly in the data sample, just to emphasize the carry through of the access level to the list)

 

I hope this is helpful for you and what you can use.

[Anonymous]

Thank you for your reply.

In the second formula why you use Operational Manager?

[Anonymous]

@RandyHayes 

Thank you so much for help. I tried this from last week.

[RandyHayes]

@Anonymous 

So where are you on this?  It seemed like you had an additional question, but I am not sure what it might be related to.  Please clarify.