Cross Join - Data Entry

Discussion created by mllm001 on Feb 23, 2014
Latest reply on Feb 24, 2014 by pmconaway



I am looking for a more efficient way - than script looping - to create a conditional Cartesian product (cross join) result table.


In detail:

I am cross joing a table with active listings and. historical listings. Filemaker should create only records in a result table which will meet certain conditions.

For example: (Price Active - Price History) > $15,000. If conditions = true than FM shall auto create a new record in a result table and store the Active Listing ID, Historical Listing ID (to be able to link it back to the source tables) and the calculated results.



Nice to have: If something change in the Active Listing Tbl it should update the result table as well.


If I dont limit the join result via conditions my output would be around 400 million records. With the current loop I can reduce it to 150 to 200k records. A portal works ok for individual comparison, but we are looking at the data in total first and drill down later. The total view is the performance issue.


I am very happy for any feedback!