cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mark999
Frequent Visitor

Filter form or create a Collection in a Many to Many relationship directly in CANVAS

I am using the Power Apps Canvas app…

 

I have been searching for an answer (with or without using Filter) for days.  Either it’s a brilliant and difficult question, it is an unsolvable problem (is the such a thing?), or I as dumb as a box of rocks! I am hoping it is the first one....

 

QUESTION:

I have:

FormA with 2 columns

FormB With 1 column

 

Form A has:

2 Columns Named:  CourseID & CourseTitle

It has 4 records in it

CourseID             CourseTitle

      1                       Course One

      2                       Course Two

      3                       Course Three

      4                       Course Four

 

Form B has:

1 Column Named:  CourseID

It has 2 records in it

CourseID

       2

       4

 

I wish to Create FormC.

FormC will should show only:

one column CourseTitle

with 2 records (the 2 IDs on FormB)

 

FormC result:

     CourseTitle    

    Course Two

    Course Four

 

FormC should show the CourseTitles (from FormA) but be filtered by only [all] the CourseIDs is present on FormB.

That is to say I wish to Filter FormC to show any and all the Course Titles that correlate to either of the records in FormB.

 

FormB could be a collection rather than a form but I believe I have the same issue.

For that matter both Forms A&B could be collections with FormC based on the result of the appropriate collection.

 

Either way, I’m trying to create a data set based on all records from a different data set (aka a Many to Many relationship)

 

NOTE:  I don’t have permissions to use such features as CDS or List Relationships, so please limit any answers to solving this problem using code within the Canvas app (if it is possible!)

 

Thanks for any and all help!

1 ACCEPTED SOLUTION

Accepted Solutions
v-jefferni
Community Support
Community Support

Hi @Mark999 ,

 

I may suggest a formula for your scenario first:

Filter(ForAll(FormA As A, If(A.CourseID in FormB.CourseID, A.CourseTitle, ""), !IsBlank(CourseTitle))

 

Best regards,

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

3 REPLIES 3
Drrickryp
Super User
Super User

@Mark999 

To work with PowerApps, you need to construct the tables and relationships first.  The design will naturally flow when the tables and relationships are created.  In a Many to Many relationship (the classic example is Students and Courses), three tables will be required, the student list, the course list and a junction table between them that holds the primary keys of each of them. !1.png To try to build the app starting with the user interface, is like building a house from the roof down. 

I do not have the rights in Teams to create any table relationships. However I figured out a way and will post that solution as soon as I have a chance.

v-jefferni
Community Support
Community Support

Hi @Mark999 ,

 

I may suggest a formula for your scenario first:

Filter(ForAll(FormA As A, If(A.CourseID in FormB.CourseID, A.CourseTitle, ""), !IsBlank(CourseTitle))

 

Best regards,

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,248)