Context
There are two pieces of context worth establishing:
When an item is chosen in a lookup, I’m choosing a specific object by reference. I expect my reference to remain intact unless I actively change it.
A lookup column can have a filter defined in its Item Settings to restrict which rows are displayed as options in the UI. It’s a super useful feature, especially when there are multiple rows in a table with the same display name.
Expectation
Changes to the Item Settings filter should change which items are suggested in the UI. They should not change the object lookups themselves.
Bug
When the Item Settings filter is changed all of the lookup values get reevaluated. But rather than reevaluate them by some underlying ID (i.e. by the actual reference), they’re reevaluated by display name.
So if there are multiple rows with the same display name in the foreign lookup table, all of the references now point to the first one int he list (which is often the wrong one) causing data to be lost in an unexpected way.
Severity
I don’t know how often this happens to other people, but it’s happened to me several times. I was previously confused about what was going on and only now in retrospect understand why I kept having references to the wrong items popping up. Given that it causes actual data loss in a very hard to identify and unexpected way, I think this is worth fixing.
I’ve recorded a video showing it in-action. Please excuse my voice I’m not much of a voiceover talent.
I think the following happens: your column is filled with certain rooms, then you remove the filter, so the lookup will find the first matching object in the lookup table. Put the filter back in place (the filter has an error so you need to fix the filter) and things should work as expected again.
It’s a filter on displayed values, not a formula for what the cell values should be. Changing the filter on what’s displayed shouldn’t change the manually set lookup values.
Even still, the same outcome occurs when fixing, removing, or editing a working filter.
Sorry to hear that it is not as simple as I thought. It sure is strange - I hope someone can shed some light on this - it hasn’t happened to me and I use a lot of lookups. If I can find some time I will experiment a little and see if I can reproduce this.
Hi, sorry to hear about this. I’ve gone ahead and filed a report with our team to investigate further. We’ll post an update as soon as we have one to share. Thanks!
There are some cases where Coda treats lookup references as expected and others where it does not. For example:
If I copy lookup references from one column to another, the correct data is pasted. This is true even if the destination table is a text column
But if I then change that text column to be a lookup column, suddenly the data is overwritten as with the example above.
My guess is that there’s some old column refresh code that runs when this filter is applied and when a column type is changed that re-evaluates the data in the column and its “object reference” handler does a lookup based on display name instead of using the actual object’s underlying ID.
Hopefully someone will update the handler to treat object references differently and that will fix both of these problems and should be more performant to boot!)
Hello @Christian_Rodriguez3 ,
I do agree that this is a bug. Somehow the item settings have an impact on the data and it should not work the way it is working now. This can indeed lead to massive data loss when editing parameters in the lookup columns.
Thank you for sharing the document that illustrates this problem.
I hope this will be fixed.
Greetings,
Joost
Update: I notice that the moment you change from Filter ‘none’ to ‘custom filter’ or vice versa, your data gets screwed up, even if you don’t make any changes. I really don’t understand why this is happening and I am pretty sure it didn’t work like that in the past.
A fix is indeed urgently needed, because this can ruin your day in a hurry!
This bug has royally screwed up some of my sensitive data (master security codes for installed locks) and I’m not sure how we’ll be able to recover. I’m very frustrated by this.
You should be able to recover your list by looking at older versions of your doc. I have also screwed up tables in the past (not with this bug) and recovered lost data by copying from a previous version.
It’s easy to forget to use this method, so I hope you will recover your data.
I haven’t seen the fix hit yet and am guessing that you’ve covered these bases, but am including as an FYI just in case it wasn’t covered by your test cases: this issue also arises if you change the lookup column from single to multiple select.
I’m currently waiting on your fix to go live to make the change to one of my columns. First time around it caused so many errors my doc started flashing red ha!
Fix should be on prod now! Let me know if you still run into any issues. The fix should have addressed any unexpected value changes caused by changing any of the lookup column settings in the configuration panel.
