Forum Discussion

BobThomas1's avatar
BobThomas1
Qrew Cadet
8 months ago

SQL to get Employee Name Fields (F,M,L Names) using Email Field

SQL to get Employee Name Fields (F,M,L Names) using Email Field in Extensions Table.

The Extensions form is being redone in the New Forms. I see that the first field is called Related Employee, which is just blank in all records.

I would like to add the employee name which is in First Name, Middle Name & Last Name (3 fields) in the HR-Employees table. 

There is no relationship between the HR-Employees table & Extensions table. 

I would like to replace the 'Related Employee' field which doesn't appear to be pulling any information as all the data in all the records for this field are blank.

I could create a relationship. 

I would like to do this with SQL, which is a new feature I believe. 

There just a lot of table relationships in the app that I think it would be cleaner to just relate the data SQL, but I am can't find the formula?



------------------------------
Bob T.
------------------------------

3 Replies

Sort By
  • ChayceDuncan's avatar
    ChayceDuncan
    Qrew Captain
    8 months ago

    I'm not sure what you're referring to when you say you'd like to do this with SQL, there isn't anything natively that you can construct a true SQL statement in Quickbase using formulas. Perhaps you mean formula-queries? That is the closest you can get without a true relationship. Formula query documentation can be found here

    You can setup your query to search for the HR-Employee record using the email and get using the GetFieldValues setup get whatever values you need. 

    That said - I would recommend you attempt to make a relationship. Is your HR-Employee table using the native QB record id# as the key field or can you make it so that Email is the key field? If you're able to change it or if it already is email, you can create a formula-email field that copies that value of your 'Email' field in your extensions table and then setup a new relationship between these tables where you set the new field from above as the foreign key value, this way Quickbase auto-relates your records based on the email provided in the Extension record. 



    ------------------------------
    Chayce Duncan
    ------------------------------
    • BobThomas1's avatar
      BobThomas1
      Qrew Cadet
      8 months ago

      Chayce, the very accepted use of SQL queries to connect to databases, was mentioned in a news item, within the last year (if my memory is off a bit, just say the last two years.) 

      Not Select * type statements, though I see that can be done. 

      The two pictures show the two tables both having the email field, which is the related field. So the WHERE would be HR-Employees.Email Field = Extensions.Email so the fields the three name fields in HR-Employees table (First, Middle & Last Name fields) concatenate  would be put in the Related Employee field of the Extensions table. 

      The Related Employee field type would need to be replaced as a 'Text Formula' and the appropriate SQL 'Like' statement would generate the Related Employee name. 

      As it is the Related Employee is blank for all Extension Records.



      ------------------------------
      Bob T.
      ------------------------------
  • BobThomas1's avatar
    BobThomas1
    Qrew Cadet
    8 months ago

    This will close the thread, though any comments welcome. 

    I solved the problem without using a relationship. 

    I have seen APPS in QB that just seem to have to many table relationships, where it isn't necessary to have, though they can be used. It makes troubleshooting, hard, and that is what I am a troubleshooter in programming.

    Now I put the two tables in Word along with the formula for the field that is circled in red.

    The source is from the Community Forum URL: https://community.quickbase.com/communities/community-home/digestviewer/viewthread?GroupId=103&MessageKey=afc09eac-7e6f-4534-85f8-2258eab08f40&CommunityKey=d860b0f8-6a48-487b-b346-44c47a19a804&tab=digestviewer

    Thanks to all in the thread including QBJunkie.

    At school, so it may take a bit for me to respond to comments. Thanks.



    ------------------------------
    Bob T.
    ------------------------------