LookUp in Filter returns wrong values

(0) Share

Report

Posted on by PVosEska

329

Hi,

I'm using a PowerApps EditForm and added a custom card.

In here i put a ListBox that checks a different list for ID-Numbers and retrieves values of a different column.

I have 3 columns in here; ProjectLeader, Employee, and HoursInProject.

code:

Filter(
 'Medewerkers Verbeterprojecten Operations',
 LookUp(
 'Medewerkers Verbeterprojecten Operations',
 ProjectID = Gallery1.Selected.ID,
 Projectleider
 )
)

The formula is going to check which SharePoint ListItems match the Gallery1.Selected.ID and return that ProjectLeader.

The strange thing is, is that i'm getting the employees back (different column) that match the Gallery1.Selected.ID.

No matter what i want the result of the LookUp to be, it still give me the matching employees instead of the projectleader.

Same happens when i want the 'HoursInPRoject' as a result of the LookUp.

Any idea how to tackle this?

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Pstork1 68,717 Most Valuable Professional on at

Like (0)

Report
The Syntax for Filter is

Filter( Table, Formula1 [, Formula2, ... ] )

The third parameter you are specifying is being evaluated to see if the record should be in the filtered data source. It doesn't control which columns are returned. So your filter is basically asking for all the records where the ProjectID matches the selected ID and where the ProjectLeader column exists.

Depending on the control in the custom card you should be able to select which field from the filtered data source is being shown. You can't do that in the filter statement itself.

Was this reply helpful? Yes No
v-yutliu-msft on at

Like (0)

Report
Hi @PVosEska ,

Could you describe more clearly about what do you want to filter based on and what value do you want to get?

The right syntax of Filter should be like:

Filter(tablename, formula1[,formula2,formula3,....])

//for example Filter(tablename, field1=value1,field2=value2)

This formula will return a table. (the records that meet the formulas)

The right syntax of LookUp function should be like:

LookUp(tablename,formula[,fieldname])

//For examplr: LookUp(tablename, field1=value1,field2)

If you just not use fieldname in the formula, you will get one record that meet the requirement

If you use fieldname in the formula, you will get one value( the field value of that record)

So the formula that you use:

LookUp( 'Medewerkers Verbeterprojecten Operations', ProjectID = Gallery1.Selected.ID, Projectleider )

This will return one record's Projectleider value.

So you could see your formula as:

Filter( 'Medewerkers Verbeterprojecten Operations', value //This is not a formula, you need to use formula like: field=value )

Here is a doc about these two functions for your reference:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup

Best regards,

Was this reply helpful? Yes No
PVosEska 329 on at

Like (0)

Report

Hi @v-yutliu-msft ,

I am trying to get a list of employees with the same ID from a second SharePoint list.

I do this by:
- Creating two lists, one for the project, one for the employees and projectleader of that project
- Creating the App, inserting an EditForm,
- Collecting the LastSubmit.ID on 'SubmitForm'
- let users put in the relevant employees for that project
- Patch it to a second list, with the ProjectID the same as the LastSubmit.ID.

I need a combination of the filter() and LookUp() as i can't choose what i want the result to be with the filter, but can't collect more than 1 item with a LookUp().

Was this reply helpful? Yes No
PVosEska 329 on at

Like (0)

Report

Hi @Pstork1,

Unless i read it wrong, i don't think that is correct.

My Formula basically consists of: Filter(<DataSource>,LookUp(<DataSource>,Condition,result))
So i'm just using the LookUp as the second parameter of the filter, i am not using a 3rd Param.

Was this reply helpful? Yes No
Pstork1 68,717 Most Valuable Professional on at

Like (0)

Report

Sorry, I misread your code. But even if you reduce to a single column it still returns it in the form of a record not a text value. So you need to add the property to your card to get the specific value out of the object.

Was this reply helpful? Yes No
Verified answer

PVosEska 329 on at

Like (1)

Report

@Pstork1 ,

Okay, i did found out though that i can also use a .Value after the filter, and that was exactly what i needed. so it boils down to: 'Filter(<DataSource>,Condition).Value'

Where .Value is de column that you want to show. In my case, this was ProjectLeader or Employee

WHY DID I NOT KNIOW THIS! thanks though!

Was this reply helpful? Yes No
Pstork1 68,717 Most Valuable Professional on at

Like (1)

Report

As I mentioned in my previous reply. The Lookup is returning a record, which is an object. You have to specify which property you want to display. That is the 'value' name you are adding.

Was this reply helpful? Yes No