Complex Gallery Sort/Filter/LookUp. Need Help.

[ericonline]

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"
  • 
• 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")
    • Thank you @Anonymous for this post
  • [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?
  • 

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

[v-micsh-msft]

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

[ericonline]

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.