Can I have a text entry customer-lookup and a related-customer drop-down in the same table regarding the same relationship?

  • 0
  • 1
  • Question
  • Updated 6 months ago
  • Answered
One set of permissions does not allow viewing of all records. So the user must enter an account number to pull of the data. However, Admins are permitted to see all records, and if using a form, I would like them to have a drop-down option or even the list option to locate a customer.
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb

Posted 6 months ago

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

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
It sounds like you want users to be able to directly key the account number if they know it, or else look it up using a drop down list (if they have Admin Permission level) 

No problem,

Make a formula numeric field for the reference field on the relationship as the final relationship.

So you will have your existing relationship, but do not pull any lookup fields (nor make any summary fields) from that Relationship.

Then make a new relatioonship and for tyhe rerfence field on the right hand side use a fomrmula field like

IF([Account # (direct entry)] >0, [Account # (direct entry)], [Related Account #])

ie have a formula to use either the direct keyed account or the lookup account from the drop down list.
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb
"Make a formula numeric field for the reference field on the relationship as the final relationship."
Am I creating a second relationship?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Yes a 2nd relationship which will be the one that you will use for any lookup fields or summary fields.
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb
Bare with me... The new field should be created on the Parent or Child table, and I enter the code after the relationship is created?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
no problem, I often rush with my answers so they are not as detailed as they could be.

The new field will be on the child side.  Make the new formula field first field first and it would look something like this

IF([Account # (direct entry)] >0, [Account # (direct entry)], [Related Account #])

but I don't know what your fields names actually are or if that field account # field is numeric or text .....


Then make the 2nd relationship, but rather than letting Quick Base make a new field for you called [Related Account], use the formula field.
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb
So I changed it to a text formula field, and I must not be assigning the correct fields to the formula, or making the correct selects for the Proxy field, because all that is appearing is the field name.
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb
It's actually just mirroring what I enter in the 'Enter Account #' field rather than giving me an option to search through accounts.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
You will have two fields on the form.

The first field will be the direct entry of the account number.

The 2nd field on the form will be the usual field with the drop down list just like you would have normally done that.  So the user can decide which to use or you may chose to control that with form rules.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Do you have two relationships?

Can you tell me the name of the field for the reference field on the right side of each of the two relationships, and tell me the formula for the one that is the formula field?
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb
Account # For Relationship and Lookup Customer
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
So were are we at now?  Is the drop down field on the form?  Is the direct entry field on the form?  Is the Relationship based on he formula field pulling in data?
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb
The 'Lookup Customer' was not displaying as a drop-down. Not sure why. 
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb
Was I supposed to create 2 new fields in this scenario or just the one? I keep re-reading to see what I missed.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
There will be a regular field to choose the drop down choice as with any normal relationship.

There will be a new field which is just a pure data entry field where the use can enter an account # manually.

Then there will be a 3rd field which calculated which one of those two fields to use.  That last field will also be used in the relationship - a 2nd relationship to the Accounts table.  All you lookups and summary fields will come off that last relationship.

Please post the formula for 2nd relationship.
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb
When I created the first data entry field, you gave me a step-by-step. Now a user can enter an account # and their information pulls up. All I need is another step by step on how to add to that (1... 2...). I see all of the ingredients, but the recipe is not clear to me.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Can you tell me your current formula for the field 
[Account # for relationship]
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb
[Enter Account #]
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
So try changing it to

IF([Enter Account #] >0, [Enter Account #], [Lookup Customer])
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb
That's where I went wrong. I was putting that in the Lookup Customer field. What should go in there?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Do you gave a normal clean relationship with a drop down list of accounts?  if so, what is the field for the account # chosen that way.
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb
No. That is why I am creating this relationship, because I needed a drop-down option.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
1. You need to have a traditional drop down relationship.

2. You need to have a field to enter the account number directly.

3. You need to have a relationship where the reference field on the right hand side is a formula field which will calculate to either the [Enter Account#] value or the value from the drop down list. 
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb
So all I needed to do was create a normal relationship, and then update the code in my entry field to pull information from one source or the other?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
YES!  I can see the glare from the 200 watt light bulb that just lit up for you .. all the way up to here in Canada.
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb
Hahaha... I had to put on some shades! I finally got around to finishing it up, and updating the rules to make sure everything works, and it does! Thanks, Mark!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 50,178 Points 50k badge 2x thumb
I'm glad we got there!  I know its a journey at the beginning to have things "click" in your head.  I was there once too.