Hello,
I'm hoping somone has some insights on this one ( @CarlosFigueira, @ShaneCows, @mrdang ? ). I'm seriously stuck!
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"))
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
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!
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?"
RE: The "half" Function and using || vs ","
RE: The Label LookUp Function
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?
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!
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?"
RE: Are you saying compx_num is text?
If(card_comp1Eval.Visible=true,Value(label_comp1Num.Text),"Not Evaluated")
Epic back and forth here Paul. I'm definitely not taking your time for granted and I do whole heartedly appreciate your insights.
Eric
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?"
RE: Are you saying compx_num is text?
If(card_comp1Eval.Visible=true,Value(label_comp1Num.Text),"Not Evaluated")
Epic back and forth here Paul. I'm definitely not taking your time for granted and I do whole heartedly appreciate your insights.
Eric
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?"
RE: Are you saying compx_num is text?
If(card_comp1Eval.Visible=true,Value(label_comp1Num.Text),"Not Evaluated")
Epic back and forth here Paul. I'm definitely not taking your time for granted and I do whole heartedly appreciate your insights.
Eric
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.
Any insights? Racking my brain here!
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.
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!
Stay up tp date on the latest blogs and activities in the community News & Announcements.
Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST
Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.
User | Count |
---|---|
207 | |
194 | |
82 | |
58 | |
38 |
User | Count |
---|---|
303 | |
247 | |
119 | |
83 | |
55 |