Why won't the Lookup formula work for me?

[Ander]

@Lorraine_Becker

I did not know anything about the requirement to make sure that display columns do not contain duplicates. … …and a person like me with a no-code backgroud would have no way of intuitively knowing that.

To be clear, it’s not a requirement, but I consider it to be necessary for any kind of meaningful schema – others may disagree with that. Imho however, this concept should be made prominent during onboarding so that users who are new to relational db concepts at least have the opportunity to make an informed decision.

@Lorraine_Becker

Why doesn’t Coda return an error is you try to duplicate values in a display column?

Not every table by every user requires relational referencing. I would be willing to bet that some large percentage of tables across Coda’s entire user base are just being used as grids, with no need for unique values in the Display column.

I personally think that every column should have a setting to Allow/Forbid duplicates, not just the Display column, and that the default for the Display column should be to Forbid duplicates, in order to minimize the frequency of these types of situations.

[Lorraine_Becker]

Thanks for passing on your insights Ander. I very much appreciate it.

I am attempting to rid my document of display columns that are not RowID (and therefore unique). I am hoping that this improves performance. I will let you know!

Thank you!

Lorraine

[Lorraine_Becker]

Update on the need to have only unique values in the display column:

I asked Support about it and initially they said they didn’t know anything about that and then went off to find the information. When they came back, they confirmed that this is indeed the case… that documents will have problems if values in the display column are not unique.

This “small” piece of information has probably cost me many many hours of time struggling to make my document work. I am quite mad and losing faith in Coda. Why withhold such a critical piece of information both from users AND from support staff? Incredible!

Will be moving to a more robust platform just as soon as I can!

[Lorraine_Becker]

I have the Lookup formula problem again! I am checked that none of the tables have duplicate values in the display column but it still will not work.

Here is the formula that you fixed @Scott_Weir :

And here is my formula that does not work when I tried to your solution in a similar summary table:

I cannot see what is different and why it doesn’t work! No message from Coda about what is different… on a “dot” error… but no explanation of what that means and how to fix it!
the doc is here: Copy of Lorraine's OA Workbook L6-8

Any ideas?

Thank you

Lorraine

[Scott_Collier-Weir]

Hey there!

In your formula you have a dot operator after thisRow.month which is messing it up. you will want to try to put the dot operator after the parenthesis to pull out the trade p/l (net) information from your filtered rows

[Lorraine_Becker]

Hi Scott,

You mean like this?

I am afraid that doesn’t want to work either.

If I put the tile before the bracket but leave out the dot it doesn’t work either:

Wow, these lookups are SO hard to get working!

Thanks for your assistance.

Best regards

Lorraine

[Scott_Collier-Weir]

What page in your document is it on? Or name of table?

I can take a look

[Saul_Garcia]

Hello @Lorraine_Becker!
When you use filter or lookups, you always have to compare info with the same type of data, what I mean is, in you case you want to compare 2 dates but you are comparing a list of dates with Currecy that’s why it doesn’t work. If you want to know more about these icons check this article.

First things first, why your Month Trade Closed is a list of date?
Well, I suppose you made a filter to get this value. If my assumption is correct then, when you make a filter you always get a list of values, even if it just found one, you get the icon that is a list of values. How do you fix this? you put something like a .First() at the end of the filter
Table.Filter(X=thisRow.X).First()

Now you are telling it to the filter that you only want the first you find, this way you now have one date instead of a list of dates.
There are some cases that you actually have a list of dates on propose. In this case you could use contains(), containsOnly() or containsAll() formulas to help.

Another thing you have to be careful is, when you select a column as a date, and you only show for example the month (I supposed is what you did in thisRow.Month) the complete date is still stored in the column even if it only shows the month.
So whenever you want to know if a date is inside a range of dates you will have to provide either a Start date and an End date and use filter like this

Table.filter(Date>=thisRow.[Start Date] and Date<=thisRow.[End Date])

or compare the month values of the dates as it is in your case.

[Trade Closed (Date)].Month() = thisRow.[Date column].Month()

Now you are converting these dates to numbers with the help of the month() formula so now you could know if both dates are from the same month.
You have to be careful though, if two dates fall in the same month but in different years your filter will also select these.

Filters can bit a bit tricky, but when you understand how they work you feel like you have superpowers

[Jean_Pierre_Traets]

Dear @Saul_Garcia,

Really a great explanation, about the why and how

This is one of the fundamental knowledge cores for people working with Coda, and as you already mentioned, when you get the hang of it, it opens super Powers

Your patience and skill to explain isn’t well recognized to my opinion, as knowing how is one thing, being able to explain is something else

[Lorraine_Becker]

With all due respect Saul I think Coda has a BIG problem here. As I understand it, being able to use Lookups as opposed to multiple views is essential to staying within the functional computational limits of Coda. I am experiencing this because my documents now lag and “freeze” when I try to use what I have built the documents to do. Coda engineers have run tests for me and have confirmed that the backend cannot compute fast enough to service the frontend doc that I have built.

I have built my docs based on views, and multiples of them! I used this approach because I cannot figure out how to use Lookups… despite reviewing all of your materials, and multiple engagements with Support, and input from posting here online.

So, if no-code users like me cannot figure out Lookups and the backend of Coda cannot support the frontend without documents being constructed using Lookups rather than views, you guys have a problem. I guess options might be:

1. boost power of backend so no matter what users build Coda can compute it on the back end;
2. change architecture to run calculations in a more robust database in the cloud or locally, keeping the awesome UI the same; or
3. improve documentation on Lookps as well as including all essential information - like display columns should not contain duplicates!

I do not believe that referring users to consultants in order to sort out basic functionality like Lookups is an acceptable solution, at least not for me. I got a quote from a consultant to help me and it was going to be $250/hour for a minimum of 8 hours - not a viable option.

Tomorrow, like most Saturdays for months now, I will try AGAIN to figure out Lookups using the information above Saul, thank you. Funnily enough I can get the filter formula to work if I just type it into the document, but somehow when I take the same formula and put it in a Table I cannot make it work.

I’ll let you know how I get on.

Lorraine

[Paul_Danyliuk]

Hey @Lorraine_Becker!

@Lorraine_Becker

Support have been great in the past but now they are sending a stock response directing me to ask the community instead.

Are you sure it’s not the canned bot response that you get immediately after typing your question? I’ve never encountered that — it was always humans who responded me. Sometimes in a day or two but there was a human on the other side for sure, and never an instruction to go figure on my own.

That said, I’ve heard that sometimes the support would answer superficially, not being able to comprehend the client’s problem fully. Maybe that’s because of the volume of work. Or maybe it’s a skill only available to the chosen ones, idk

Reading through the thread, I got a feeling that you could be simply missing a few fundamental bits after which everything would click for you and you’d love Coda again. I’m myself a consultant of a comparable hourly rate, no minimums though. I’ll gladly help you get unblocked — please DM me and we’ll schedule some time for a 30-40 min call I won’t quote for.

Also I welcome you to watch my videos on Coda basics here. Those are long but should have enough aha! moments. I’ll turn most of it into a concise basic Coda course at some point (most likely a Udemy course that will always sell at the discounted $10 or $15)

Best Practices Showcase (8 hours of videos & a doc)

I created a doc to showcase best practices for making Coda docs that are enjoyable to build and use. As I was building it from scratch, I filmed it and narrated extensively. At the moment this is the single concentrated source of knowledge on how to approach building great Coda docs, how to be mindful of good user experience and clean maintainable architecture. I tried to pack as much Coda experience that I’ve got over the last few years as I could into these. Hence the length: I’m explaining …

I know you probably don’t have time and may think Coda is not worth investing into any more, but I’d like to prove you otherwise.