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

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.

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!

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.

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? 

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?

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. 

I left out a step.

Change the Key field to the Employees table to be the employee number field.

ok, I'll look into this further. Thanks