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

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • In Progress

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)


-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?

Photo of Devin


  • 362 Points 250 badge 2x thumb
  • brain damaged

Posted 1 year ago

  • 0
  • 1
Photo of Esther


  • 702 Points 500 badge 2x thumb
I do not understand the suggestion you make " 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
Photo of Devin


  • 362 Points 250 badge 2x thumb

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!

Photo of Matthew Neil

Matthew Neil

  • 31,698 Points 20k badge 2x thumb
As continuation to this and your other post....

I do think option E is your best bet from what I can tell. 
But, its hard to tell without know the complexity of those actions, how much automation you need vs how much management you want.

There are a few layers that you could add now that might help with the regional assignments and help the workflow.

Also there are several tricks to make the 'joined' tables more manageable and less duplicated work.

have you have any good advice from your account manager at QB?  Maybe reach out to a partner or myself to talk through the options.  Most people do a small 'consultation' for free to help layout all the options.

Matthew Neil - Product Specialist