I'm wondering your thoughts on the best way to display some data to a user. The easiest way to explain my question is to give you the scenario:
I have 3 tables: site, hardware and installation. A site can have many installed pieces of hardware, and a piece of hardware can be installed at many sites over time. In addition, the site can have many installations of the same piece of hardware (e.g. it is returned as faulty, refurbished and re-installed again).
I am trying to display all pieces of hardware a site has ever had installed, and it's current installation status (e.g. with a different site, being refurbished, etc.).
There is an added complication that not all installation records will have a linked hardware record, so I can't avoid the installation table and link straight through to the hardware table.
The difficulty is that I only want to show each piece of hardware once in the portal at the site level, and if there are many installation records for the same piece of hardware, I can't do this.
I have been trying to use ExecuteSQL to grab back a list of installation records where the hardware foreign key is distinct, and this gives me the right number of records, but no way of showing the installation info in a portal!
Any advice would be greatly appreciated. I have added my current SQL query below if that it at all helpful.
query = "SELECT DISTINCT \"_fk_hardware\" FROM installation WHERE \"_fk_site\" = ? AND \"_fk_hardware\" IS NOT NULL"
ExecuteSQL ( query ; " | " ; "" ; site::_pk_site )