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

Append records to a variable/collection/table

There exists a SharePoint list with Employee Observations.

 

Employees can only see the Observations that they created, but Managers (who are also employees that have their own Observations) can also see their underlings Observations. (The employee-to-manager relationship is handled separately in an Employee list, and manifests as variable gvarWorkerIsManager.)

 

To handle having employees only see their own Observations, a variable/collection is created in App.OnStart, using:

       Set(gtblObsSearchResults, Filter('Observations', Lower('Created By'.Email)=Lower(gvarUser.Email)));

(gvarUser is populated by User(), 'Created By'.Email is native to SharePoint)

 

Next, to handle showing the underling employee Observations to the Manager, this was attempted: 

 

If(gvarWorkerIsManager,
  ForAll(gtblMyEmps,
    Collect(gtblObsSearchResults, LookUp('Observations', Lower('Created By'.Email) = Lower(WorkEmail)))
  )
); 

 

gtblMyEmps pulls underling items from the Employee list, where their manager is set to the CurrentUser. 

 

QUESTIONS:

1. Can a "Set()" be followed by a "Collect()", in order to append more items to a variable/collection?

2. Can a Lookup() or Filter() be used within a ForAll() to find more items to append to the variable/collection?

3. What other method(s) should have been used?

Bonus. What question(s) were not asked that should have been?

 

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
GDSI
Helper I
Helper I

I got this to work: 

 

If(gvarWorkerIsManager,
  ForAll(gtblMyEmps,
    Collect(gcolObsSearchResults,
    Filter('F-SOC-HaS-BBS-Observation', Lower('Created By'.Email) = Lower(WorkEmail))
    )
  )
);

 

Out of curiosity, why are variables that store tables bad? I treat them like a Snapshot, which is an old Access term, which holds a point-in-time snapshot of the data, and doesn't need to update/change during processing.

View solution in original post

4 REPLIES 4
WarrenBelz
Super User
Super User

Hi @GDSI ,

Firstly, setting a Table as a Variable it not a good idea (and you cannot append to them - that is what collections are for) - if you are only expecting one record

Set(
   gtblObsSearchResults, 
   LookUp(
      'Observations', 
      Lower('Created By'.Email) = Lower(gvarUser.Email)
   )
);

but I suspect there are more records than this so

ClearCollect(
   colObsSearchResults, 
   Filter(
      'Observations', 
      Lower('Created By'.Email) = Lower(gvarUser.Email)
   )
)

and your other code - however you need to specify what field you want to add here from the matching record in Observations

If(
   gvarWorkerIsManager,
   ClearCollect(
      colSearchResults,
      AddColumns(
         colMyEmps,
         "YourAddedField",
          LookUp(
             'Observations', 
             Lower('Created by') = Lower(WorkEmail)
            ).YourFieldNameRequired
         )
      )
   )
)

 

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.

Employees will have multiple observations. 

 

Won't the second ClearCollect empty the contents from the first ClearCollect?  So, would just Collect be used within the If(gvarManager) to preserve the first set of data from being just an employee? There are multiple columns from the Observations list that are needed. EmpID, ObsDate, ObsCity, ObsStreet, ObsStatus, Created By.  When the first data pull occurs, (for Employees) the collection column structure should include those columns. Then, when the second data pull for being a manager occurs, there are no more columns to add, only rows. So, I don't understand the benefit of the AddColumns() when I only want to Add rows.   But, I think you're saying I need to do something like this:

 

If(gvarWorkerIsManager,
  ForAll(gtblMyEmps,
    LookUp('Observations', Lower('Created By'.Email) = Lower(WorkEmail));
    Collect(gtblObsSearchResults,
        {
        EmpID : EmpID,
        ObsDate : ObsDate,
        ObsCity : ObsCity, 
        ObsStreet : ObsStreet,
        ObsStatus : ObsStatus ,
        'Created By'.DisplayName : 'Created By'.DisplayName
        }
)));

 

??

GDSI
Helper I
Helper I

I got this to work: 

 

If(gvarWorkerIsManager,
  ForAll(gtblMyEmps,
    Collect(gcolObsSearchResults,
    Filter('F-SOC-HaS-BBS-Observation', Lower('Created By'.Email) = Lower(WorkEmail))
    )
  )
);

 

Out of curiosity, why are variables that store tables bad? I treat them like a Snapshot, which is an old Access term, which holds a point-in-time snapshot of the data, and doesn't need to update/change during processing.

Hi @GDSI ,

If you do not want to further edit or query them further (and I am an old Access user as well), then I guess you can use them, but a Collection is in fact simply a Table variable with all the added advantages of being able to modify and more deeply query. As well, I had no insight into your coding experience and there was a possibility you did not fully appreciate the differences. 

 

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.

Visit my blog Practical Power Apps

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (3,535)