Skip to main content
Question

Using Formulas, Actions or Other Means to Auto-relate Records or Facilitate Field Changes

  • April 4, 2018
  • 0 replies
  • 52 views

Wracking my brain...

I have a "Resources" table, and a "Properties" table. They have a many-to-many intermediary table "Resource Assignments" (child to both) which relates "Resources" and "Properties". I have a third table that is "Incidents" (child) related to "Properties". See diagram for clarity on the end goal.

I have been wracking my brain trying to solve how to minimize relationship/data maintenance by relating "Resources" to "Incidents" (or adding an auto-filled field as filter).

1. I can't relate Resources-Incidents (employees come and go)

2. I can't relate Resources-Properties (increased relationship management, and it's be nicer to report on any Resource's Incidents as child to Properties)

Questions/Brainstorming:

-Is it possible to use a formula across tables or applications to achieve some automation?

-Is it possible to use Actions to achieve this?

-Could I create a new parent table to Properties and Resources that could auto-fill set fields on which reporting filtering could be achieved?

    This topic has been closed for replies.

    I do not understand the suggestion you make "...to minimize relationship/data maintenance by relating "Resources" to "Incidents" (or adding an auto-filled field as filter).." probably I do not get the whole picture.

    Can you share a screen shot of the current relationships?, you can get it selecting "home" in the app you talk about , the select "App management" an then the third option  "show relationship diagram" .

    Ones the relationships are correctly created you can achieve all the questions you have.

    I suppose you already used the help in QB by it gives you some advice about how to set it up correctly a many to many relationship.

    here one link

    https://help.quickbase.com/user-assistance/?_ga=1.50342057.1068101511.1485456111#many_to_many_relationship.html
    • DevinDevin
    • Author
    • Registered
    • April 4, 2018

    The issue I have is that no relationship arrangements fully satisfy what we're attempting to achieve. Current scenarios I've attempted or researched:

    Option A: Create Resources (Parent)-Properties (Child) relationship limits only one resource a la one-to-many. We have two or three resources per property which would need to be a parent.

    Option B: Create a field or category(ies) in the Properties tables on which to filter related child records (e.g., Incidents) when reporting. This would require manual changes should the Resources and Assignments records change. Too much data management. Also, use of formulas to automate is not feasible from the research I've done (e.g., cannot use across tables upstream).

    Option C: Scrap the Properties-Incidents relationship and/or add an Property Assignments(Parent)-Incidents(Child) relationship. This would require too much management of data for the multiple Property Assignments.

    Option D: Create new Staff Regions Tables. They would be related: Properties(Child)-Staff Regions(Parent to Properties/Child to Resources)-Resources(Parent). This requires additional data management. E.g: if a Property Assignment changes, the Staff Regions table(s) would also have to be updated (duplicative).

    Option E: Similar to "D", create Staff Regions table as a parent to Properties, child to Property Assignments. This makes a circular relationship (is this a problem?):

    -Properties(Parent to Property Assignments/Child to Staff Regions)

    -Staff Regions(Parent to Properties/Child to Property Assignments

    -Property Assignments(Parent to Staff Regions/Child to Properties)

    Option F: Replace Property Assignments table with a table for each Assignment (e.g., Accountant, Property Manager etc.). Searching assignments by property is hindered by this setup, and would only be visible in the Properties table.

    Option G: Reverse the relationship of Properties and Assignments. Not possible given the one-to-many nature of the relationship.

    Below is the best I could come up with brainstorming and thinking through each scenario!

    Terms & ConditionsAccessibility statement