Discussions

Expand all | Collapse all

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

Kelly Kelly05-31-2018 14:06

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

    Posted 05-30-2018 16:48
    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.


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

    Posted 05-30-2018 17:25
    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.


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

    Posted 05-30-2018 18:54
    Make a formula numeric field for the reference field on the relationship as the final relationship."
    Am I creating a second relationship?


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

    Posted 05-30-2018 18:57
    Yes a 2nd relationship which will be the one that you will use for any lookup fields or summary fields.


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

    Posted 05-30-2018 19:06
    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?


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

    Posted 05-30-2018 19:10
    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.


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

    Posted 05-30-2018 19:28
    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.


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

    Posted 05-30-2018 19:32
    It's actually just mirroring what I enter in the 'Enter Account #' field rather than giving me an option to search through accounts.


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

    Posted 05-30-2018 20:38
    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.


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

    Posted 05-30-2018 20:52
    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?


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

    Posted 05-31-2018 00:36
    Account # For Relationship and Lookup Customer


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

    Posted 05-31-2018 02:20
    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?


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

    Posted 05-31-2018 02:26
    The 'Lookup Customer' was not displaying as a drop-down. Not sure why. 


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

    Posted 05-31-2018 02:46
    Was I supposed to create 2 new fields in this scenario or just the one? I keep re-reading to see what I missed.


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

    Posted 05-31-2018 09:56
    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.


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

    Posted 05-31-2018 12:58
    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.


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

    Posted 05-31-2018 13:59
    Can you tell me your current formula for the field 
    [Account # for relationship]


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

    Posted 05-31-2018 14:06
    [Enter Account #]


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

    Posted 05-31-2018 14:19
    So try changing it to

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


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

    Posted 05-31-2018 14:37
    That's where I went wrong. I was putting that in the Lookup Customer field. What should go in there?


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

    Posted 05-31-2018 14:41
    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.


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

    Posted 05-31-2018 14:44
    No. That is why I am creating this relationship, because I needed a drop-down option.


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

    Posted 05-31-2018 14:49
    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. 


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

    Posted 05-31-2018 15:01
    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?


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

    Posted 05-31-2018 15:03
    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.


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

    Posted 06-01-2018 03:01
    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!


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

    Posted 06-01-2018 03:03
    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.