I believe you are looking for a method of selecting Contacts by their name but insert their ID (PIN) into the field. If so, you create a new value list (File > Manage > Value Lists), call it Contacts and use values from field. In next screen, select k_ID_Contact and then to the right, also 'display values from' Name_Full.
Then below, specify show values only from second field. Now on the ContactID field in your other table, attach the Contacts value list as pop-up. Pop-ups should be used because they restrict User to selecting from the list and they do not allow free typing. The added benefit is that, when you leave the field, even though it inserted the ContactID, it still displays the contact name.
Once this ID is in the field then the relationship from this table to the Contacts table (which should be based upon the ContactID using = ) will complete the relationship and all contact data can be placed directly on your layout and they will then display the contact information.
If this is not what you need, let us know. :-)
Actually what I am trying to do is the user will enter a PIN number, and the information related with that (name, surname, address, phone numberc etc.) PIN will migrate from ContactManagement database. I am trying to put multiple people with different PIN's under another database which is Project.
"I am trying to put multiple people with different PIN's under another database which is Project. "
Well I explained the selection process on the customers, which you will need to do in Projects. So let's go backwards and get the relationship in place, since there isn't one.
Projects table should have:
kp_ID_Project (auto-enter serial number )
ProjectName ... etc
You need to use a join table, call it something like ProjectsPeople. It will hold the following fields:
kp_ID_ProjectPeople ( auto-enter serial number )
kf_ID_Project (number) ... or text
kf_ID_Contact (text) ... note ... all keys should be serialized numbers but since the entire Contact Management file has them as text, just be sure to match the data types between same IDs
Now you join Contact Management to ProjectsPeople as:
Contact Management::k_ID_Contact = ProjectsPeople::kf_ID_Contact
Then join Projects to ProjectsPeople as:
Projects::kp_ID_Project = ProjectsPeople::kf_ID_Project
and while there, look below and, on the ProjectsPeople side, check 'allow creation of related records'
Now you place a portal on Projects from ProjectsPeople. In this portal first row, place only the kf_ID_Contact field from the ProjectsPeople table. Place any fields from the Contact Management table also in this portal row if you wish.
Create the value list as I described originally and attach it to the kf_ID_Contact field. Now when you click into the first empty row of the portal, it will create a new join record (in ProjectsPeople) and it will insert the Project ID automatically as well.
If you would like a demo, I would be happy to provide one.
"and the information related with that (name, surname, address, phone numberc etc.) PIN will migrate from ContactManagement database"
In relational database theory, it is best to have data exist in only one place. This means that you do not have to go to several places to change someone's phone number (for example). By storing data in only one place (and using relationships to display it anywhere it is needed), you can change it once and it will change throughout your solution.
Sometimes however, you might need to store the 'current' information for audit/historical purposes. For instance, if you ship products, you want to store the customer address within the invoice so you know the address you shipped to and not the address they may live at currently.
So once your are set up, you may decide that is is important to store something from your contact right within the ProjectsPeople table. If so, add your field and then go to the field Options and the auto-enter tab. You can set up a Lookup or an auto-enter calculation to fill the required field as soon as you select your contact.
So it is best to display just by placing your related Contact fields but if needed, you can 'migrate' your Contact Management data to their new ProjectsPeople table.
I got what I was looking thanks for the help LaRetta. Just a as a note though, I didn't need the value list after what you described because in the portal, I can add new rows when I click on the portal and when I enter a value kf__ID_Contact, name_full and phone1 shows up near that field in the portal.
Now, I just need to ask another question. How do I setup a button so that instead of clicking in the portal to add a new row, when I click the button, the focus goes directly to the next available spot in the portal?
"I didn't need the value list after what you described because in the portal, I can add new rows when I click on the portal and when I enter a value kf__ID_Contact."
That was my point. You use a popup as a selection tool so you do not need to know the Contact's PIN ... it displays their name for selection but inserts their ID instead. And it means that you already have a relationship set up with 'allow creation.'
"How do I setup a button so that instead of clicking in the portal to add a new row, when I click the button, the focus goes directly to the next available spot in the portal?"
Go To Portal Row [ Last ]
If you wish, your script can also contain the specific field the cursor should enter, with Go To Field [ yourFirstDataEntryField ].
I am working mostly with PIN's since I have like 1500 people in the ContactManagement table. It is easier tracking with the employee's and it prevents any confusion with the names. What I meant to say is, I didn't do any value list, instead left it alone, and user enters the PIN on kf_ID_Contact field in the portal, and the rest of the info comes in automatically :)
I am good with the button as well!
Just another quick question. I want to add a button at the end of each portal entry and when I click it, I will take me to that specific contact info from ContactManagement database but the way we set it up, through this post, Go To Related record script doesn't seem to be working.