Lookup Table with Unique Names Only

[cnr]

I have this table, which shows all the different Units I can build. When I select the dropdown, however, the list that it offers is really really long.

How can I make this only offer the unique Unit Types from other unit tables?

The Unit Data Table I am pulling from looks like this:

[Nick_HE]

Can you show what the unit table looks like that you’re pulling the select options from?

[Ander]

@cnr

The fact that the table you are looking up has so many identical values in the Display column is something you might want to ponder. As a general rule, you give yourself more options with the rest of your build if the Display column is a unique value.

Then again, it may not matter at all.

Selectable Options in the image is: types.type.Unique()

[Ander]

@cnr

I just played with this some more and it looks like you don’t even need .Unique(). That seems to be built into Coda’s ‘Select list’ algo.

To see this better, set up two Select columns and enter these Select options, one in each column:
types
types.type

The first references a table and returns a list of the table’s rows, and since every row is unique under the hood, it returns “duplicates” (because they aren’t really duplicates).

The second returns only uniques.

The reason you’re getting all the duplicates in your list is because you’re using a Lookup column which references all the rows in the Lookup table, and since each row is unique under the hood, it returns every row in your drop down list. This relates to what I said earlier about having unique values in your Display column.

[cnr]

Hi Ander,

Thank you for your response and all your work on this, I think many people will find what you’ve written very helpful.

Unfortunately, I cannot use a select list. I need to use the Lookup from Table column format because I need the items in the dropdown to update dynamically when I change their names elsewhere in other tables.

Any thoughts on how to accomplish the same thing with Lookup from Table?

[cnr]

Yes, I have updated the original post to include an image of it

[Ander]

@cnr

More tables are better than fewer tables.

You probably need at least one more table.

[Dalmo_Mendonca]

@cnr

I cannot use a select list. I need to use the Lookup from Table column format because I need the items in the dropdown to update dynamically when I change their names elsewhere in other tables.

If you use referenced Selectable Options exactly like @Ander described, updating the source will automatically update the references. So if you need just the names (Text values) that works.

But even if you need to reference the full row, to be able to fetch other properties, Select List still works. Actually Lookup From Table is nothing more than a Select List with restricted Selectable Options.

Seeing as your “Unique” name column is not unique at all, the question is what column do you want to reference when you select it through Lookup from table? You probably need to rethink your schema a bit.

As it stands, you can either create another table like Ander showed just above, or you make your Unit Type column a Select List, then use the following formula for Selectable Options. It will fetch the first row of every “unique” set of rows.

[Unit Data Table].Filter([Unit Data Table].Filter([Unique Unit Name]=CurrentValue.[Unique Unit Name]).Find(CurrentValue)=1)

[cnr]

Here, unique only means that there is only one unit with that particular name, but that one unit may have multiple Items that relate to it.

This is the same as the fact that you and I probably have a unique user id in Coda’s database somewhere, but we can still have multiple docs to our name.

[cnr]

Ok, I’ll look at @Ander’s solution and try merging it with yours to see if I can get it to work for me. Thank you for your help.

I think you may be right, I need to rethink my format. I am frustrated by this as I keep having to refactor my doc as I add functionality because my beginning data structure doesn’t support what I want it to support. Now that I have hundreds of rows and real data that relies on this, it’s getting costly to do that.

[shishir]

Looks like you’re getting good help here but on the point of “transition a column to a lookup column”, there’s a little trick for how to do it without doing a lot of work:

[cnr]

Pure gold.

[Lorraine_Becker]

I am having the same issue @cnr ! I wish Coda were upfront in their documentation about the limitations of Coda or the right way to structure docs so you don’t run up against these limitations. It would avoid this user-disappointment later.