A selection join pulls data from one table into another. This article will walk you through the process step by step. If you run into trouble, reach out to Support—let them know the outcome you’re aiming for and how far you’ve progressed.
In this example, we want each job, in the selection "Ongoing CM Jobs", to display the responsible user’s phone number. Because the Job table doesn’t contain phone numbers, we’ll need add a new column to the Job table and use a selection join to pull the phone number from the Person table.
First, confirm you have a selection that lists each person’s telephone number. If you don´t have one, you create one before you continue.
Create a new Column:
1. Go to System Settings.
2. Click on “Tables” (located under “Selection”).
3. Select the table where you want the data to appear—in this case “Job”. Dubbel click on it to open.
4. Click on “Add” and select “Selection join”.
5. In the column name field, write “Tel. nr. respons.”.
6. Make the following settings:
- Column for local key: Responsible
- Selection to join: All persons
- Column for remote key: Person
- Column for remote value: Phone
- Function: First
Set the "Local key column" and "Remote key column" to columns that hold the same information (in this example, the user’s first and last name). The column names can differ between the two tables, as long as the values they contain match.
7. Click on “OK” and then “Close”.
8. Close the system settings.
Add the column to the selection:
1. Go to the selection group "Corrective Maintenance". Right-click on the selection “Ongoing CM jobs”, and select “Edit”.
2. Go to the tab “Output columns”.
3. Add the column “Tel. no. responsible.”
4. Move the column so that it is next to the column "Responsible".
5. Click “Save and close”.
What does your selection do?
- Your job selection now retrieves the telephone number for the responsible person on the job, from the selection all persons.
- Local key and Remote Key must be linked together, and these two must therefore contain the same information. In our example we have used Responsible. Local key is the column of the selection we are in and because the Responsible column contains information about a Person (first and surname), we will use that column. Remote key is the column in the selection we want to retrieve information from and we need to pair this with our local key. Because the local key contains responsible, our remote key must also do that. Therefore, we use the person for column because it contains a person's first and last name.
- Remote value column contains the information we want to have, in this case phone number.