cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alienvolm
Helper III
Helper III

Adding a column from related table

Hello, 

 

My app connects to a SQL database. I have built a screen with a table that can be filtered and searched and all works fine. Now, my next issue is that my table is missing a column that belongs to a related table. I have already connected the app to both tables, but I don't know how to display a column from the second table. 

 

I read that I could use a Lookup function, but I'm not too clear how/where that function would reside:

 

Alienvolm_0-1622821977592.png

 

Any help would really be appreciated... 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User III
Super User III

@Alienvolm 

No worries.  I just want to make sure I am giving you accurate information that is helpful.

So, based on what you stated, the formula should be this:

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

This will produce a table that is a set of filtered JurneyUser records based on the BatchName.  The records in the table will all have an additional column called E911 that will be based on the Response column of the Record in the UserResponse table where the JourneyUserID is the same as the JourneyUser id and the QuestionID is 34.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

9 REPLIES 9
RandyHayes
Super User III
Super User III

@Alienvolm 

You have not mentioned the names of the other column or what the linking criteria is, but in general please consider changing your Formula to the following:

Search(
    AddColumns(
        Filter('[dbo].[JourneyUser]', 
            (BatchNameDropdown.Selected.Result = "All" || BatchName = BatchNameDropdown.Selected.Result)
        ),
        "otherColumn", LookUp(otherTable, criteria)
    ),
    TextInput2.Text, "BatchName"
)

Specify the name you want for your column and the criteria used to connect the current record to the looked up record.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Hi, 

 

I've modified the code as follows, but now I cannot retrieve any rows matching the criteria, whatever criteria I insert... Maybe I don't understand what criteria I need to use.

 

Alienvolm_3-1622828655166.png

Alienvolm_4-1622829218031.png

 

This is the relationship between the two tables: 

Alienvolm_2-1622828181372.png

My new column will be called "E911" and needs to retrieve the values from UserResponse.Response where QuestionID = 34. 

 

When I get this right, will I see the new column in the table or do I need to add it as I did for the other columns in the table?

 

Thanks again! 

 

RandyHayes
Super User III
Super User III

@Alienvolm 

Your formula is not correct.  You have changed the criteria on the first filter - which will not work.

Search(
    AddColumns(
        Filter('[dbo].[JourneyUser]', 
            (BatchNameDropdown.Selected.Result = "All" || BatchName = BatchNameDropdown.Selected.Result)
        ),
        "E911", LookUp(UserResponse, criteria)
    ),
    TextInput2.Text, "BatchName"
)

Now...for that "criteria" in the above formula - what column is it in the JourneyUser that holds the ID or other information for the UserReponse?  I see there is a JourneyUserID in the UserResponse, but I am guessing there are multiple records that will have that...so there needs to be more to get to the record you want. You mention QuestionID  = 34....is that to say you always want the record where the JourneyUserID matches and QuestionID is 34?  Or is there something more to this?

 

Doing this in the formulas will not alter your actual datasource, this is all about data shaping in your app.  The table result of the formula we are working on will have the added column.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

The field that contains the value I need to display is called Response. 

 

To summarize: 

The same user submits multiple responses (to questions 1-34). All the responses are collected into the Response field. I only need to display the responses to question 34. 

 

Sorry for the confusion... 

RandyHayes
Super User III
Super User III

@Alienvolm 

No worries.  I just want to make sure I am giving you accurate information that is helpful.

So, based on what you stated, the formula should be this:

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

This will produce a table that is a set of filtered JurneyUser records based on the BatchName.  The records in the table will all have an additional column called E911 that will be based on the Response column of the Record in the UserResponse table where the JourneyUserID is the same as the JourneyUser id and the QuestionID is 34.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

Thanks! 

 

It works! 🙂

 

Alienvolm_0-1622831617660.png

 

RandyHayes
Super User III
Super User III

@Alienvolm 

Very good!!  It's all about getting all the details 😁

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

🙂 

 

I believe my hurdles are far from finished with this app, but I'm learning a lot!

Thanks again!

 

RandyHayes
Super User III
Super User III

@Alienvolm 

Excellent...keep up the good work and learning!

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (2,659)