I want to use a secondary list to populate a dropdown column in my Sharepoint list. However, the secondary list contains data I don't want to have pulled over based on the type column.
Imagine tables as follows:
Table 1
| ID | Location | Description | Category |
|---|---|---|---|
| 1 | NYC | Junk Goods | ? |
| 2 | LA | Apples | ? |
| 3 | SF | Cod | ? |
Table 2
| Location | Category Desc | Subcategory |
|---|---|---|
| NYC | Air | Plane |
| NYC | Air | Helicopter |
| NYC | Sea | Boat |
| LA | Sea | Boat |
| LA | Air | Plane |
| SF | Sea | Boat |
In my case, I need to have the "Category" column in Table 1 lookup the "Category Desc" column from Table 2 but only return those values if the Location columns match in both.
I have looked at the lookup column set up and looked at the calculated columns for the table but those don't have everything I needed. I know you can do this in Excel, but I can't use the Excel structure in SharePoint due to a field in our list that contains an image that can't be transferred to the Excel format.
Any suggestions would be appreciated. Thanks in advance.
Instead of using a lookup column, consider using a plain text column. Then customise the SharePoint list form with PowerApps. In the list, click on Integrate > PowerApps > Customise form.
PowerApps allows you to do all sorts of things to make the form more user friendly than the out of the box SharePoint list form.
You can add the other SharePoint list as an additional data source.
For the new plain text field, you can add a combo box control to the form and populate it with the values from the lookup list, which can be filtered to your needs. Overlay the text box with the combo box and hide the combo box when the form is being viewed. Store the value of the combo box in the text field.
This is just a rough outline of the approach. It is a bit of a learning curve, but it's worth conquering, because it is VERY powerful.