Unanswered

Set Variable and filter based off a lookup column

(1) Share

Report

Posted on by powerHelp

Hello, I am trying to do two things: Set a variable to a SP list to filter the cert titles if the logged in user email is the same as email in the person column and then update the context of one of my textboxes based on the lookup column values in the SP list. I keep getting an error that says that I have an invalid number of arguments. How can I fix this? Thank you!

These are my two functions:

Set(myCertCollection,
 If(User().Email = LookUp('Staff Certifications', Person.Email), Filter('Staff Certifications', Certification.Value && Status.Value = "Certification Active")),
 
 "Title", Ascending
 
);

//Foundation-----------------------------
UpdateContext({uDS1: Concatenate(CountIf(LookUp(myCertCollection, Certification.Value,
'Practice Area'.Value = "Digital Services" && 'Practitioner Level'.Value = "1 - Foundation"), 
" Certifications", Char(13), Char(13))});

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (0)

Sort by

poweractivate 11,078 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@powerHelp This doesn't look correct to me at all, and I'm looking at just the myCertCollection part. First of all the Set formula you gave has syntax that looks like it belongs in SortByColumns function.

Next you have User().Email = LookUp('Staff Certifications', Person.Email) when maybe it should be User().Email =
LookUp('Staff Certifications', Person.Email).Email

Then you have Filter('Staff Certifications', Certification.Value && Status.Value = "Certification Active") - here you have Certification.Value && - are you sure Certification.Value resolves to a true or false (i.e. boolean) value by itself? If not, you should change Certification.Value && to something else.

I didn't even look at the Foundation one but taking a quick look at it, you have
LookUp ( myCertCollection ,Certification.Value ,'Practice Area'.Value = "Digital Services" && 'Practitioner Level'.Value = "1 - Foundation" )
However the third argument of Lookup should be a reduction formula to return a specific result based on the found record, I don't think it should be additional filters, those belong in the second argument only. So you should have something like
LookUp ( myCertCollection ,Certification.Value && 'Practice Area'.Value = "Digital Services" && 'Practitioner Level'.Value = "1 - Foundation" )
I am however assuming Certification.Value resolves to a boolean - if it doesn't, the above formula will result in errors too.

There may be more issues too. What are the errors you see and in which formulas do you see them in?

Was this reply helpful? Yes No
powerHelp 8 on at

Like
a
(0)

Report
Copy link

Link copied!

It says that they both have invalid arguments still

Was this reply helpful? Yes No
powerHelp 8 on at

Like
a
(0)

Report
Copy link

Link copied!

And for the .Email part after the parenthesis, it says email isn't recognized

Was this reply helpful? Yes No
powerHelp 8 on at

Like
a
(0)

Report
Copy link

Link copied!
Last thing is that it says that this functions has incompatible comparison types:
If(User().Email = LookUp('Staff Certifications', Person.Email).Email,

Was this reply helpful? Yes No
poweractivate 11,078 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@powerHelp

Set(myCertCollection, If(User().Email = LookUp('Staff Certifications', Person.Email), Filter('Staff Certifications', Certification.Value && Status.Value = "Certification Active")), "Title", Ascending );

Even if you change it to

Set(myCertCollection, If(User().Email = LookUp('Staff Certifications', Person.Email).CorrectColumn, Filter('Staff Certifications', Certification.Value && Status.Value = "Certification Active")), "Title", Ascending );

Even if CorrectColumn is the correct one (maybe yours is not called Email) this is still invalid syntax because "Title" and "Ascending" belong in the SortByColumns function,not Set, So you may need to try like this to test:

Set ( myCertCollection ,If ( User().Email = LookUp('Staff Certifications', Person.Email).CorrectColumn ,Filter('Staff Certifications', Certification.Value && Status.Value = "Certification Active") ) );

Your Staff Certifications record result column containing the Email may not be called Email. Replace CorrectColumn above with what is appropriate and results in the email column you are looking for in that LookUp Record result.

Was this reply helpful? Yes No