Unanswered

Query on list with a one to many structure

(0) Share

Report

Posted on by Antonioclk

Hello, i have a trivia app which makes questions based on certain topics. I made the following DB schema (i'm using sharepoint lists to store the data). Every topic can have several questions, one question instead is related only to one specific topic. Screenshot 2023-04-11 110329.png

once my user pick a topic, the id is stored in the variable ChosedTopicID... how can i have a collection which stores all the questions where their topicID is equal to ChosedTopicID. how can i do that?

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (0)

Sort by

WarrenBelz 155,962 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Antonioclk ,

This should do it (but not Delegable)

With( { wQList: Filter( TopicsHaveQuestions, topicID = YourChosenTopicID ) }, Filter( Questions, questionID in wQList.questionID ) )

however SharePoint is not a relational database and SQL-type "look-though" queries really should not be used. If you add the topicID as a field in the question list and you will have a Delegable simple query

Filter( Questions, topicID = YourChosenTopicID )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
Antonioclk on at

Like
a
(0)

Report
Copy link

Link copied!

hi Warren,
i figured it was not the best approach to make a sql similar structure. how would it be the best way? (would it be good if using the dataverse?)

could you please explain me how to use your code? i have a button which sets the topicID, on that button i place the "with" code you wrote... but then if i add a table in the screen and link the question data source to it, it won't show the topic id...

i had tried to add a column with the topic id to the question table, but i can't understand how

Was this reply helpful? Yes No
Antonioclk on at

Like
a
(0)

Report
Copy link

Link copied!
I kinda found a solution...
ClearCollect( ChosenTopicQuestions, Filter( AddColumns( Questions, "qTopicID", LookUp( TopicsHaveQuestions, questionID = Questions[@questionID] ).topicID ), qTopicID=ChosenTopicID ) )

this actually works, it return a collection which stores all the Questions items, filtered by a newly added column qTopicID. the only issue is that all the fields name are messed up and gets called field1, field2 and so on... how can i fix this? and still, what would have been the correct data structure to use?
Thanks

Was this reply helpful? Yes No
WarrenBelz 155,962 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@Antonioclk ,

Your last post is similar to my suggestion of putting the topicID in the Questions list, but you are doing it temporarily rather than permanently (which I always do as I practice "one degree of separation" between any two lists referring to the other. By the field names, I assume you created the list by importing from Excel (not a good idea).

The code I posted can be used for a collection like this

With( { wQList: Filter( TopicsHaveQuestions, topicID = YourChosenTopicID ) }, ClearCollect( ChosenTopicQuestions, Filter( Questions, questionID in wQList.questionID ) ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No