Auto enter related field

[William Lindsay]

Hello

I am sure the answer to my question is out there, but because it is so general, I have found a lot of unrelated stuff.

I have three tables as seen in the attached.  I am working in a layout based on Job Sheet Main and have a portal displaying Job Roles. I wish to select an organisation (company) by name (not ID) in this portal and have the CompanyID auto enter. 

Which is the simplest way to do this please?

Thanks

 

 

[comment]

2 hours ago, William Lindsay said:

Which is the simplest way to do this please?

The simplest way is to define a value list using values from the CompanyID field, also displaying values from the CompanyName field, and set it to 'Show values only from second field'. It will look as if you're selecting the name - but the value entered be will the ID.

[William Lindsay]

Thanks comment. I actually have this set up as you describe. My users are picky and want consistency throughout - see attached. Would it be possible to select the organisation from a drop down and have it all populate the ID via a calculation?

[comment]

3 minutes ago, William Lindsay said:

My users are picky and want consistency throughout - see attached

Not sure what is the "consistency" that the picture is supposed to demonstrate.

[William Lindsay]

21 minutes ago, comment said:

Not sure what is the "consistency" that the picture is supposed to demonstrate.

I guess they want to click in the field where they want the company result and not to the left of it.

[comment]

I am afraid I don't fully understand what you're saying. To get the kind of display your screenshot shows, place the related CompanyName field on the layout, make it non-enterable, and use it to cover the local CompanyID (foreign key) field that has the value list attached to it. When they click on it, the underlying field will activate and they will get a drop-down (or pop-up) to select the name from.

The alternative where they would actually select the name is much more complex: you would have to define another relationship to another occurrence of the Companies table, and use it to make the local CompanyID field lookup the corresponding value from there.

Note that you can also select from portals or list views.

--
P.S. Please update your profile to reflect your version and OS.

[William Lindsay]

Thanks Comment - that does the trick - so simple and everyone happy.