Selecting several addresses as drop down

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

I have a Customers table which contains *many customers*, each of them having several addresses:

Customer 1

  •      Address A
  •      Address B
  •      Address n

Customer 2

  •      Address A
  •      Address B
  •      Address n

Customer n

  •      Address A
  •      Address B
  •      Address n

The current relationships are:

Customers (each with several addresses) --> many Projects --> many Tasks

The Tasks table needs to gain access to the several addresses listed in Customers

I have added lookup fields to the T2T relationship between Customers and Projects to allow the Projects table to gain access to:

Customer Name

  • Address A
  • Address B
  • Address n

And so, I was going to repeat those lookup fields (2-hoppers) for the Tasks table as well.

And now, I look for a way to use the several addresses belonging to a specific Customer, to be used as drop-down selection, when a Task is created, as part of a Project, for that Customer.

:smile: 

Photo of Nicolas

Nicolas

  • 0 Points

Posted 3 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
No problem.

Make a relationship directly between Customer Addresses and Tasks.  One Customer Address has many Tasks.

That will work, but initially you will get a giant list of ALL addresses to choose from. :(

Ah, so can we limit the choices to just the addresses for the same Customer as the task is related to?

No problem.


Coax the field for Related Customer down to the task record if it is not already there, and be sure it's on the form.

The the magic is to set the [Related Address] field to be conditional where the related Customer for the Task equals the Related Customer of the Address.  That is a Field Property setting on [Related Address] in the tasks table.
Photo of Nicolas

Nicolas

  • 0 Points
Sorry, I did not express myself correctly.
Therefore, I was unable to understand your answer.
(totally my fault, I'm sorry)

I have edited my question now.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
I believe my answer assumed the details that you did not originally provide, but have now provided.  Hence my answer is still valid.

Get the field which has the [Related Customer] down to the tasks table in a two hopper.  I suggest that you call it [Record ID# of the Customer]

On the relationship that you will set up between tasks and addresses, the reference field on the right side will be the field called [Related Address].

Checkbox the field properties for Conditional values: The values in this field depend on a selection in another field

to be conditional that after the field [Record ID# of the Customer] is selected, only offer Address field were that matched the field [Related Customer]
Photo of Nicolas

Nicolas

  • 0 Points
I really apologise for being so slow.  I still need a little bit more of hand-holding here.

1. I have brought down (2-hopper) [Related Customer]  :relieved:

2. What is the best way to bring [Customers - Addresses] down to Tasks / best way to establish the relationship between Customers and Tasks?

a) With 2-hoppers lookups (from Customers to Projects, and then from Projects to Tasks); or
b) With a Direct Customers to Tasks *new* T2T relationship)

3. Is [Related Address] a new field I need to create?  Or an existing field I need to bring down to Tasks? (confused)

4. What kind of field should I choose (which can handle a conditional value)?

:-/
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
No problem.
One day you will emerge from your initial fog and all will be clear. This has little to d with two hoppers.

 as I said in my initial Answer

Make a relationship directly between Customer Addresses and Tasks.  One Customer Address has many Tasks.

One Address has many Tasks.

Then continue to follow the steps in my responses above.
Photo of Nicolas

Nicolas

  • 0 Points
OK -- So:

Keeping in mind that *Tasks* are currently children of *Projects* from the relationships:
*Customers* --> many *Projects* --> many *Tasks*

1. One new direct relationship between the *Customers* table, and the *Tasks* table:
*Customers* --> many *Tasks*

2. I can now expose the fields Address A, Address B, Address C, etc. from the *Customers* table to the *Tasks* table (lookup fields).  Please note that I can also expose the field "Related Customer" *directly* from that relationship ([Related Customer])

3. Separately, from the relationship *Projects* --> many *Tasks*,  I have *another* "Related Customer" field exposed ([Projects - Related Customer]) from the initial relationship *Customers* --> many *Projects* --> many *Tasks* (this one is a 2-hopper)

OK - Done.

The rest of your instructions are not clear to me:

a) Which one of the two (2) separate "Related Customer" should I place on the *Tasks* form?
- [Related Customer] from the direct relationship?  Or
- [Projects - Related Customer] from the 2-hopper?

b) You mention the field "Related Address" in your initial instructions.  This should be a new field that I should create within the *Tasks* table?  If so, what *type* of value will go in there?  "Text"?  "Address"?  "Numeric"?  And moreover, how do I set that field to be "conditional"?  I do not see a checkbox that says "conditional" when creating a new field...

Hence my overall confusion --- Sorry again :(
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
I may have misunderstood your data structure. Are you telling me that the addresses are actually on individual fields in your customer table?

I had assumed that you had a relationship where one customer had various addresses.

 If in fact the addresses are just fields on the customer table then never mind the direct relationship between customers and Tassin simply to hop of those fields down to the task table.

If you need some way to select addresses down on the task table, then you will need to make a formula where perhaps the user chooses a multiple choice option is to choose Address one, address to our address three and then a formula calculates the correct address that was chosen.
Photo of Nicolas

Nicolas

  • 0 Points
"addresses are actually on individual fields in your customer table" ----

Yes sir.  Addresses A, B, C, etc.  are different fields within the *Customers* table.

You are saying "a formula where the user chooses a multiple choice option"

So a "Formula" field within the *Tasks* table.  OK.
Any advice as per where I could get additional info?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
I will answer the question reluctantly.  Unless you have some unique situation where customers always have three addresses, my real suggestion is to restructure you app so as to have the addresses be children of the customer.

In fact, in your original post, you referred to referred to Address n, implying that there were an unknown number of addresses for each customer.

But if for some reason you want to stick with your structure, then I suggest showing all 3 Addresses in edit or add mode down on the task record.

Then have a multiple choice field [Select Address] with choices
1
2
3

Then a formula for [Task Address]

IF(
[Seiect Address] = 1, [Address 1],
[Select Address] = 2, [Address 2],
[Select Address] = 3, [Address 3])
Photo of Nicolas

Nicolas

  • 0 Points
You know how a specific field may be shared ("Values from this field may be a source for dropdown lists in other apps")?  I was hoping to be able to do that with "Addresses"  :-\
Photo of Nicolas

Nicolas

  • 0 Points
I have followed your advise, and I have created a new table *Addresses* to hold all addresses for my Customers.

I have followed your instructions, and I was able to easily create the drop down you were trying to explain earlier. And for all that, I am very thankful :smile:

Now.  I have another (related) problem.

In that *Addresses* table, I have created several fields, one for each address.  I have named them "Address A, Address B, Address C, etc.   ("A, B, C" for short).  As a result, each related customer has many addresses.  I populate only what I need, meaning, if a customer has 3 separate addresses, I fill-in A, B and C.  If a customer only has one (1) address, then I only fill-in "A" and I leave B, C, D, E alone.

So far so good.

Now, on the *Tasks* table, that conditional "Related Address" field only allows me to select *ONE* "Proxy Field", whereas I need *all* addresses related to a Customer to show up in that drop-down.  Right now, I can only select *one* field (such as "A") !!

How can I get *all* address fields to be part of that selection ? (A, B, C, D, n)

Thanks again a million times :smile:
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
Each address record should only hold one address, not 5.  The whole point of a separate table for addresses is so One Customer can have many, even 100 Addresses.
Photo of Nicolas

Nicolas

  • 0 Points
I knew this was gonna be the answer to my silly question --- about an hour *after* I posted it :smile:

Thank you again.
And sorry for being such a newbie in data relationship.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
:)   no problem. let me know when you get the address selection working on the task table.
Photo of Nicolas

Nicolas

  • 0 Points
Oh it's working fine :smile:
And I have now started to use that conditionality thingy in other places as well :smile:
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
The next time will be much easier for you.