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

    Cross Join - Data Entry

    mllm001

      Hello,

       

      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!

       

      Thx.

      Michael

        • 1. Re: Cross Join - Data Entry
          PalmDBS

          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
            mllm001

            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.

             

            \Delta\sigma=\arctan\left(\frac{\sqrt{\left(\cos\phi_2\sin\Delta\lambda\right)^2+\left(\cos\phi_1\sin\phi_2-\sin\phi_1\cos\phi_2\cos\Delta\lambda\right)^2}}{\sin\phi_1\sin\phi_2+\cos\phi_1\cos\phi_2\cos\Delta\lambda}\right).

             

                    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
              pmconaway

              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.

               

              Paul

              • 4. Re: Cross Join - Data Entry
                mllm001

                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.

                 

                Michael

                • 5. Re: Cross Join - Data Entry
                  pmconaway

                  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.

                   

                  Paul