I have a nested gallery: glyUnitCount
Items: GroupBy(AddColumns(Filter(AttendanceData,Program=varProgram,YRweekNumber=lblYrWeekNum_1.Text,P_or_A="P"),"UnitNum",LookUp(colAlist,Name = AttendeeName,NumberOFUnits)),"AttendeeName","UnitNum","UnitbyAttendee")
Parent gallery: glyDaily
Items: Filter(colAlist,End_Date>=dtAttendanceDate.SelectedDate And Start_Date<=dtAttendanceDate Or IsBlank(End_Date),Program=varProgram)
My Data is a Sharepoint List.... AttendanceData, and a Collection.... colAlist.
I can not seem to get the syntax correct to tie them together by AttendeeName. This does not have to be nested galleries but I can't figure out how to combine the two. Essentially I need the filter from the Parent gallery to filter the nested gallery.
Any suggestion is appreciated!
Solved! Go to Solution.
I'm just trying to understand what you are trying to achieve here. Is the aim to get the Parent gallery to display the related data from AttendanceData? Could you provide an example of your end goal.
In the interim and as a suggestion, you could try using ThisItem in your nested gallery, like this:
GroupBy( AddColumns(Filter( AttendanceData, Program=ThisItem.varProgram, YRweekNumber=lblYrWeekNum_1.Text,P_or_A="P"), "UnitNum",ThisItem.NumberOFUnits), "AttendeeName", "UnitNum", "UnitbyAttendee" )
Essentially, you can reference the Parent gallery inside the nested gallery by using ThisItem, hence all of the Parent gallery data is available to use via this method.
End result is to know if there are units left to use.
Each week an AttendeeName has a NumberOFUnits. These are stored in the collection colAlist. I then need to count the UnitbyAttendee (this is a count of records from AttendanceData sharepoint list that match the Program and AttendeeName) used for a given week. And subtract that from the NumberOFUnits. So basically colAlist.NumberOFUnits - UnitbyAttendee = Units remaining. While filtering by the chosen dropdown Program.
I tried your code and I get a warning that 'This formula uses scope, which is not presently supported for evaluation.
This warning for both "ThisItem." codes.
I was able to change one from Program = ThisItem.varProgram, to Program = varProgram. And that helped.
I want to see just the matching Attendee's info in the sub gallery so would a Lookup be appropriate/helpful?
Ugh, I just noticed by NumberOFUnits is not being added as a the correct values for AttendeeData. It is populating a column but the value is only the first available. I don't understand why it's so difficult to combine 2 sources that have a common field.
Update...I've tried to simplify things... I now have these galleries...
Parent gallery: glyDaily
Sort(Filter(colAlist,End_Date>=dtAttendanceDate.SelectedDate And Start_Date<=dtAttendanceDate Or IsBlank(End_Date),Program=varProgram),AttendeeName)
I have included the NumberOFUnits field in a label - lblNumUnit.
Now I need to tie the two galleries together so that I can make the calculation "NumberOFUnits - UnitbyAttendee''
Do you have some sample data you can show me? I'm still not picking up what you are putting down. You can 'join' two tables but it can either easy or difficult depending on your setup and needs.
The easiest way to do this is to use GroupBy() on the Child table and group on the foreign key.
You can then fill out the rest of the items in the outer Gallery by using lookups to the Parent table, for example,
Lookup(ParentTable, ID=Foreign key, Field1). The subgallery Items property would be ThisItem.RestOfData.
I've tried cutting everything down and just don't know where to put the Lookup clause.
Parent - glyDaily
Child - glyUnitCount
Attached is some sample data
Check out our new profile badges recognizing authored solutions!
We are excited to announce the Power Apps Super Users!
Did you miss the call? Check out the Power Apps Community Call here.
Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!