cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
marcvanderlee
New Member

Unbound full outer querie

Hi

I have several survey types. Each surveytype has a number of questions with a number of predefined answers

If a survey is made I would like to generate the questions on basis of the surveyType so only the answers need to be filled out. In MS Access I can do this with an outer join. I do not know how to make this work in PowerApps

 

As an example with 2 tables I would like to have the following:

Table1 With values 1, 2,3

Table2 with values A, B

Then with this SQL Statement in MS Access 

SELECT Tabel1.Tab1Value, Tabel2.Tab2Value FROM Tabel1, Tabel2;

 

It will generate the Cartesian product this which I can store in a table

1, A

2, A

3, A

1, B

2, B

3, B

Can Someone help me how to do this in PowerApss since SQL statements are not available?

Thx in advance

Marc van der Lee

1 ACCEPTED SOLUTION

Accepted Solutions
EddieE
Super User
Super User

@marcvanderlee 

This should do it

Clear(cartPROD);
ForAll(
   Table1,
      ForAll(
         Table2,
         Collect(cartPROD,{newCol1: Tab2Value, newCol2: Tab2Value}
         )
      )
)

note: ForAll will only collect a max of 2000 records

 

Method reproduced from here

https://thepoweraddict.com/implementing-cartesian-or-cross-join/

 

 

 

View solution in original post

5 REPLIES 5
EddieE
Super User
Super User

@marcvanderlee 

This should do it

Clear(cartPROD);
ForAll(
   Table1,
      ForAll(
         Table2,
         Collect(cartPROD,{newCol1: Tab2Value, newCol2: Tab2Value}
         )
      )
)

note: ForAll will only collect a max of 2000 records

 

Method reproduced from here

https://thepoweraddict.com/implementing-cartesian-or-cross-join/

 

 

 

marcvanderlee1
Regular Visitor

Thanks very much. It works fine

v-yujincui-msft
Community Support
Community Support

Hi @marcvanderlee ,

 

It seems that your problem has been solved.
Maybe you could consider marking @EddieE  's answer as a 'Solution' so that it will be useful for others and close the case.

 

Best Regards,
Charlie Choi

Hi
I'am new on the platform. Ik thanked Eddie and give a thumb. I do not know how to close the request. Can you help me out?

 

Gr marc

Drrickryp
Super User
Super User

@marcvanderlee 

There ya go.

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 (2,907)