The BOOKS column displays the names of books over in the linked (related) BOOKS table. As you can see, there is no error in the BOOKS column for rows 1, 2 and 3; but rows 4, 5 and 6 are flagged with an error identified as “not a valid row reference”.
When I set this up initially, the formula for the Books column was
And at that point, there were no error flags in this column in any rows. But then I added what I thought was a valid appendix to the formula:
OK @William_Porter thanks for sharing. I think I figured out what your issue is. What you are trying to do is figure out which books are written by the author. The first step is to create a column that does just this and only this. I like to name these in my docs as ‘X Array’. So in this case I would name it ‘Author Book Array’. This would be Books.Filter(Author.Author=thisRow.Author) or Books.Filter(Author.Contains(thisRow)).
Then you can hide this array column, but use it for everything else. Want to show the authors in a bulleted list? Easy: thisRow.[Author Book Array].Title.BulletedList()
This schema is flexible and has better performance. Your ultimate issue was that you were trying to do too many things at once; you wanted to create the bulleted list, but then treat the bulleted list as an array. It is no longer an array, it has been transformed into this new object type - bulleted list - which I don’t fully understand its behavior, but as you can see it doesn’t work for your purposes. A Bulleted List is really a formatted object, like an ‘end product’, not something you should use for other formulas. Good luck!
Thank you for taking the time to look at it (and to offer the various changes to the columns in AUTHORS – I assume that was you) and also for taking the time to make these suggestions.
I can see that what you have suggested WORKS and does not throw the errors that were being thrown before. Thank you for that. I will keep your formulas to hand as models.
But – and again, I’m very grateful to you for your time and effort – this didn’t answer my questions. I’m not just trying to get things to work. I’m trying to understand them. Looking back at my first post, my questions were: Why did my formula generate the results it did? That is, notwithstanding that my original formula did in fact return the correct values (the right books for each author), a “not valid row reference” error flag was displayed for some of the columns but not others. If the formula was invalid, I’d expect two quite different results:
The row would not display the results I want (because the formula is faulty); and
The row would show an error flag in every single cell of that column, not just some.
Hmm. I have to protest that I never intended to create an array. As you said up top, I just wanted to see what books each author is linked to. I did want to display that result in a bulleted list, and I do see that the bulleted list converts the titles into ordinary text strings, while showing the results NON-bulleted, each title is in its own little bubble indicating that it is a reference. Good point and I will keep that in mind.
But I don’t see how I was “trying to do too many things at once”. It looks like I was simply doing it wrong. This formula does everything I want at once – it’s a “hole in one”:
This simply takes the formula you offered for the array (in the opening parenthesis here) and then tacks on the stuff I wanted: getting the titles into a bulleted list. And it does it with one column instead of two.
I’m willing to concede (not because I see it yet but because I defer to your experience) that this is a false efficiency, that creating the array in one column and then hiding it and referring to it as needed, might be more flexible and more efficient in the long run.
But again, I’m trying to understand why it works the way it does, and find a lot of this fairly opaque.
Many, many thanks again for your help. This community is a great resource.
I might add that some of my problem is that the explanation of the error (“invalid row reference”) is not very helpful to me, and I can’t find a more detailed explanation. By way of contrast, here is another error that I ran into today, where the error notice instantly made sense. Here’s a picture of my screen while the error was still visible:
Note that rows 1, 2 and 3 are showing the little red error flag. (I’ve circled it to direct your attention to it.) But when I added Stanley Donen’s Indiscreet (and only that for him) I noticed it had no error. So I added Murnau’s Nosferatu as well. Obviously, both those directors are linked to only one movie, while the directors in whose rows the error flag is shown, are linked to more than one movie each.
When I hovered over the “Released~” column, I got the error message: “Cannot convert value to the specified format.” And that made perfect and immediate sense: The column “Year Released” over in the Movies table is a 123 Number field. But this list of release dates in this column in Directors is obviously a string. I just changed the column format to Text and the errors went away.
This is because you set the column to be of ‘Lookup’ type. It expects a row reference, or an array of them, instead it found a bulleted list, which is not something it can ‘Lookup’. It seems to take it far enough that you return the ‘right results’ but it is erring due to the bulleted list.
Yes you are correct that you can ‘hole in one’ the formulas and many times it makes sense to do that. I did over-design your model from my experience bias. The way I suggested is the “better” way for me with my biases - which in this case I think tend towards performance, scale and best practices.
Your solution works great for what you are doing and I kind of read a bit into what you were doing and thus made that suggestion. Either works, and as a veteran I would advise you to create that extra column and do it the way I suggested - its a good practice and will help you a lot as you extend your docs.
My heads got a few bruises from all the wall-banging I’m doing with Coda but I feel like enlightenment is coming, thanks to you and others. I accept your recommendations and certainly understand the idea of best practices.
After reading and rereading your replies in this thread and reviewing the Coda docs, I now understand the Lookup column format, finally, and that’s a major advance. I know I’m a newbie but I’m going to say this now before I get used to Coda’s terminology: The term “Lookup” seems to me, um, not very helpful. I gather it used to be called “Lookup from Table” which was much better; but the word “lookup” is used in other apps in quite different ways and I think some other term might help folks like me. I don’t of course know what other, better term to suggest. “Linked Row” perhaps? “Row Reference”?