5 Replies Latest reply on Feb 24, 2014 11:29 AM by pmconaway

    Cross Join - Data Entry




      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!




        • 1. Re: Cross Join - Data Entry

          Initial population could occur by joining the tables, performing a search on your criteria, exporting the results, and importing into your new table.


          Subsequently, you can have triggers to write / delete join records individually as dictated by the Active Listing Tbl changes.

          • 2. Re: Cross Join - Data Entry

            This was our first version, but I could not limit the result set for the export. I have some custom functions which I couldn't implement in the search request. Maybe a syntax issue. .


            Currently I do the calculation in stages to save CPU time.


            For example:


                 1) A subtraction & multiplication calculation (for the entire cross-join).

                 2) If the frist calculation is still positive I perform a orthodromic distance calculation.




                    If result is less than my limit I am creating the result record.


            Some calculations have 8-10 steps. This is the reason why I do it in steps via a loop script.

            • 3. Re: Cross Join - Data Entry

              Mllm001, what version of FMP are you using. What using ExecuteSQL function/ Calculation as part of the process? Based on your description here is what I would do. Write a query that will ruturn the records that meet your criteria. "(1)" and then use the result set to run the second calcuation. I haven't tried to write complex ExecuteSQL queries as the FMP implementation isn't the easiest to work with. I've developed other queries where the "where clause" is a sub query. This sounds like a place where this might work. I also tend to develop this sort of thing peacemeal, getting one step to work first (intermediate result) then working on the next step. In your example I would return a list of records with just the information I would need to complete the calculation in the second step.  I hope this helps.



              • 4. Re: Cross Join - Data Entry

                Thank you for input. Would you store the query result in a global container / text field and re-importing it? I looked into the SQL function to update data, but learned the lesson that it is only for SELECT statements, so I guess I have to add the records in a seperate step. Please correct me, if I am work.



                • 5. Re: Cross Join - Data Entry

                  Michael, you are correct but it how you process the results that can make this work. You should store the results in a global field or global variable. I would porbably store it in a global variable. And then process the delimited text string that the executesql function returns.