Change a related field based on another related field without hundreds of dynamic form rules

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • In Progress
For our projects, each project has a project type (field on the projects table), master project (related table) and a master project part (related table). On the intake form, the user first selects project type.

I have a dynamic form rule that when project type is equal to "Email Marketing," it sets the Related Master Project to "Email Marketing." If it is "Event Request," it sets to Related Master Project to "Event Request." These are hard-coded.

I'm having problems with the Master Project Part. Both the "Email Marketing" and "Event Request" master projects have each of our departments as Master Project Parts. So there is a "Communications" master project part associated with "Email Marketing" (Master Project Part #96) and a "Communications" master project part associated with "Event Request" (Master Project Part #156), but both named Communications.

I managed to hard-code a dynamic rule that says:
If [Department Name] is "Communications" and [Project Type] is "Email Marketing" then change [Related Master Project Part] to "#96 Communications (COMM)"

However, there are 42 departments, and each Master Project has each of the 42 under it for a total of 84 manual dynamic rules I would have to make to cover all of them.

Is there a way to set a dynamic rule where:
If [Project Type] is "Email Marketing" then change [Related Master Project] to "Email Marketing" and change [Related Master Project Part] to the value in the field [Department Name]

I get a VERY limited selection for possible fields when I choose "the value in the field..." and [Department Name] isn't one of them.

Any thoughts on how I can pull this off?
Photo of Jason


  • 152 Points 100 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of QuickBasePros (Laura Thacker)

QuickBasePros (Laura Thacker), Champion

  • 5,436 Points 5k badge 2x thumb
You might find building a formula to calculate what the selections should be.  Then write form rules to reference this dynamic formula value.  You have to remember to update the formulas when you add/remove departments.

Alternatively, think about the workflow and have Users START somewhere else.  For example, if you have a button in a table that says [Add a Project] you can encode into the related reference field values the record ID#s you need automatically.  You can then present a list of selections on a dashboard for the user to add projects from; thereby eliminating the complexity of form rules etc.

I can discuss more offline if you think this might be a direction to go in.