cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User
Super User

Complex Gallery Sort/Filter/LookUp. Need Help.

Hello, 

I'm hoping somone has some insights on this one ( @CarlosFigueira@Shanescows@mrdang ? 🙂 ). I'm seriously stuck!

 

  • Data Source:
    • Sharepoint custom list named "TI_Evaluation_Tool_Results"
    • data_source.png
  • Use Case:
    • Employees submit a monthly evaluation where up to 13 competencies may be evaluated.
  • Compliance:
    • Employee must evaluate at least 2 competencies every month.
    • Employee must evaluate all 13 competencies every half (6 month period).
  • Key Sharepoint Columns: 
    • [rdm_cost_center] : Employee.
    • [compX_num] : Competency that was evaluated. Numbered [comp1_num] - [comp13_num].
    • [year] : Calculated SP column. Applies =TEXT(evaluation_date,"yyyy") to each record.
    • [half] : Calculated SP column. Applies =TEXT(evaluation_date,"mm") to each record. If "mm" = "01"-"06, period "1","2")
    • [month] : Calculated SP column. Applies =TEXT(evaluation_date,"mm") to each record.
  • Global variables: 
    • Used to make the app aware of the year, half, and month for comparison on the Compliance Screen
    • Sets Year: Set(thisYear,Text(Today(),"[$-en-US]yyyy"));

    • Sets Half: 
      If(Text(Today(),"[$-en-US]mm")="01",
      Text(Today(),"[$-en-US]mm")="02",
      Text(Today(),"[$-en-US]mm")="03",
      Text(Today(),"[$-en-US]mm")="04",
      Text(Today(),"[$-en-US]mm")="05",
      Text(Today(),"[$-en-US]mm")="06",
      Set(thisHalf,"1"),"2");

    • Sets Month: Set(thisMonth,Text(Today(),"[$-en-US]mm"))

  • Gallery.Items =  GroupBy(TI_Evaluation_Tool_Results,"rdm_cost_center","group")
    • Using this formula for .Items, I'm able to get each rdm_cost_center to display in the Gallery
  • compX_num label = LookUp(TI_Evaluation_Tool_Results,rdm_cost_center=ThisItem.rdm_cost_center).comp1_num
    • This gets some of the compX_nums to display, but is not comparing the year, half, or month. 
    • How do I display the competency if it was evaluated this year and this half? 
    • How can I set the Fill property to display a different color if competency was evaluated this year, half, and month?
    • gallery1.png

I can't seem to figure out the combination of Sort/Filter/Lookup needed to display the [compX_num] in the label. Any leads would be SO much appreciated. I'm about 3 days into trying different things! 

 

Thank you

 

 

 

2 REPLIES 2
Community Support Team
Community Support Team

Re: Complex Gallery Sort/Filter/LookUp. Need Help.

If you would like to filter the record together with Year and half, then the Lookup formula should be written in this way:

 

Lookup(ListName,
             and(cost_center=ThisItem.cost_center,
                     thisYear=ThisItem.year,
                     thisHalf=ThisItem.half)).comp1_num

For the Fill property, could you please explain a bit for the

 

"evaluated this year, half, and month" condition?

Basically, the color change mostly depends on the If condition.

The logic for the fill property should be:

 

If (ConditionMet, color1, color2)

For example, for this Year color:

 

 

If (thisYear=ThisItem.year, color.azure, color.white)

Regards,

Michael

 

 

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Complex Gallery Sort/Filter/LookUp. Need Help.

Hello @v-micsh-msft

 

Thank you for looking further into this. The "year", "half", and "month" fields were of the Sharepoint Calculated-type. This was causing problems. Once I replaced them with Single Line of Text columns and handled the variables in the app, I was able to get the Lookups to work. 

 

 

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 188 members 5,368 guests
Please welcome our newest community members: