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.
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.
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.
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.
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, 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.