laurend

Comment - I agree with you on the Dashboard concept. I put one into my last solution, and the performance is terrible. I am not familiar with a "utility" chart and didn't find much searching for that online. Is it just a dummy table with just the BuildingID as a global field? Do you then base your layout on that instead? How exactly would that differ from using a Dashboard? My dashboard table has several global fields and several calculation fields as well as the usual Modification/Timestamp stuff. Thank you again for your help. I am nearly done updating my super table and adjusting the layouts accordingly.

Comment - Thank you again. I think I knew deep down that doing the supertype/subtype table structure would be the best way to go, but I was daunted by the change to the backend without an expert's opinion. In reality, I don't have that many records, and since I already have the subtype tables (e.g. air, water), it seems simpler to create the super permit table and migrate a handful of fields per permit type instead of migrating all of it. This just makes total sense for doing a Dashboard item too. For the Dashboard, would you base it on the super permits table and then use a table layout with a script trigger to allow the users to filter the list by building? Or, would you create a portal based upon the Dashboard table (no records in that table) and add the super permits table as a portal?

Comment - Thank you for providing your input. I almost designed the database that way, but the Air table alone has another 50 fields that are unique to the Air permit only. This is the case, albeit at a smaller number, for each of the other permit types too. I was concerned about having over 300 fields in one permit table whereby on a few of the fields would be used per record. The building table is linked by the ID and not the building name. Given this additional information, would you still recommend one massive table to store all permit data?

I created a database to track permits by building. Each permit type is stored in its own table. The only link between tables is the Building Name which is the physical location where the permit exists. Tables: v_Buildings (fields include building name, ID, building status, address, city, state, zipcode) Air (ID, Building Name, Expiration Date, Days_to_Expiration, Type, etc...) Water Food Elevator ...9 permit types in all, and I will add more in the future Each of these tables has the same fields in common: BuildingName PermitExpirationDate PermitOwner Days_to_Expiration I created a Dashboard layout with the idea that users could select a building name from a drop-down menu and then see all of the permits for that particular building along with their expiration date and the permit owner's name. I am just baffled as to how to do this across multiple tables. I have looked at SQL, Join tables, portals etc...., and I cannot figure out how to aggregate all of this information into one view for users. I cannot even seem to figure out what table should be used for the Dashboard layout. I would love to hear from the community the best and hopefully scalable approach for designing this layout. Thanks in advance from a novice user.