There are various ways of doing that.
1. Merging the data into a new FM table via imports. The downside is that it isn't 'live' and depends on the frequency of the merges as well as holding duplicate data.
2. Doing a virtual merge into a virtual list (Google 'Todd Geist Virtual List' to learn more about Virtual Lists) by using 3 ExecuteSQL statements (one for each table) and appending the results into a global variable that is then parsed by the virtual list fields.
I'm sure there are a few more ideas out there....
Thank you, radeffect.
I opted for Virtual list option. Now I have a new problem that I am creating a relationship with the unstored calculation.
If the data is static (like sales figures) I would import them in a table, then you can do anything with it you want.
I used to import the summary of sales data into a new table (cause it doesn't change after the month is over). No need to do any summarizing then when the boss views reports. Really fast :-)
A live update is preferred than Importing each time for X amount of sources.
I would modify the virtual list table then to include one regular field where you save the key. This could be done by a stored calculation or script step that saves the "unstored" to a regular field.
We usually use virtual lists with portals and add filters to show what we need (e.g. date ranges, status).
Combined with the SELECTOR-CONNECTOR model this works very nicely for most cases.
Take into account that Executesql is not very efficient and if you are pulling data from large tables on a client this can slow things down considerably. One way to improve speed in such a scenario would be to run he SQL statements on the server (PSOS) and just pull the result back to the client.