How to get multiple results into one field
I have a 'Customers' table with a Portal on it to a related Orders table. On CustomerID can have many orders.
I then have a Products table for each order. One OrderID can have many products.
In the Orders portal I would like to display the Products for a single order all on a single row. For example if orderID 666 has products AB, BB and CX the portal would show (amongst other things)
666 AB BB CX
How can I do this?
I have tried to make a calculation field in the orders table that uses the ExecuteSQL fuction to return the results on a single line. It works OK on my test database with a few thousand records but is unusable in the real world as the Products table has over 1 million records and the function hangs for half an hour before I kill the process.
Is there another way to achieve this?