Is it possible for a query or calculated field to cross reference matching fields across two entities?

The Goal:

Looking to create a field “Progress” in entity “Shots” whose possible values are limited to a pulldown list.
I can do that using a field configured as a List, but I’d rather that the options in the pulldown be derived from a related entity.

Call that related entity “ProgressEntries” and there are two particular fields: Name + SortOrder.
That sort field would server two functions:
- Control the display order of the pulldown
- Serve as the basis of sorting by the Progress column.

My impression is that none of that is currently possible in ShotGrid, so I’m trying to jerry-rig an alternative:

Use a field “Progress” in the Shots entity of type List, with pulldown options entered in the expected ShotGrid way.
Then create a custom entity, ProgressEntries to holds those same List items in, say, the “Name” field, along with a column “Sort”
Then, using second calculated or query field in Shots obtain the Sort values from ProgressEntries.

In standard SQL terms I’d like to achieve the following:

Shots.Progress, text field
ProgressEntries.Name, text field
ProgressEntries.SortOrder, integer
Shots.SortBy, calculated integer: select SortOrder from ProgressEntries where ProgressEntries.Name = Shots.Progress

Shots.Progress pulldown options: select Name from ProgressEntries order by ProgressEntries.SortOrder

In the end, anything is possible given API expertise and time. Custom solutions are up to you, but best to look at what is available in SG first.

Progress on a Task-containing entity like Shots is usually done by viewing the Task Pipeline steps in the Shots list view. You can summarize for all pipeline steps if you want to give overall progress.

“anything is possible” – If that’s true, it may change even my original question. Is it possible, using the API to populate the contents of a ShotGrid table with custom HTML content or an iFrame? Something like that would open up all kinds of possibilities.

I’m playing with status & pipeline etc. to see if they will work, being mindful that key people on this particular production won’t take kindly to too much clicking.

Status, so far, isn’t working. It does have the necessary simplicity, but so far I haven’t been able to override its default use of its icon-only display.

Tasks is also – at least for now – not appropriate b/c for now our pipeline is extremely simple, and the sophistication of Tasks will be dismissed as too involved.

I’m happy to build something that behind the scenes might take some work if I can build something that is simple and effective for our team. Hence the earlier question: Is it possible to populate ShotGrid table cells with custom HTML content or (perhaps better) an iFrame?

Yes, thank you. I’m actually familiar with and make a lot of use the REST API.

The question about HTML is a little different in that it’s actually up to ShotGrid to decide whether HTML code (e.g. <b>test of html</b>) fed to, say, a field in table cell will be rendered as HTML or simply displayed as raw code. ShotGrid definitely goes to the trouble of preventing the HTML code rendering.

So I guess the question really is: Is there a way to tell ShotGrid to permit HTML rendering in a table cell.

I suspect it won’t be possible if SG does explicit action to prevent HTML code from executing.

Overall, the Task-based workflow works well for even small teams. It can be as simple as having just 1 or 2 Tasks per Asset.

This is worth trying before looking to build complex workarounds.

RE Tasks – Absolutely. Started digging in. It wasn’t immediately super simple, but I’ll be getting back to it.

The HTML question actually came up a number of times for other features.

Whether ShotGrid is for sure preventing HTML rendering I’m assuming only because by nature… HTML renders if you feed it to a browser, and you have to make an effort if you want to escape that behavior. But just as frequently there are allowances to include HTML – a preference or a request on the back end that permits it.