Forum Discussion

DavidHurwitz's avatar
DavidHurwitz
Qrew Member
5 years ago

Need help with solution to a complex table relationship

Hi, I need help figuring out how to structure part of my application. My company transports art. So here is the scenarios I am trying to figure out:

I have one table called Clients which has all the information on my clients as well as their locations.

I have another table called Artworks which has all of the information on the pieces of art themselves (size, titles, packing material etc)

I created a third table called Jobs which will contain data on pickup and delivery dates, fees, additional services on the transportation like install and packing etc. This table has three places in it for info all pulled from different records in the clients table:
1. Client who requested job
2. Client where pickup will occur
3. Client where delivery will occur.

The information for all three of these clients is in my Clients table. It's also not uncommon for the same client to be the one who requested the job as well as the one where the delivery or pickup occurs.

So what I need to do is:
1- Be able to populate a single entry in my jobs table with information from three different clients.
2- Be able to add multiple artworks from my artworks table to the job.

What would be the best way to do this? A many to many relationship between the clients and job with a new in between sheet called Job Aggregation or something like that and then a many to one relationship from Artworks to the new Job Aggregation table? Am I thinking of this the right way?

Thanks for your help and look forward to hearing back.

------------------------------
David Hurwitz
------------------------------
Relationships

2 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago

    no problem,
    Let's deal with Clients first.

    You will need a relationship for one client has many Jobs to select the Requesting Client. Rename the Related field to be called [Related Requesting Client].  Lookup the client name and call it [Requesting Client Name].

    Set the Proxy field to be Requesting Client Name by editing the field for [Related Requesting Client] and setting the Proxy.  Proxy field is the one that you will use on all forms and reports.

    Did that process two more times for the other two client fields. If you copy the field [Related Requesting Client] not only will you get that field but you will get the relationship created again as well.

    Sure all of your fields have appropriate names so you don't get confused between your three set of Client Relationships and their field names.

    You have a Jobs table now, so the next table that you need is Job Artwork Lines.  Make a Relationship where one Job has many Job Artwork Lines. The Job Artwork lines table is where you are going to identify the pieces of art to be moved. 

    Now, it's not clear from your post of you intend to have a master table of Artworks where you would list the Artworks.  Perhaps you are an Art Rental company and the inventory is moved around as your clients get bored of looking at the Mona Lisa all day (same old - same old) and decide to with her out for "Persistence of Time and Memory by Dali.

    But if you will have that Master Art SKU table, then One Artwork will have many Job Artwork Lines.

    The locations of the Artwork at any time can be known.  If you do a lookup of the Job Movement date from the Jobs table down to Job Artwork Lines, then each line knows when it was moved.  You can do a Summary maximum of the date up to Artworks to know when it was last moved subject to the filter that the date is not in the future.  Then do a Combined Text Summary field for say the Delivery Client and that is where the Artwork is now . 



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • DonLarson's avatar
      DonLarson
      Qrew Elite
      5 years ago
      David,

      Expanding just a bit on Mark's here is a drawing for you with a two questions.


      The first question is the one Mark mentioned.  Who own's the Artwork?   If the pieces are the property of the client, then a relationship between Clients and Artwork will pay you dividends later when you need to report on who's Artwork is where.

      The second question is similar and that is the Locations.   If these are all public art galleries then a stand alone Locations table makes sense.  If these are all corporate clients and you are moving things between their buildings, then filling out the Jobs form will be much simpler if your list of Locations is limited to the ones for the Client.


      ------------------------------
      Don Larson
      Paasporter
      Westlake OH
      ------------------------------