Allow defining a pick list based on a Query

[grantfitzsimmons]

We have an example in our Herbarium data where we would like to link a determination citation to a specific preparation within the collection object (we want the user to select one of the values from the BARCODE field in preparation). It would be very helpful if we could configure dynamic picklists - essentially defining a list that I would write in SQL as:

select DISTINCT [column] from [table]

However for our case we want a WHERE clause that limits the rows to the existing collection object.

select DISTINCT [column] from [table] where collectionobjectID = current

It might also be nice to have an optional WHERE clause that limits it to the current collection or all rows.

Our examples relate to linking determinations and preparations, and include applying this dynamic picklist to the other linking tables from those (preparationattributes/preparationproperties/determinationcitation), so we'd need to be able to know the collectionobjectid even if the table we are applying the picklist to doesn't have it stored directly.

Requested by: Dan B at CSIRO on Asana

[maxpatiiuk]

just to clarify, this is possible right now:

It would be very helpful if we could configure dynamic picklists - essentially defining a list that I would write in SQL as:

select DISTINCT [column] from [table]

the only part that's not possible is the where clause

but rather than implementing that, why not just allow defining a picklist from a query? (as long as the query returns either 1 or 2 columns - first column is value, 2nd is title)

[danb213]

Hi @maxpatiiuk, creating a query to build the list would be fine, but it still needs a new feature in that Specify needs to know what collectionobject you are viewing when you are on the form and want to see the options. So if I'm looking at record A12345 and I want a list of the preparation barcodes in the determinationcitation field I only want to see the values from A12345. If I build a query it would show me barcodes for every single collection object.

[maxpatiiuk]

ah, thanks for the clarification - I assumed you want some static where clause, but it sounds like you want a where clause specific to the current record

but I could also see in the future the need for a where clause based on id of the related table (i.e some field in the Agent table would be based on the values of one of the fields in the addresses it has)

thus, this could be resolved by leaving a placeholder like <currentRecordId> in the query, and having specify resolve that placeholder automatically when running the query - we do something similar in the stats page right now (where you have placeholder like Current user, among others)

though, this might be adding too much complication...