Complex Sort, Filter, LookUp function

[ericonline]

Hello, 

I'm hoping somone has some insights on this one ( @CarlosFigueira, @ShaneCows, @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

[Paul_C]

Hi ericonline,

Why are you using numbers for the values of comp1_num, comp2_num, etc?
It seems like true/false for whether or not they were evaluated is really what you want.

It can work with numbers, of course, but it seems like unnecessary complexity.

I also see a couple issues with how thisHalf is being set. You need to || all the comparisons instead of comma separating. The If() function will actually be like a bunch of if-else-ifs in what you currently have. I think this is more like what you want:

       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),Set(thisHalf,2)
        );

Here's the documentation on the If() function: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-if 

Notice how it has two usages:

If( Condition, ThenResult [, DefaultResult ] )
If( Condition1, ThenResult1 [, Condition2, ThenResult2, ... [ , DefaultResult ] ] )

Currently you're using the second option.

Anyway, with that sorted, we can move on to the main problem you're trying to address. First off, I can tell you why the current setup is behaving as it is.

GroupBy(TI_Evaluation_Tool_Results,"rdm_cost_center","group")

The "group" column doesn't exist on your data, so this statement effectively just creates a table (list) of rdm_cost_centers. This is fine, though, because you're doing additional look ups with those cost centers to find the competencies, so you don't necessarily need all the data to be part of the gallery's items.

The real problem is with label LookUps, I think:

LookUp(TI_Evaluation_Tool_Results,rdm_cost_center=ThisItem.rdm_cost_center).comp1_num

LookUp finds the first record matching the condition and returns that. It doesn't condense all of the columns into one value, or anything like that, so you're making all your evaluations off whatever record happens to be the first with the given rdm_cost_center name.

Instead of doing that, you would probably want your label to be something like the following:

If(
    !IsBlank(
        LookUp(
            TI_Evaluation_Tool_Results, 
            rdm_cost_center=ThisItem.rdm_cost_center And half = thisHalf And comp1_num >= 1
        )
    ), 
    true, 
    false
)

What this does is checks if any of the records for the current rdm_cost_center, for the current half, have a value. If so, it gives the label true, otherwise false. (Note if comp1_num is text, then the comparison will be different and more complicated. You would need to get the value from the text with the Value() function or check the length of the text to make sure it's not blank or check !IsBlank(). The problem with any of this is that it all requires you to evaluate another function for each record, and PowerApps may block it or warn against it for performance reasons.)

Finally (and optionally), if you want to nest the horizontal gallery inside, like I mentioned on a different post, your Items property for the inner gallery could be something like the following:

[
    If(!IsBlank(LookUp(TestSample, rdm_cost_center=ThisItem.rdm_cost_center And half = thisHalf And comp1_num >= 1)), true, false),
    If(!IsBlank(LookUp(TestSample, rdm_cost_center=ThisItem.rdm_cost_center And half = thisHalf And comp2_num >= 1)), true, false),
    If(!IsBlank(LookUp(TestSample, rdm_cost_center=ThisItem.rdm_cost_center And half = thisHalf And comp3_num >= 1)), true, false),
    <etc....>
]

But that isn't beautiful, either. Maybe you or someone else can come up with a more elegant solution?

I hope this helps!

[ericonline]

Paul, 

I tell ya what, receiving support like this just about brings a tear to my eye. Beyond thank you and yes, it helps me very much.

RE: "Why are you using numbers for the values of comp1_num, comp2_num, etc?"

• There are 4 columns associated with each Task
  • compx_num: a numeric value 1-13 that corresponds to tasks in a manual
  • compx_desc: a 30-200 character description of the task from the manual
  • compx_eval: a 140 character evaluation the user types in
  • compx_pass: a toggle where user states whether employee passes the task
• The _num is the easiest way I found to show which tasks have/have not been completed. Not being accustomed to programmatic thinking, I may be off. 

RE: The "half" Function and using || vs ","

• Thank you for that clarification. I made your recommended edits. 
• Global Variables shows correctly when a Date is chosen before and after the 6th month

RE: The Label LookUp Function

• The card_comp1Num.Update Function is set to Value(input_comp1Num.Text) . Which is good.
  • However, the Sharepoint list column type is set to "Multiple Lines of Text".
  • Does this also need to change to a "Number" type column?
• I used your recommended LookUp function for label_comp1 as a test...
• 
• ...but it returns "false" even though there is a "1" in the comp1_num column, for rdm_cost_center S0801, and half 1, in the Sharepoint list
• 

I tried setting the "!IsBlank" to "IsBlank" and tweaking some other parts of the Function, but cannot get a "true".

Why is the LookUp Function not finding these "1" 's?  

[Paul_C]

---

 RE: "Why are you using numbers for the values of comp1_num, comp2_num, etc?"

• There are 4 columns associated with each Task
  • compx_num: a numeric value 1-13 that corresponds to tasks in a manual
  • compx_desc: a 30-200 character description of the task from the manual
  • compx_eval: a 140 character evaluation the user types in
  • compx_pass: a toggle where user states whether employee passes the task

---

If comp1_num can only ever be 1 or blank, comp2_num can only ever be 2 or blank, etc, then really booleans will give you what you want. Did they complete task 1? True or false. That's what I meant. You know which task it is by the column name, so it doesn't have to be part of the data too. But, it sounds like that is almost exactly what compx_pass is, except true/false/blank.

Another question, though: does compx_desc ever change for a given x? For instance, if I have three records, one has comp1_num=1, the others are blank for comp1_num, what is comp1_desc? It sounds like, for a given task number, the competency description always remains the same (or "static"). In that case, I would recommend creating a second list with rows that have the competency id number (1-13) and the description, OR just adding it directly in the app (but only if it's actually being used). There's no need to store a bunch of static data as part of every record. (It's possible I've misunderstood what you're doing with that description, though.)

RE: The Label LookUp Function

• The card_comp1Num.Update Function is set to Value(input_comp1Num.Text) . Which is good.
  • However, the Sharepoint list column type is set to "Multiple Lines of Text".
  • Does this also need to change to a "Number" type column?

---

I'm not following. Are you saying compx_num is text? If that is the case, that would explain why the LookUp I gave you is failing. "1" can't be compared against 1 like I did in the LookUp. 

Good luck!

[ericonline]

Paul, 

Seriously, if you are in Seattle, I'd love to buy you lunch soon! Thank you for your time. 

RE: "Does compx_desc ever change for a given x?"

• No it doesn't. I am actually using a second list for the _desc's.
  • Shown as "SP Task List" on the whiteboard
• 
• I may be way off (again, non-programmer by nature), but here is how the user interacts: 
• Using checkboxes, user selects tasks to evaluate
• 
• Checking the box changes the visibility of a Task to true:
• The selected description is displayed (in the form card)
• User types in an evaluation, sets toggle to yes if employee passes task
• The compx_num is hidden, but still written to the results
• 

RE: Are you saying compx_num is text?

• The Update property for the comp1_num card is:

• If(card_comp1Eval.Visible=true,Value(label_comp1Num.Text),"Not Evaluated")

• AFAIK, this converts the text to a number.
  • Am I right or does the compx_num field need to be a Text Input rather than Label for the Value() function to work? 

Epic back and forth here Paul. I'm definitely not taking your time for granted and I do whole heartedly appreciate your insights. 

Eric

[ericonline]

Paul, 

Seriously, if you are in Seattle, I'd love to buy you lunch soon! Thank you for your time. 

RE: "Does compx_desc ever change for a given x?"

• No it doesn't. I am actually using a second list for the _desc's.
  • Shown as "SP Task List" on the whiteboard
• 
• I may be way off (again, non-programmer by nature), but here is how the user interacts: 
• Using checkboxes, user selects tasks to evaluate
• 
• Checking the box changes the visibility of a Task to true:
• The selected description is displayed (in the form card)
• User types in an evaluation, sets toggle to yes if employee passes task
• The compx_num is hidden, but still written to the results
• 

RE: Are you saying compx_num is text?

• The Update property for the comp1_num card is:

• If(card_comp1Eval.Visible=true,Value(label_comp1Num.Text),"Not Evaluated")

• AFAIK, this converts the text to a number.
  • Am I right or does the compx_num field need to be a Text Input rather than Label for the Value() function to work? 

Epic back and forth here Paul. I'm definitely not taking your time for granted and I do whole heartedly appreciate your insights. 

Eric

[ericonline]

Paul, 

Seriously, if you are in Seattle, I'd love to buy you lunch soon! Thank you for your time. 

RE: "Does compx_desc ever change for a given x?"

• No it doesn't. I am actually using a second list for the _desc's.
  • Shown as "SP Task List" on the whiteboard
• 
• I may be way off (again, non-programmer by nature), but here is how the user interacts: 
• Using checkboxes, user selects tasks to evaluate
• 
• Checking the box changes the visibility of a Task to true:
• The selected Task's description label, evaluation textbox and pass/fail toggle are displayed.
• User types in an evaluation, sets toggle to yes if employee passes task
• The compx_num remains hidden, but is still written to the results
• 

RE: Are you saying compx_num is text?

• The Update property for the comp1_num card is:

• If(card_comp1Eval.Visible=true,Value(label_comp1Num.Text),"Not Evaluated")

• AFAIK, this converts any text in the label to a number.
  • Am I right or does the compx_num field need to be a Text Input rather than Label for the Value() function to work? 

Epic back and forth here Paul. I'm definitely not taking your time for granted and I do whole heartedly appreciate your insights. 

Eric

[ericonline]

I know this is the longest forum post in the history of forum posts but...

I cannot for the life of me figure out why this LookUp function is not working. 

• The syntax is correct, the rdm_cost_center is correct
  • Though the fact that "comp1_num" does not have the blue squiggle while "half" does makes me wonder
• 
• The variable "thisHalf" is a "Number" type variable = 1
• 
• Both the "half" and "comp1_num" columns in the Sharepoint list are Number-type
  • With "half" being calculated (not sure if that matters). 
• 
• 
• But here it is, plain as day. A Sharepoint record meeting all the functions criteria yet the results still return "false" in PowerApps.
  • half = 1, rdm_cost_center = S0801, and comp1_num = 1
  • 

Any insights? Racking my brain here!

[Shanescows]

The blue warning is about delegation. How many items do you have in your SharePoint list? 

If you haven't read them I think it is highly useful to read this and then this. 

[ericonline]

Thanks Shane. Yep, I'm familiar with Delegation.

The list will eventually have thousands of records. Each "rdm_cost_center" in my example will have a max of 120 so I think i'm safe by performing the LookUp against this value. 

Above, I was curious why only 1 of the 2 Sharepoint columns had the blue squiggly. Both of them are in the same list. Seems like both would have delegation applied.

Great videos btw!