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
In particular :
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!) )
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!