cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MrDannyHarry
Resolver I
Resolver I

Nested Concats and Filters

Hi All,

 

Another I'm sure really easy question from myself!
I am currently building a HTML report into my App.
I have got so far but I am trying to join Tables and failing a bit


My SQL Query which works fine in SSMS

 

 

Select answer from answers
Where visitid = '107-668-03-04-2020' and q_id = '6'

 

App - Report Query02.PNG

 

What I have used in Power Apps

 

 

Concat(
    '[dbo].[q_groups]',
    "<h3>" & group_name & "</h3>" & Concat(
        Filter(
            '[dbo].[questions]',
            q_group in '[dbo].[q_groups]'[@ID]
        ),
        "<p>" & question & Concat(
            Filter(
                '[dbo].[answers]',
                visitid = PV_VisitID && q_id in '[dbo].[questions]'[@ID]
            ),
            " - " & answer & "</p>"
        )
    )
)

 

 

 

This outputs like this:
App - Report Query.PNG

 

What it should be is like this

Group 1

Question 1 - Answer to Q1

Question 2 - Answer to Q2

 

Group 2

Question 1 - Answer to Q1

Question 2 - Answer to Q2

 

It is worth noting I am filtering this based on a visit ID as well.

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hey @GarethPrisk & @v-siky-msft,

 

This code seemed to get me exactly what I needed!

Concat(
    '[dbo].[q_groups]',
    "<h3>" & group_name & " - " & ID & "</h3>" & Concat(
        Filter(
            '[dbo].[answers]',
            visitid = PV_VisitID && Value(q_group_id) = '[dbo].[q_groups]'[@ID]
        ),
        "<p>" & LookUp(
            '[dbo].[questions]',
            q_id = ID,
            question
        ) & " - " & answer & "</p>"
    )
)

Thank you for all your help!!

View solution in original post

9 REPLIES 9
GarethPrisk
Resident Rockstar
Resident Rockstar

Can you elaborate a bit more of your data sources?

  1. Are there separate tables for Questions, Visits, Answers, etc.?
  2. If so, how are they structured/related?
    1. Ex: List of Questions, List of Visits, Answers act as N:N to allow a Visit to have multiple Questions, and the Answer?
  3. If so, what type of data integrity is there?
    1. Will each Visit have each Question?
    2. Will each Question be answered (be non-null)?

 

In regards to an output:

  1. What are you looking to accomplish?
  2. Are you looking to create a matrix-type report?
    1. Rows are Visits
    2. Columns are Questions
    3. Cells are Answers (per visit/Question)
  3. How is this being consumed?
    1. Are you trying to compare questions per visits?
    2. Are you trying to summarize a question, or a visit? Both?

 

I ask, because there's probably a lot of ways to solve this. The output example you have, is...well...a bit tricky to read. 😅

 

I have created a few matrix-type canvas apps, and I have some ideas for how to create the row/column/cell structure I alluded to above. Let me know!

Hi @GarethPrisk 

 

Many thanks for your reply!
I never was the best at explaining.

 

The App needs to create a report at the end of a visit.

The report is built up of questions and answers and notes.

 

The Table structures are as follows.

 

visitlog table

na_idvisit_datecustidvisitidstatus
10703/042020668107-668-03-04-20201

 

q_groups

IDgroup
1Group 1
2Group 2

 

questions table (questions.q_group = q_groups.id)

IDquestionq_group1_status
1Question 111
2Question 221

 

answers table (answers.q_id = questions.id) (answers.q_group_id = q_groups.id) (answers.visitid = visitlog.visitid)

IDq_idq_group_idanswervisitid
1111107-668-03-04-2020
2220107-668-03-04-2020

 

I've tried to make this as simple as possible to how they all link.

 

What i would like it to look like for now is

 

Group 1 (Group name - Pulled from q_groups)

Question 1 for Group 1 (Question name - pulled from questions)  - Answer for Q1, G1 (Answers result - pulled from answers table)

Question 2 for Group 1 (Question name - pulled from questions) - Answer for Q2, G1 (Answers result - pulled from answers table)

 

Group 2 (Group name - Pulled from q_groups)

Question 1 for Group 2 (Question name - pulled from questions)  - Answer for Q1, G2 (Answers result - pulled from answers table)

Question 2 for Group 2 (Question name - pulled from questions) - Answer for Q2, G2 (Answers result - pulled from answers table)

 

I would like a report a bit like this for each visit.

But I don't want to hard code any groups, questions or answers as I want it to populate each time a new group or question is added to the DB

See attached for a working example, with data structured similarly to yours.

 

My app is using static Excel data, but otherwise it's structurally similar to what I think you're looking to accomplish. Some syntax and formatting required, but should get you close.

 

The base tables, but as static data.The base tables, but as static data.

 

Different Visit, and toggling to hide inactive questions.Different Visit, and toggling to hide inactive questions.Formatted per Group, and Question. Showing Questions which might be inactive, hiding questions which don't exist for Visit.Formatted per Group, and Question. Showing Questions which might be inactive, hiding questions which don't exist for Visit.

@GarethPrisk this is brilliant thank you.

I have something similar, But how do i get the results I need into a HTML page instead of a gallery?

 

Just need a HTML page with the answers like follows which generates for the selected visit. I will push the visit ID from the previous screen.

 

Group 1

Question 1 - Answer to Q1, Group 1

Question 2 - Answer to Q2, Group 1

 

Group 2

Question 1 - Answer to Q1, Group 2

Question 2 - Answer to Q2, Group 2

 

v-siky-msft
Community Support
Community Support

Hi @MrDannyHarry ,

 

Could you please try this?

Concat(
    '[dbo].[q_groups]',
    "<h3>" & group_name & "</h3>" & Concat(
        Filter(
            '[dbo].[questions]',
            q_group in '[dbo].[q_groups]'[@ID]
        ),
        "<p>" & question & "-" & LookUp(
            '[dbo].[answers]',
            visitid = PV_VisitID && q_id in '[dbo].[questions]'[@ID]
            ).answer & "</p>"
    )
)

Hope this helps.

Sik

@v-siky-msft Hey thanks very much for this reply!

 

It seems to give me the same answer to every question instead of the answer for that question 

Hi @MrDannyHarry ,

 

How about this?

Concat(
    '[dbo].[q_groups]',
    "<h3>" & group_name & "</h3>" & Concat(
        Filter(
            '[dbo].[questions]',
            q_group in '[dbo].[q_groups]'[@ID]
        ),
        "<p>" & question & "-" & LookUp(
            '[dbo].[answers]',
            visitid = PV_VisitID && q_id = '[dbo].[questions]'[@ID]
            ).answer & "</p>"
    )
)

Sik

Hey @v-siky-msft,

 

This has a formaula error on the following: 

q_id = '[dbo].[questions]'[@ID]
 
I'm not sure how to do it and pull the question ID from the Concat statement before.

Hey @GarethPrisk & @v-siky-msft,

 

This code seemed to get me exactly what I needed!

Concat(
    '[dbo].[q_groups]',
    "<h3>" & group_name & " - " & ID & "</h3>" & Concat(
        Filter(
            '[dbo].[answers]',
            visitid = PV_VisitID && Value(q_group_id) = '[dbo].[q_groups]'[@ID]
        ),
        "<p>" & LookUp(
            '[dbo].[questions]',
            q_id = ID,
            question
        ) & " - " & answer & "</p>"
    )
)

Thank you for all your help!!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

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.

Users online (2,656)