The never ending saga of look-up vs select lists, and more potential differences

[Brendan_Woithe]

Hi all!
I preface all this by referring to another topic on this board. Its a good discussion which answered a bunch of questions for me, but then also left me with more! Always the way when learning right?

Lookup from Table vs Selectable List

Hi There! Can someone explain to me functional differences between using “Lookup from table” and a “Select list” where the “Selectable options” is a table? One noticeable difference is that I can filter the available options in the “Select list”. For example, Projects.filter(Not(isArchived)), which means I don’t have to see all of my archived projects every time I select one. Is there a way to do the above with “Lookup from table”? I’m a little confused why both exist when they appear to over…

I’ll try explain what I’m seeing - but if its too much, I’ll make a temp demonstration doc that you can muck around in.

In my current doc, I’ve got a lot of buttons that create new rows in various tables, and pre-fill them with some data from other tables. Yes, this does mean that theres some doubling up of data, but its not a tonne, and is being done for good reasons

Anyway. When I add data into a column of another table of the type “look-up”, the data is placed as normal data. (I’m not quite sure what the correct word is for it - but it doesn’t have the blue line around it)
But anything that is selected in that table column manually gets the blue border line - which i gather means the data is being referenced.

Now for the most part, this isn’t a huge bother. However, I’ve just discovered that it DOES make a difference to formulas.

Indeed, I have a formula in another table (call it table A) looking to check if there is an associated row in table B. If the data in table B is a reference, the formula works. If the data is just normal text, the formula doesn’t work.

Here’s some screenshots demonstrating what I mean.

Relevant section of Table A.

The formua is correctly saying the final project (the last row, which is all I’m caring about for this demonstration) has no tasks associated with it.
Here’s the formula for creating the task. Note - this creates text in the lookup column!

And this is the result in Table B - note that the inserted project name is text!

If I have a look at table A again, my simple formula in the last column is working

Like it says - All good!
Take a quick look at that formula :

Now. If I go back to Table A and SELECT the same project (where previously it was placed text) it becomes a reference…

AND the formula breaks…

(ie - it is now returning FALSE rather than TRUE)

So I’m wondering if in my reading I’ve missed something about the difference between referenced text and text when writing formulas. AND how to place referenced text rather than the normal text I get when using my button. I also wonder if it is not something to do with look-up rather than select-lists?

I apologise if some of the terms I’m using are not correct. It comes from really only starting to dig deep into Coda last week, and deciding then and there I wanted to move my 5 person business across to coda after successfully building out an app for a project we have coming up! Ha! Now I’m WELL out of my depth, but enjoying the challenge.

[Paul_Danyliuk]

Short answer: you’re mixing text values with row references. You shouldn’t mix them.

Row references are those blue-bordered boxes. The text you see is the text from the referenced table’s Display Column:

Text values are, well, just text values.

Comparing a text value with a row reference will always return false because those are a priori different things. You should revise your data tables and column types.

In your case, make sure that the values you’re getting in the select list are always row references — and rewrite the “all good” formula to read:
If(Tasks.Project.Name.Contains(thisRow.Project Name)...

Or better, link tables further: pull the list of tasks into each Project, then simply check that Project.Tasks.IsBlank() or something like that.

[Brendan_Woithe]

So this helps me a bunch.
I suspected as such.
However…

@Paul_Danyliuk

You should revise your data tables and column types.

In your case, make sure that the values you’re getting in the select list are always row references

I feel like the question is HOW does one rewrite the formula for the button to make sure that the data I place into the select list is a row reference and not text - given its being placed their via a button…

I can see why its placing it as text right now - but having searched the formula list top to bottom I can’t work out how I could place data there as a row reference via a formula. As is the case with all of this, I’m sure I’m missing something fairly simple…

(I’ll add that the project name in Table A IS where its expected to be text, as it is where the user inputs the project name for the entire doc!)

[Paul_Danyliuk]

Don’t put thisRow.Project Name, but just thisRow.

And check the settings for the Project column in Tasks table — looks like “allow multiple values” is enabled there, making it of a list type (see more about that here, and the issues you will be running into if you don’t fix this)

---

@Brendan_Woithe

As is the case with all of this, I’m sure I’m missing something fairly simple…

[Brendan_Woithe]

I laughed hard…

[Brendan_Woithe]

Oh you’ve hit the nail on the head. I’m not quite understanding why its working yet (particularly the last hint) but I’m going to fiddle a while and try work it out - referencing the other link you sent. MANY Thanks.

[Paul_Danyliuk]

thisRow is a row reference. You put a row reference into the Tasks table field. Then you can get data (aka dereference) from that field, e.g. in Tasks table write a formula like thisRow.Project.[Project Name] to pull the text value from that row you put into the Project column.

Linking tables via lookups (and preferably by row references and not arbitrary text/integer columns) should be the first step when designing data structure in Coda. This is very opposite from, say, SQL, where you actually link by foreign keys, and pull related data upon queries. And in Coda, you link data ahead of time.

[Brendan_Woithe]

@Paul_Danyliuk

And check the settings for the Project column in Tasks table — looks like “allow multiple values” is enabled

Its not - but your link opened my eyes to another whole WORLD of info that I need to take into account. Thanks.

[cblock]

@Paul_Danyliuk

Linking tables via lookups (and preferably by row references and not arbitrary text/integer columns) should be the first step when designing data structure in Coda. This is very opposite from, say, SQL, where you actually link by foreign keys, and pull related data upon queries. And in Coda, you link data ahead of time.

The words to live by.

[BenLee]

Hi @Brendan_Woithe,

Thank you for the detailed post! It points out something that is a common mixup, for us in our own docs here as well!

I started using Coda before we had the color “capsules” in the formula builder to help know what tables, columns, and rows you were referencing, so this is something that has been improving over time. Recently, @Jason_Tamulonis did some work to improve how these formulas operate. You might notice the formula building auto-recommending values better than it did before where it will auto-complete with a “capsule” row reference when it’s able to instead of text in quotes.

You can read more on the update here…

Update: Formula Improvements - Type understanding

Coda has a rich formula language composed of many different formulas, data types, and objects. A big part of making a good formula user experience is making sure that Coda can understand the data types of every part of your formula, so that we can provide relevant autocomplete, check for errors, and much more. In some simple formulas the like Date() the typing is very specific. It takes a number for the year, month, and day and returns a date. In other formulas the typing is much more dynamic. …

If you have other questions, or keep running into this situation, let us know!

Ben