Unbound full outer querie

[marcvanderlee]

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

[EddieE · Accepted solution]

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

Thanks very much. It works fine

[v-yujincui-msft]

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

[marcvanderlee1]

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]

@marcvanderlee 

There ya go.