How to create Drop Down to trigger ID match

[dmontano]

I was asked to post my question in this section - it was in Portal & Relationships earlier titled: "I just really confused myself - stuck."

 

In that thread there was a suggestion that did solve my problem, but doing so seemed like there had to be a better way.

 

Brief history: I made the mistake of creating my "unique" joins by joining fields that were not unique, such as: "vendor_company_table::vendor_company_name" joined to "vendor_employee_table::vendor_company_name".

 

Worked just fine - except I realized this was not a unique join. So I want to fix every instance in all the DBs I have built. The ironic part to this is I have built all my tables from the start with a unique id key - problem is I did not use them in my joins of the relationships that were pulling the data from. I was - and still am - somewhat ignorant about the join to the key ids.

 

It gets even cloudier for me because I was under the impression that if I just tied the two tables "vendor_company_table" to "vendor_employee_table" in the relationship graph by unique IDs - the uniqueness was taken care of. After doing this, I would then add additional TOs like: "vendor_company_table::vendor_company_name" joined to "vendor_employee_table::vendor_company_name" thinking the keyid joins were already created by the previous ID join. I hope someone can figure out what I am trying to say.

 

That is the brief history. I am currently trying to find all possible methods on how to use the keyid join, but only display information that is understandable to users - such as: ABC Company, instead of an ID number.

 

I know this can be done through the value list - to show a second field like "ABC Company", however, the result in that field is the KEY ID. I do not want the KEY ID to show.

 

Lingo Jango had suggested a solution and it works - the transparent layered approach, however, I am curious if there are other ways of doing this.

 

I am not concerned with the amount of work I will have to do to fix the mess I created - I am really interested in the right way to do this. I am not suggesting what Lingo suggested was not the best - I am only soliciting for other techniques that I may prefer over the transparent method.

 

Thanks

[David Head]

Hi David

Basically, to create a link between a company record and their employee records, you need to insert the company ID (primary key value) into the employee record. As you have found, doing this without showing the key at either end is problematic - but not if you know how to script it.

 

There are two ways to create a new employee record using the primary key in the background:

 

1. set up the relationship to allow creation of new records in the child table and do so via a portal on the company layout.

 

2. script the process

 

The second takes more effort but is a much neater solution. I have attached a sample file showing how each method works.

 

Many developers do not like #1 because it is a bit limited in what fields you can fill in for the new child record and there are some user interface artifacts.

 

There is a third method. That is to create a new employee record and script a pop-up company picker window where the user picks a company. I have not implemented that in the sample file but just to let you know there are other ways.

 

Have a look at the sample file. Specifically, look at:

1. the relationship between tables - primary key to foreign key

2. note that there is NO company name field in the employee table - knowing the company name is through the relationship

3. see that you can add an employee to a company via the company layout employee portal - no keys are visible

4. see that you can add an employee to a company using the button provided that runs a script to pop up a modal window

 

See if that all makes sense to you.

[dmontano]

Thanks David,

 

For some reason I am unable to download the file - I will try later in the day.

 

I have one layout "vendor_employees" that uses (I believe) the 3rd method you had mentioned.

 

I will take your advice and mess around with these concepts to see if I can get it ground in my head. I keep hoping I will soon "get" how all these things work.

 

Thanks