Lookups and Select lists - an understanding, but how to fix?

[Brendan_Woithe]

I’ve been struggling away for perhaps 5 or 6 months creating a project management system for our little business, and thanks to an oft repeated piece of advice on this forum, I think I’ve just realised a few things.

Big shout out to @Paul_Danyliuk - and this post coming out of another topic

New characters appearing for displaying references

Correct — I meant you should explicitly reference the column whose value you want to display (e.g. Currency.Name) instead of just ToText()-ing the reference. I haven’t yet seen this new @ thing, but I welcome it.

In particular :

@Paul_Danyliuk

you should explicitly reference the column whose value you want to display

And this really got me thinking.

I’ve had a hard time trying to ‘lock’ parts of my project (see Editing lookups from a locked, read only table (and how do we lock a lookup table properly!) - #6 by Brendan_Woithe )

I’ve been creating select lists, then converting them to tables for ease of editing the lists / keeping tabs on all the options. But even if I make the tables read only, they can be edited as demonstrated in the gif in that post.

And I noticed that the process of making a select list, converting the list to a table converts the column type to a lookup.

And a lookup specifically looks up a ROW of a table (if I understand correctly).

So - I’m wondering… and this is a specific use case, but I bet it comes up all the time for many people here.

Whenever you are creating a list for a select list and convert it to a table, it is better to then change your column to a select list, and specify the COLUMN directly as the lookup, instead of the row.

So - here’s what it was. Bare in mind the specificity of the situation - the table is only one column wide, and doesn’t connect any further information.

Instead, we build a really simple select list with a formula

The selection is now “hard coded” but still in a table.

AND - it helps me understand paul’s simple message regarding totext / looking up columns. I can’t believe I’ve spent so long confused about this.

So - When I want to filter by this new select list, I now know NOT to use something that looks like this :

Which works perfectly fine - but only because the totext() is grabbing the display column and converting it to a string - NOT efficient.

Instead, its just a simple case of pointing to the column as referenced in the original select list.

I hope I’ve got the understanding correct. I feel like I could have done with this knowledge months ago. And I know its written about a lot, but until someone (like me!!!) actually understands whats going on, they’ll continue (like me) to just brute force things and get them working “good enough” without truly understanding the mess they’re making for themselves…

Is there anything further worth adding? Hope this helps someone else figure it out one day. Or that others come back and let me know I’ve still got it wrong and have more learning to do!

Cheers.

[Brendan_Woithe]

A question about how to go through an update my tables though.
If I go back and change the column type of the lookup columns to a select list using a formula, coda connects the options back to the ROWs and not to the columns. And I can’t see any way to bypass this.
Now - I could go ahead and just make new columns - but there are quite a few to get through (and each require buttons to read the old data and rebuild it in the new column - a time consuming job for me at least!)
and then I need to go through and check all my formulas one by one (and there’s 100’s) to update them to the new columns.

So any hints on how I might be able to successfully transition a column from a row lookup to a select column would be MOST appreciated

EDIT with more info/more tests/a gif

Here’s a little gif showing a small segment of one of my control documents - a bunch of presets for technical deliveries.
And most of the columns are lookups of single column tables - and thus can all be edited (and I want to lock them down!)

the gif shows that just converting the column to a select list (and letting it self propagate with the selections) doesn’t actually convert it to a normal select list at all - and keeps it attached to the original table.
Indeed - even then changing the select list to a formula which specifically includes the column seems to ignore it, and still gives the row as the thing you select.
I didn’t show it in the gif, but this stays the same for new rows.
Is the only way really to make new columns? And therefore ALL formulas that use these columns need to be rebuilt? I must say it all feels a little overwhelming…

Edit 2

So - I’ve worked out a LONG winded way around it, but its still going to take a while. At least I won’t have to update the formulas this way.

Step 1. Copy all the current data to a new temp column.
Step 2. Change the original column from a lookup to a select list referencing the specific column
Step3. Delete all data in the original column. Every single last row needs to be deleted.
Step 4. Copy all the data from the new temp column back

This can be done with a series of buttons. Unfortunately it cant be done with a single button - as ALL rows have to be deleted before step 4 is done. 1-3 can all be done with one button, and 4 with another.

Interesting that the buttons are taking a long time to execute, even just 100 odd rows in a table. But that is for another time.

There must be an easier way?