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

    Combining Multiple Tables

    fireballprinting

      Title

      Combining Multiple Tables

      Post

           Hello,

           I have the following tables:

           Clients

           Jobs

           Lines

           Tasks

           Shipments

           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
          philmodjunk

               Clients----<Jobs----<Lines
                                   |
                                  ^
                              Tasks

               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
            fireballprinting

                 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!