How to reference fields in a related table without going through the picker

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
Hi, 

I have a table with employee information such as name, title, and employee number. I have a separate table in which to assign assets to said employee. The two tables have a relationship such that the employee number can be selected from a drop-down list in the "assets" table and the name and title will populate. This is what I want but as I expand the list of employees in the drop down list (to over 1000) it switches to the picker instead. 

Is there another way to reference the employee information without going through the picker? Using the picker adds many more clicks and is inconvenient and time consuming. I would simply like to be able to type the employee number in the "assets" table and have it populate the name and title from the employee information table. There are too many employees to use a drop-down and I'd rather not use the picker. Is there another way?

Thanks.

P.S.  I'm pretty new to Quickbase so please be specific. 
Your help is greatly appreciated!
Photo of SF

SF

  • 222 Points 100 badge 2x thumb
  • perplexed

Posted 1 year ago

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

QuickBaseCoach App Dev./Training, Champion

  • 53,038 Points 50k badge 2x thumb
Yes, there is a way for sure. I will respond later today. But in the mean time, do you just want to allow for direct entry of the employee number or give the user an option to use use the record picker as well.
(Edited)
Photo of SF

SF

  • 222 Points 100 badge 2x thumb
Great! 
Having the option to use the record picker as a backup would probably be a good idea. I was just looking for a faster primary option.

Thanks!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,038 Points 50k badge 2x thumb
Ok here goes.

By way of background, we are going to need rwo very similar relationships to make this work.

One is going to be your existing relationships, Wein is going to be a new relationship based on a formula field.

So the first step is going to be to edit your first relationship and we are going to rename the field on the right side which is probably called [related employee] to be renamed to be called [related employee from record picker].

We should also edit the report link field that you probably have on the left side of the relationship and call that Assets from Record Picker.

The next step is to make a new formula numeric field on the assets table called [related employee from direct entry or record picker] , and also make a new field called [employee number direct entry]. Edit the formula numeric field to make the formula be

If([related employee from record picker] > 0, [related employee from record picker].
[employee number direct entry])

Now make New relationship but for the reference field on the right hand side use the formula numeric field called related employee from direct entry or record picker] .

Look up the employee name field in that relationship.

I would I suggest you do is go back to look at where you were using your previous employer name field and replace it with the employer name field from the new relationship. Once that old field is not used anywhere you can delete it. Then do the same thing with the report link field on that first relationship to see if it was used anywhere and if so replace it with a report link field from the new relationship.

So the effect of this is that the form of the field will either use the direct entry field or the record Pjetra field. Give that a try and post back if you get stuck anywhere. This post was mostly dictated so I hope Siri did a good job on that.
Photo of SF

SF

  • 222 Points 100 badge 2x thumb
I've managed to get half of it working, but not the half I want. When I use the picker for the "related employee from record picker" field it populates the information I want, but when I try the "employee number direct entry" field, the same information won't come up. 
Can we try it without using the record picker at all? 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,038 Points 50k badge 2x thumb
Does the employee information come up when you save? 
Did you do a lookup of the employee name on the relationship which uses the formula field?  Is that lookup field on your form?

Can you post your formula field?
Photo of SF

SF

  • 222 Points 100 badge 2x thumb
When I use the direct entry field, the only values that come up when it's saved are the "related employee from direct entry or record picker" and "employee number direct entry", both of which equal the number I entered. And this makes sense given the formula.


If I use the picker, the "related employee from direct entry or record picker" is equal to the value I 'picked', otherwise, "related employee from direct entry or record picker" is equal to the value I entered in the direct entry field.

Here are the two relationships I have set up between the assets (checked out) table and the employee information (card and employee numbers) table


Yes, there are duplicates. Once I get something up and working I can delete the duplicate information. Below I assigned an asset (a physical key) to Mr. E. First I used the picker to select him and the information populated. In the second row I assigned a different asset to the same person, this time using the direct entry but no information populated. 

*note* all information is made up for test purposes

I think this means I need a relationship between the direct entry field and the information I'm trying to get it to populate (from the card and employee numbers table). But now I'm back where I started, trying to type in an employee (or ID card) number and getting the name and title to populate. >_<*

Sorry if this is too much information. I was thinking out loud, trying to process. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,038 Points 50k badge 2x thumb
I left out a step.

Change the Key field to the Employees table to be the employee number field.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,038 Points 50k badge 2x thumb
To do that, list the fields of the employees table and checkbox the employee number field and then I think it is "Set as Key field" at the top.
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,038 Points 50k badge 2x thumb
Just btw, in future when posting a formula is best to copy and paste the actual text of the formula into the post.  That way we can help you edit it as an image of a formula is really tough to read,  but more importantly an image cannot be edited if we have a correction to suggest.
Photo of SF

SF

  • 222 Points 100 badge 2x thumb
ok, I'll look into this further. Thanks