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.
------------------------------
Original Message:
Sent: 09-25-2023 15:37
From: Chayce Duncan
Subject: SQL to get Employee Name Fields (F,M,L Names) using Email Field
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
Original Message:
Sent: 09-24-2023 21:07
From: Bob Thomas
Subject: 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.
------------------------------