Nested Concats and Filters

(0) Share

Report

Posted on by MrDannyHarry

144

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.

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (1)

GarethPrisk 2,828 on at

Like (1)

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

In regards to an output:
What are you looking to accomplish?
Are you looking to create a matrix-type report?
Rows are Visits
Columns are Questions
Cells are Answers (per visit/Question)
How is this being consumed?
Are you trying to compare questions per visits?
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!

Was this reply helpful? Yes No
MrDannyHarry 144 on at

Like (0)

Report

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

Was this reply helpful? Yes No
GarethPrisk 2,828 on at

Like (1)

Report

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.

PowerUsers - Nest...

PowerUsers - Nest...

Was this reply helpful? Yes No
MrDannyHarry 144 on at

Like (0)

Report

@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

Was this reply helpful? Yes No
v-siky-msft on at

Like (1)

Report
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

Was this reply helpful? Yes No
MrDannyHarry 144 on at

Like (0)

Report

@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

Was this reply helpful? Yes No
v-siky-msft on at

Like (0)

Report
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

Was this reply helpful? Yes No
MrDannyHarry 144 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

MrDannyHarry 144 on at

Like (0)

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

Was this reply helpful? Yes No