Forum Discussion

HansHamm's avatar
HansHamm
Qrew Assistant Captain
6 years ago

Relationship Issue

I have a heck of a time getting my mind around this relationship, fumbling around every time with it.

In the Parent Table – Resets, I use a text-formula field in the Resets Table to create the ResetID#, this ResetID# is then passed to the Child Table – Travel, when a new travel record is created for a given reset. A reset can have one travel record or innumerable travels.

Now what I want to do is reverse the order, where the Travel becomes the Parent Table and Resets becomes the Child Table. What I need is to pull back to the Reset Table the associate that is the assigned Lead for a Reset (Reset Lead). Of course, the common denominator is the ResetID# in both tables. The ResetID# is not the key field in either table. So, how can I make this field a relate-able field in both tables?

​

------------------------------
Hans Hamm
------------------------------
Relationships
  • DonLarson's avatar
    DonLarson
    Qrew Elite
    6 years ago
    Hans,

    It is hard to follow your description.  Is this what you are talking about?



    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------
    • HansHamm's avatar
      HansHamm
      Qrew Assistant Captain
      6 years ago
      Don I believe it is... I do have one question in regards to your diagram... what is the FT after ResetID#?
      I'm sorry QB and database design is not my primary responsibility and I know just enough to be dangerous...

      ------------------------------
      Hans Hamm
      ------------------------------
      • DonLarson's avatar
        DonLarson
        Qrew Elite
        6 years ago
        Hans,

        You will be more dangerous and much more valuable to the company soon.

        The FT stands for Formula Text.   I always short hand the fields so that I know what is real data and what is meta data that can be changed in moments notice across the entire database.

        Since we have the diagram right, I assume you have made the black relationships in the application and it is the one in red that you need.  It is very easy to make that with a caveat to follow.

        Go to the Travels Table
        Click Settings
        Click Relationship 
        Add a Relationship
        Travels have many Resets

        This is the easy part, now for the caveat.   None of the Reset records will be related to a Travels record automatically.     It is a brand new field and can be set by a User.

        I assume you are really trying to get a Reset record to be related to a specific Travel record? 
        Do you want the Reset to know the Lead from the very first Travel record?
        Do you want to know the Lead from every Travel record?

        Assume there are 25 Travel records for a single Reset.   The Employee on each Travel Record is different.  Which Employee do you want the Reset to know?   In the Reset record you can put a report now that will show all the Travel Records and the Lead/Employee.

        There may be a better way to do this if you are trying to accomplish something else.

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