User Given Options Based on Whether Record Exists in Related Table

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

I have these tables in two connected apps:

App 1: Clients < Assignments > Associates 

  • (Assignments ties together a Client and an Associate, since one Client has several Associates working on it and vice-versa.)

App 2: Meetings < Projects < Tasks

Relationship between the apps are:

  • App 1: Clients < App 2: Meetings
  • App 2: Associates < App2: Projects

So, some confusion happens in the Projects table because a project is tied to clients in two ways.

  • A child-Project is related to a parent-Meeting, AND
  • A child-Project is related to a parent-Assignment (which has parent-Client and parent-Associate)

When Associates create a new project, I want them to only have to choose among their clients -- currently they're choosing from a list of Assignments, which lists hundreds of choices that join every Associate with a Client, because:

  • Projects > Clients < Assignments > Associates

What methods would be best to limit Associates choices of Clients for which to created new Projects?

Thank you!

Photo of Amber

Amber

  • 588 Points 500 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,664 Points 50k badge 2x thumb
I know how to do this elegantly. I will post back later. Does the associate table include the User for the Associate? It must.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,664 Points 50k badge 2x thumb
Ok. "No problem"
In App 1,

Make a field on the Associates Table called
[I am this Assiciate (=1)]

The formula will be

iF[userid] = User(), 1,0)

Lookup that field down to the Assignments Table.

Then do a Summary Maximum of that field up to the Clients Table.

If you want to make it super friendly to use, make a formula checkbox field to be checked if that Summary maximum field equals 1 and call it [am Assigned to this client]

That's basically it. When choosing a drop down list of clients set the form to use a report filters where

I am Assigned to this Client is checked.
Photo of Amber

Amber

  • 588 Points 500 badge 2x thumb
Awesome!!!

Want to make sure I understand

In what table would the formula checkbox field be?
Photo of Amber

Amber

  • 588 Points 500 badge 2x thumb
Okay, I make a user field in the Associates table.
Photo of Amber

Amber

  • 588 Points 500 badge 2x thumb
Okay, 'I am this Assoc' is a formula - number field.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,664 Points 50k badge 2x thumb
Let me know if you get stuck, or succeed!
Photo of Amber

Amber

  • 588 Points 500 badge 2x thumb
I did indeed succeed! Thank you!

I used a conditional drop-down for the list based on a report which filtered for that field not equaling zero.

The only strange thing is that it's showing as a client for me and I have no client assignments -- the client has a 'blank' assignment (no associate entered). Not quite sure why that's happening...but I'm very happy with the results, thank you!!