3 table relationship???

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered
The Route header is the parent of route details

At the moment I am allowed to enter details within the header as shown by the picture "Route header Details".

Each route has multiple stores within it. What I need is for the store table to implement the store numbers into route details so when we type in the data under Route headers the stores will be listed in the grid shown in picture "Route header Details".

Pics:

Route details = Details table

Route header Details = Edit mode in Route header

Store = Store number # table

Table to table relationsips = relationship at the momment

Route header to route details relationship = how route header is set up to route details

 

Photo of Andres

Andres

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of Vincent

Vincent

  • 10 Points
I'm not sure if i have the full picture, Is the route header static or will there be a new route, say, every week?

Maybe just have a relationship setup so that Stores have many RouteHeaders and RouteHeaders have many RouteDetails. Then just simply bring the store numbers down to the route details?

I noticed in your screenshot that there is also a "Routes" table?
Photo of Andres

Andres

  • 0 Points
So the way it works is the Route header is the where everything is done. There is 48 routes within each route there is about 18 to 25 stores. That "Route header Details" picture is the grid view for the detail table that is in the route header now thanks to the relationship between them. In the store table I have all the stores # (unique key field) with the route they are suppose to belong to.

What I need it to do is when I go to the Route header table in the Detail Grid for the Store # to show up in the Store # column so it does not have to be typed out each time. The route # and Store # stay the same for 6 months than it must be rerouted which is why the stores are in a separate table. The route table just has the Route # 1-48 and is being transferred to the route header. Was only able to put 5 pics don't think i can put more.
Photo of Vincent

Vincent

  • 10 Points
Relationship tables can be complicated and it becomes more difficult if you are not careful on how you word things. Saying "48 routes within each route" is confusing since you are saying a route is the parent of itself and then later go on to say that a route is template. If "Routes" (which i'm inferring as Route Headers) have many Stores, then you would not be able to pull up a store up to a "route" since there are many stores. You could do a summary field that gives max store number or something like that.
Photo of QuickBasePros (Laura Thacker)

QuickBasePros (Laura Thacker), Champion

  • 5,224 Points 5k badge 2x thumb
In order to "populate" your Route Details table (where your Store #'s etc. are); you have to create the records in there, which are then "Related" to your Route Header table via the [Related Header] field.

You have the options natively, to enter these Route Details one-by-one (Add a Route Details, click Save & Add Another repeatedly); or to populate them via Grid Edit in the embedded report shown in your Route Header Form Layout.

You have not provided any details about a "Store Table" (which presumably would be another table to act as a Parent to the Route Details table).

Route Header < Route Details > Stores Table

If you build a Stores table, then make that Stores Table a Parent to the Route Details table; that will be how you join your Stores with Routes (Route Header) using the Route Details "join" table.