Lookup depending on multiple conditions

[FY]

Hello;

I am very new to power apps. I am trying to create a simple app that lets the users pick a unit and a component and then shows the hours. The app is conncected to an excel sheet (which in turn gets it's data through a connection from microsoft access). So here is the issue. I have been trying to use the lookup app in order to find a scpecific value depending on the user input. The issue is it needs to return the value from column "Unit_x0020_SMU_at_x0020_Install" from where values in the other cells match the user input and that cell "Removal_x0020_Date" is empty. I have been using the code below, but so far instead of returning a value, it just shows it as empty.

LookUp('Table_CNRL_Component_Database.accde3', Text('Unit No')=Text(Unit_x0020_Number) && Text('Component ID')=Text(Component) && IsEmpty(Removal_x0020_Date),Unit_x0020_SMU_x0020_at_x0020_Install)

[PaulD1]

Try replacing: IsEmpty(Removal_x0020_Date)

with: IsBlank(Removal_x0020_Date)

IsEmpty is for enumerations and IsBlank is for individual values

[FY]

Thanks for the advice. I tried that and the results are the same. Instead of populating the field with the number, it just shows it as blank

[TimRohr]

Hello @FY...

I would suggest three things. First, LookUp() can get screwy and return nothing when there is definitely something there. To be sure you are going to get what you're looking for, let's convert that to a First(Filter()) construction (which returns a record), and since you want a particular field, we'll make it a First(Filter()).field construction. Second, a combobox might be the better implementation to let the user pick the Unit Number and Component ID. These can dynamically draw from the datasource to provide a fulsome list of the fields they represent, they allow typing but they don't require it, and they don't require the user to type the pieces of data exactly. They are also easier to refer to in code, since they have the data as it comes from excel (exact, even in type)... that means you don't have to do Text() conversions of it. If it were me, this is the direction that I would go. If you want help with that, post back.

For now, I will proceed with the textboxes you have. So, the third thing to suggest is to build the First(Filter()).field stuff from the inside out and make sure you get the return you are looking for in each situation. For troubleshooting purposes, add a gallery to your screen and go into the Items property.

Step 1

Set the Items property of the gallery to Table_CNRL_Component_Database.accde3. Make sure you have four textbox controls in the Gallery. Set each to pull a different field from the datasource by putting the field name in the Text property. You want to see:

Unit_x0020_Number

Component

Removal_x0020_Date

Unit_x0020_SMU_x0020_at_x0020_Install

You should see all of the records in the excel file. If so, move on to Step 2.

Step 2

Add the basic Filter() statement to Items property of the Gallery.

Filter('Table_CNRL_Component_Database.accde3', IsBlank(Removal_x0020_Date))

You should see all of the records that have a blank "Removal Date" (so far we're not worried about Component, Unit Number, etc.). If you see the appropriate records, move on to Step 3.

Step 3

Expand the Filter() statement to look at the Unit Number:

Filter('Table_CNRL_Component_Database.accde3', IsBlank(Removal_x0020_Date) && Text('Unit No') = Text(Unit_x0020_Number))

That should get you a set of returns where the Unit Number matches your input and the Removal Date is blank. Make sure you enter a Unit Number that has a blank Removal Date to test this one. If you see that, go on to Step 4.

Step 4

Replace the new part of the Filter statement (in orange, above) with a check of the Component DI:

Filter('Table_CNRL_Component_Database.accde3', IsBlank(Removal_x0020_Date) && Text('Component ID') = Text(Component))

Make sure you see what you would expect to see.

Step 5

Combine the Filter() tests into one statement:

Filter('Table_CNRL_Component_Database.accde3', IsBlank(Removal_x0020_Date)  && Text('Unit No') = Text(Unit_x0020_Number) && Text('Component ID') = Text(Component))

Now you should only get the records that exactly match. In fact, depending on your data, this could be only ever as much as 1 record. However, realize that Filter() returns a table (that's why we were troubleshooting with a Gallery). Even a 1-record table object is still a table. To get the First record, we have to wrap the Filter() statement in -- you guessed it -- a First() statement. So if you are seeing the right data at this point, it's time for Step 6.

Step 6

Add a textbox to your screen and set the Text property to be your Filter statement, wrapped up in a First() statement:

First(Filter(......., ......))

Now, just like Filter() produces a table, First() produces a record. This isn't specific enough for our textbox to handle, so we're going to add a dot-field notation to the end:

First(Filter(......., ......)).Unit_x0020_SMU_x0020_at_x0020_Install

That should be the piece of information you are looking for. But, like I said, I would consider doing this with comboboxes. It might make things easier on your users.

[v-xida-msft · Accepted solution]

Hi @FY,

Could you please share a bit more about the 'Unit No' and 'Component ID' mentioned within your LookUp formula? Do they represent the Text Input box within your app?

I assume that the 'Unit No' and 'Component ID' both represents the Text Input boxes within your app, I have made a test on my side, and don't have the issue that you mentioned.

Please take a try to modify your formula as below:

LookUp(
      'Table_CNRL_Component_Database.accde3', 
      Text(UnitNoTextBox.Text) = Text(Unit_x0020_Number) && Text(ComponentIDBox.Text) = Text(Component) && IsBlank(Removal_x0020_Date),
      Unit_x0020_SMU_x0020_at_x0020_Install
)

Note: The UnitNoTextBox represents the Text Input box within your app, in which the user enters Unit Number. The ComponentIDBox represents the Text Input box within your app, in which the user enters Component.

In addition, you could also consider take a try with the following formula:

First(
      Filter(
              'Table_CNRL_Component_Database.accde3', 
              Text(UnitNoTextBox.Text) = Text(Unit_x0020_Number) && Text(ComponentIDBox.Text) = Text(Component) && IsBlank(Removal_x0020_Date)
       )
). Unit_x0020_SMU_x0020_at_x0020_Install

If the issue still exists, please consider take a try to save your Excel Table records into a Collection within your app, then check if the Unit_x0020_SMU_at_x0020_Install column within the corresponding record is blank:

ClearCollect(RecordsCollection, 'Table_CNRL_Component_Database.accde3')

You could also take a try to re-create a new connection to your Excel table from your app, then try above formula again, check if the issue is solved.

Best regards,

Kris

[aqureshi]

If(LookUp(colUserMaster, RoleID=RoleID, IsHCC = false) & LookUp(colUserMaster, RoleID=RoleID, IsNCC = false), true, false)