URL formula to open different form from different table

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I have two forms created under the table named "TASKS". Form names are PCAR TASKS and RMA TASKS

I have two additional tables. First named "PCAR" second named "RMA"

Within the PCAR table, I have a form which has a relationship with TASKS. so there are multiple tasks for one PCAR project.

Within the RMA table, I have a form which has a relationship with TASKS. so there are multiple tasks for one RMA project.

My issue I am having is I want the PCAR table to reference only the PCAR TASKS form and the RMA table to only reference the RMA TASKS form.

Right now, if I click "add new task" on either PCAR or RMA, I am sent to the PCAR TASKS form. How do I set the PCAR form to only open the PCAR TASKS form & RMA form to open only the RMA TASKS form?


I have added a flow chart of what I am after. I need the relationships between the tasks and the forms to be customized.

RMA form should have a relationship with RMA tasks...PCAR form should have a relationship with PCAR tasks. However when I setup the relationship, I can only do RMA has many TASKS & PCAR has many TASKS. there is not option to select "RMA has many RMA TASKS"...Therefore it is defaulting to the PCAR Tasks for both.

Thank you!!!

Photo of mkosek

mkosek

  • 918 Points 500 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 4,516 Points 4k badge 2x thumb
I think what you are needing is two relationships (one for each parent table to the one Tasks table):

RMAs < Tasks (Related RMA)

PCARs < Tasks (Related PCAR)

1. When ADDING a Task from either of the two tables; you want to encode the dfid of the correct Form

URLRoot() & "db/" & [_DBID_TASKS] & "?a=nwr&dfid=2 (or 12)"

This means that when you add a new Task from the RMA or PCAR table that the correct Form to populate the task will display.

2. You will need a 3rd Form, which is the combination of the two Forms, with both relationships on it.  Your two relationships can be "hidden" in a Form Enforcer section so that the User cannot change it.  You will set this 3rd Form so that the fields from RMA and PCAR are both on the form (in separate sections) and use Form Rules to hide/show the section based on the relationship that is populated.  ie.

When [Related RMA] is >0

  hide PCAR SECTION

and

When [Related PCAR] >0

  hide RMA SECTION

3.  Set this 3rd Form with both sets of information as the EDIT and VIEW  forms for the Tasks table for all Roles.  This means that whoever is viewing or editing a Task will see the right "set" of lookup fields

4.  You should then remove the ability for a User to be able to add a Task from the grey-button at the top by turning it off in all the roles, forcing the Users to add Tasks from the RMA or PCAR tables.

5.  Embedded report links should then display only records in the RMA and PCAR tables which have that matching relationship populated.

6.  For reports; you will need to get creative with some formula fields.  You will need formula fields to display data from "either" relationship depending on which is populated; so Users don't have to have multiple fields in a report.  You can also build formula fields which identify for them what the relationship is displayed as RMA or PCAR, for example so when they display reports; they can use this as a filter or grouping mechanism.
Photo of mkosek

mkosek

  • 918 Points 500 badge 2x thumb
I instead just modified the original form to work for both tables. Not ideal but I was unable to setup two different relationships without having to create two "Tasks" tables.