Results from multiple tables in one single table

[polarpro]

Hey,

I have two related tables that both can be searched. In the results table I'd like to show the found records of both tables in one single list.

As far as I know, I need to have a third table for this; a table that gathers the results from table A and table B. However, I am stuck on that and am looking for a hint where to start. Any idea?

Thanks,

Mike

[LaRetta]

In the results table I'd like to show the found records of both tables in one single list.

This usually indicates that the records from both tables should have been in the same table to begin with, Mike. But one way you can handle it is to import only the primary key (of the found set) from both tables into a third table and display your results or report from there. You must be sure the keys cannot be the same, either by adding a letter to the beginning or by using two separate fields.

Then you will create relationships back to their tables. All reports and results will be shown from this new third temp table by simply placing the fields from both tables on it; or use two portals side by side; or even create a calculation in the third table which displays the name, dependent upon which relationship is not empty.

Much simpler to combine the tables. This won't be the first time you will hit such a roadblock.

LaRetta

[polarpro]

Hey LaRetta,

Thank you for your answer!

Here is the reason why I have two different tables:

One table contains 'topics', and the other one 'resources'. One topic can have many resources (links to stored files, URLs and so on).

If you search for a topic you get as a result a list of topic titles, clicking on such a title opens a new window with the topic and all related resources in a portal. From there you can open the resources. However, I thought it'd be convenient to show matching resources directly in such a list, too, so that the user can open them right away.

I hope this is not too confusing?

Mike