Created Related rows in spreadsheets based on primary keys. Have related row lookup fields in spreadsheet. Creating form view to have users fill out data but the related row lookup selection field doesn’t show up in the form view as an available field to add to the form.
@Abigail_Merritt
Related Row lookup is a ‘computed field’. ie, its value is computed based on the value of the Related Row itself, once it is filled.
For eg, if ‘Home State’ is a related row, ‘Home State Capital’ is a related row lookup.
So once I choose NY as the state, Albany is automatically computed ( and is not editable). If I change the state to CT, the capital will automatically change to Hartford.
Which is why it is not exposed as a Form field. However, I agree that in certain cases it may make sense to expose this information to the person who is filling out the form. What is your use-case?
In my mind I’m treating related rows as primary keys. So if I link a sheet to my employee directory, then I would put the related row as the employee id number and not the name field. If I’m having an end user fill out a “request” type of form and part of it is selecting an employee’s name from a drop down list I would want that related row lookup to show up and not the employee id because end users wouldn’t know those numbers. You can only have one “primary column” or key per sheet so to me it should be the unique identifier (number) and not a name that could have a chance of duplication.
1 Like
This sounds like something commonly done with database applications where you have ids as the primary key, yet when you are doing a query you want to search for and choose a value based on information in other columns for that related row (not directly by entering the primary key value).
This is one of the things I really like about Spreadsheet.com, which does it much better than just a data validation based drop down list in Excel and Google Sheets. The facility management template provides an example when choosing a building via the worksheet view:
Note all the extra information that you can see which can help you choose the correct building. Showing this additional information when choosing a value for a related row within a Form would be great (and being able to search based on the other columns would be ever better - see below). I’ve made this same request before, but I can’t remember which thread.
My guess is that Abigail wants to see this related information in the form even though it isn’t something that the user would specifically edit. In the FM case above for example, it might be to show the information associated with the building that was selected so that you can make sure you selected the right one (and it would make it appear as though the form automatically populated the fields).
Within other database applications where you are performing queries directly within forms to search for the items you want to associate with the record, you might have the ability to search using one or more different fields. In my example above, that would be like being able to use the Search field to search/filter by address or city instead of only being able to search by the primary key value (“Building X”). Some people may become so familiar with their data that they can choose values based on Primary Key, like those experienced checkout people who have memorize SKUs. But most of the time, you’re wanting to search for items based on the fields other than the primary key id.
2 Likes