LookUp with two conditions returns nul values

[Alienvolm]

Hi, 

In my app I have two SQL Server tables as data sources: [dbo].[JourneyUser] and UserResponse. 

I need to create a search that returns all the fields of [dbo].[JourneyUser] plus the field Response from UserResponse where the user ID is the same and the QuestionID field = 17. The new field is called E911. 

I used a LookUp function to do that, but it returns only empty records:

What other functions could I use? Or could I perform the equivalent of an Inner join and store the results in a Collection?  

Thanks in advance for the suggestions... 

[timl]

@Alienvolm 

What I would recomend here is that you create a SQL Server view that joins the 2 tables, and to filter/search the view.

Here is the basic T-SQL for the view:

CREATE VIEW vwJourneyUserResponse
AS
SELECT * FROM
[dbo].[JourneyUser] ju
JOIN
[dbo].[UserResponse] ur
ON
ju.JourneyUserID=ur.ID

From Power Apps, you can the search/filter directly against the view.

Search(
   Filter(vwJourneyUserResponse,
          (BatchNumberDropdown_1.Selected.Result = "All" ||
           BatchName=BatchNumberDropdown_1.Selected.Result)
          And QuestionID=17
   ),
   TextInput2.Text, 
   "BatchName"
)

[RandyHayes · Accepted solution]

@Alienvolm 

The problem is more likely on the id.  Does your JourneyUser table have an id column?

In that case, I would change the formula to the following:

AddColumns(
    Search(
        Filter('[dbo].[JourneyUser]', 
            (BatchNameDropdown.Selected.Result = "All" || 
             BatchName = BatchNameDropdown.Selected.Result)
        ),
        TextInput2.Text, "BatchName"
    ) As _item,
   "E911", LookUp(UserResponse, JourneyUserID= _item.id && QuestionID=34, Response)
)

I hope this is helpful for you.

[Alienvolm]

@RandyHayes 

That worked!

Thank you! 

[Alienvolm]

@RandyHayes I spoke too soon... now the search doesn't work anymore... The gallery will not retain the batch selection... 

[RandyHayes]

@Alienvolm 

Well, that seems like a different issue.  Is it related to the initial problem of the empty column from the Lookup or is that working properly now?

What do you mean by the gallery will not retain the batch selection?  Are you talking about the actual selected row? When does it change?

[Alienvolm]

@RandyHayes 

When I run the app, if I select a batch from the dropdown list, the values in the gallery don't change (it always shows all of them): 

OLD CODE: 

NEW CODE: 

[RandyHayes]

@Alienvolm 

Well, that is odd because we did not make any changes to that part of it.

That search/filter is all part of the inner statement.  We only changed the outer AddColumns lookup.

What happens if you put your formula back to what it was?  

[Alienvolm]

I know... 
If I put back the old formula the search and the filtering work... 

I just tried to eliminate the ID condition altogether (removed

 JourneyUserID = id && )and believe it or not, everything works fine... I only have two test batches, so I'm not sure if this will work but for now it looks like that! 

Not sure I understand it... 

[RandyHayes]

@Alienvolm 

Well, you're going to need that in there at some point because that is how you are looking up the question number result for the journey user that is in the table.

Let me see what your formula looks like at this point.