If you know what is VLookup, I bet you already know to apply it in Microsoft Excel. It is useful when you have a main data sheet, which 1 of the column need to refer to another data sheet. Example, on the main data sheet contain product id, and you want to display product name on the same main data sheet, but the master product information is on separate data sheet.
The limitation of VLookup in Excel is, the first column of the referred data sheet is always the key.
You can do that in NocoDB, with any column. Let’s use a claim form as an example. Assuming you have a claim sheet, with employee id on it, it will be good that the next column showing the employee name, as I don’t think you can remember all employee by their employee id. In this example, let’s assume that we have 2 data sheets:
- Claims data sheet (which contain employee id and the claims from employee)
- Employee data sheet (which contain the master employee information)
Here is the actions in NocoDB:
1. On the Employee data sheet, create another column with column type “LinkToAnotherRecord”, and select “Has Many” relationship with child table “Claims”. This indicate that, 1 employee, having many claims.
2. On the Claims data sheet, create another column with column type “Lookup”, and under “Link to another record” dropdown, select the “Employee”, and under “Child column” dropdown, select the common key between Claims data sheet and Employee data sheet
3. Now, if you pick an employee for a record in Claims data sheet, you will be able to see the employee name disiplay on the Lookup column.
4. Back to Employee data sheet, create another column with column type “Rollup”, and setup the following, which sum up the total claims for each individual employee for you, at Employee data sheet.