2 Replies Latest reply on Sep 12, 2013 5:14 PM by fireballprinting

    Combining Multiple Tables



      Combining Multiple Tables



           I have the following tables:






           There's a 1 to many relationship between clients and jobs, a 1 to many relationship between jobs and lines, and a 1 to many relationship between jobs and tasks. Shipments is populated through a script, and combines both lines and tasks (if they involve shipping).

           I'd like to be able to generate a report for the day's shipments. It would also need to combine lines and tasks, but I don't want to base the report on the shipments table, because I'd like to combine multiple items going to the same address into one record, so they show up together on the report. I was considering creating a table based on due date and address, related to the shipments table, but it felt a little clunky; I was worried that the shipping address would update without updating the corresponding record in the related table, unless I added a ton of scripting. Just wondering if there's a more elegant solution out there?

        • 1. Re: Combining Multiple Tables


               But what relationship for Shipments? How does that work? How are they "combined"?


                    Shipments is populated through a script, and combines both lines and tasks (if they involve shipping).

               My best guess is that you have Shipments related to other occurrences of Tasks and Lines and that you "combine" them by placing a portal to each on the same layout.

               Seems like Lines and Tasks should be records in the same table, but with a field that identifies each record as either a "task" or a "line". Portals to such a combined table can be "filtered" to show only tasks or only lines. Then your report can be based on that combined table.

          • 2. Re: Combining Multiple Tables

                 Hi Phil,

                 Thanks for your response. Right now I have shipments related to clients, with other table occurrences related to lines, tasks, and jobs. I have a script that uses set field to generate a related record from lines to shipments and another one from tasks to shipments. Does that setup make sense? I also have the same setup for deliveries.

                 And thanks for your suggestion about making lines and tasks one table. I think that totally makes sense, I'm not sure why that didn't occur to me before!