Forum Discussion

DavidBerndt1's avatar
DavidBerndt1
Qrew Member
3 years ago

unrelated relationships

In our app we have the primary set of tables and then some that I would call "supporting" tables.  The issue is at the lowest level, I may have all the necessary data to build a relationship but it doesn't really exist all the way to the top so I can ensure I'm matching the right records.  Here I go to try to explain it a bit
The Site Authorization has the Provider, Study and Site and this Auth Date
The Event has the Study, Site, and Provider and the Event Date

We need to develop a report at the event level that provides the number of days between the Authorization Date and the Event Date.  I cannot see how to build the relationships that can get Auth date up the chain and back down to the Event table.  A "join" would be pretty simple but since that isn't an option - any suggestions?​

------------------------------
David Berndt
------------------------------
Relationships

7 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    3 years ago
    One site has many Site Authorizations.  Therefore there are many Authorization dates.  How will we know which of those dates to use.  Oldest or  Newest or a particular one?  

    If we know that then we can just do a summary field where one site has many site authorizations and summarize either the minimum or maximum authorization date up to sites and then it's a simple look up back down to events, right?

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • DavidBerndt1's avatar
      DavidBerndt1
      Qrew Member
      3 years ago
      It is a particular one.  As the site authorization is also tied to the provider, each provider would only only have 1 authorization per study training/site combination and only one provider is associated with a particular event.

      ------------------------------
      David Berndt
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        3 years ago
        I am not sure if there's a more elegant way but you could make a text field on the site authorization record which combined the provider name with the authorization date. Then float up that combine text field up to the Sites table, then look it up back down to events. The event knows the provider and then you would have a formula that would purse out the possible authorization date provider combinations to find the one that had the right provider and turn that text format a date back into a real date field.

        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------