I am looking for a more efficient way - than script looping - to create a conditional Cartesian product (cross join) result table.
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!