Why won't the Lookup formula work for me?

Thank you Ander. I appreciate your time and support on this document.

Hey @Lorraine_Becker !

I went ahead and added a new column to your table with a working filter formula.

Check it out and let me know if you need anymore help!

I’ve always used the filter() formula instead of lookup() and Coda recommends the same. See photo below

1 Like

Oh Scott, THANK YOU so much. That’s brilliant!

Thank you!

Lorraine

2 Likes

@Lorraine_Becker

On the table Summary table of closed trades, you should also change the Display column to a column that holds unique values. Right now that column is filled with duplicate values, which is likely to cause you problems down the road, if it isn’t already.

OK, good suggestion. I have changed the display column to the first column which is the “ID”.

Should all display columns only contain unique values? Could that be why my document is so slow and often freezes up or spends minutes “Syncing”. If so, I probably have the completely wrong structure for my document as it is tied together by the trade “ID” and I have lots of multiple entries for the same ID in most of my tables.

Ideally, yes.

Each row has a unique value under the hood, that’s how the code base keeps track of them. Human users aren’t required to designate row names (the Display column) with unique values. But you should. It will make your schema much more powerful and scalable, and prevent a lot of headaches and hair pulling.:stuck_out_tongue_winking_eye:

No idea what might be causing performance issues.

In a doc like this that tracks transactions, I would definitely set the Display column of every table to a unique value. With a doc this built out, that could be quite an involved proposition, or not so much, just depends on current schema, which I didn’t review.

Thank you so much Ander. That seems like a key piece of information that I did not pick anywhere along the way. Probably any database person that would be an obvious baseline piece of information. I do not have a coding or database background (although I am learning quickly) so that is really helpful.

I suspect the ID issue could be behind my performance issues so I will introduce row IDs and see if that helps. My husband is in the process of setting a database on Azure Data Studio and Jupyter Notebooks for me to migrate to because of the performance issues I have been experiencing.

Thanks for your assistance. I appreciate it lot.

Best regards

Lorraine

@Lorraine_Becker

Honestly, those are probably user-derived.:stuck_out_tongue_closed_eyes: Coda’s performance nowadays is remarkably good.

You probably just need to refactor your doc to implement best practices for schema, formulas, etc. Refactoring is a normal part of the development process, and nothing to be discouraged about. I’ve refactored some serious docs. Heck, Coda is right now refactoring a massive chunk of their entire code base! If your husband codes you a custom solution, he will also have to refactor that from time to time.

You know what’s best for you, but I thought your doc looked pretty cool based on what little I reviewed, and I think you should not throw in the towel on Coda so soon!:sunglasses:

$.02

Thanks Ander. I have spent a bunch of time refactoring as per the recommendations to increase performance. I didn’t make a whole lot of difference to be honest. I did not know anything about the requirement to make sure that display columns do not contain duplicates. It makes sense from a database perspective…and a person like me with a no-code backgroud would have no way of intuitively knowing that. Why doesn’t Coda return an error is you try to duplicate values in a display column?

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.

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.

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

1 Like

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!

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 :

2021-08-09_06-47-43

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

2021-08-09_06-49-05

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

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

Hi Scott,

You mean like this?

2021-08-10_07-47-08

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:
2021-08-10_07-49-32

Wow, these lookups are SO hard to get working!

Thanks for your assistance.

Best regards

Lorraine

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

I can take a look

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.

image

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 :superhero:

4 Likes

Dear @Saul_Garcia,

Really a great explanation, about the why :gem: and how :handshake:

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 :muscle:

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 :brain:

3 Likes

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

Hey @Lorraine_Becker!

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 :man_shrugging:

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)

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.

1 Like