Nested Concats and Filters

[MrDannyHarry]

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'

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:

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.

[GarethPrisk]

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!

[MrDannyHarry]

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_id	visit_date	custid	visitid	status
107	03/042020	668	107-668-03-04-2020	1

q_groups

ID	group
1	Group 1
2	Group 2

questions table (questions.q_group = q_groups.id)

ID	question	q_group	1_status
1	Question 1	1	1
2	Question 2	2	1

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

ID	q_id	q_group_id	answer	visitid
1	1	1	1	107-668-03-04-2020
2	2	2	0	107-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

[GarethPrisk]

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.

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.

[MrDannyHarry]

@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]

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

[MrDannyHarry]

@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 

[v-siky-msft]

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

[MrDannyHarry]

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.

[MrDannyHarry · Accepted solution]

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!!