Append records to a variable/collection/table

[GDSI]

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

[WarrenBelz]

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.

[GDSI]

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 · Accepted solution]

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.

[WarrenBelz]

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