cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted

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
Highlighted
Super User II
Super User II

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!

Highlighted

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

Highlighted

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.

Highlighted

@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

 

Highlighted
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

Highlighted

@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 

Highlighted

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

Highlighted

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.
Highlighted

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (10,617)