Possible to somehow paste new Reference / Choice items?

Hi!

One thing I got stuck with. When having a Choice or Reference column (or Choice/Reference List), it seems you can add new Choices or References by typing them, but not by pasting data?

Typing:
image

Pasting:
image

It is driving me a bit crazy, because if you for example have a list of authors, and you want to save a collection of url links in a reference table for each author (Wikipedia, Goodreads etc) , it seems you can’t just paste new references for that row.

You would need to either

  • Type in each URL manually from the “author” row
  • go to referenced table and add each url, then manually try to reference it from the author

Is this something that should be a feature request, or am I just missing an easier way of doing this?

1 Like

That’s exactly what struck me as odd the other day, too. The problem seems to be that Grist really doesn’t match the pasted data to existing referenceable records in any way on the backend-side of things. When you’re entering things manually, the frontend autocompletion via the dropdown menu takes care of that, but when you’re pasting - no dice.
Luckily, there’s a solution for that, at least for reference columns. :slight_smile: Look here!

I have a solution for choice columns lying around somewhere, too. If I get some time today, I’ll make it available. Turns out I don’t have a complete solution just yet, but there’s a function to set the available/valid choices for a column. Take a look here (function ‘update_item’ - where the item in question, here, is called “choices”), and at the README for how to use this in general.

1 Like

Hi Tom!

Thanks for the resource, after some fiddling I got it working - I am not much experienced with Python (yet).

Unfortunately when I got it working, I realized that even tough it worked exactly as I envisioned, having URLs (from my other example and first real life use case) as References makes them unclickable and even difficult to copy. So that idea unfortunately has to end up in the bin.

One solution could doing something like your example, and write “Author” from my example above into the URL reference table, alongside the url column. You could then at least open the url table and easily filter for associated author.

But when trying to send along the “Author” with “value” in

return RUC.lookupOne().Input.create_reflist_from_input(value + $Author, URLs, "URL", prevent_doublets=False)

it gives a…

TypeError : unsupported operand type(s) for +: 'AltText' and 'str'

A `TypeError` is usually caused by trying
to combine two incompatible types of objects,
by calling a function with the wrong type of object,
or by trying to do an operation not allowed on a given type of object.

You tried to add two incompatible types of objects:
`AltText` and a string (`str`).

Which I guess makes sense, from what I understand of the code, but do you know if it is somehow possible to convert $Author to AltText, or some other way to send along data from a different column in source table, to reference table?

Hey @Johan_Castello, yeah you could actually do that:

# Grist's "objtypes" modules is where the "AltText" type comes from:
from objtypes import AltText

# Rest of your formula goes here ... then do the actual conversion like this:
author_as_alttext = AltText($Author)

This should work, at least as far as Python is concerned.

Looking at your usecase, though, I’m not sure you have it all set up in the most ideal fashion. If I understand you right, you want your users to be able to use the ‘URLs’ table directly (so that the urls can actually be clicked on) and therefore that table needs to include information on what author each url is associated with. Correct? If so, then there’s a much easier way to do it!
Supposing you have two tables, ‘Authors’ and ‘URLs’, and that there’s a reference list column ‘urls’ on the ‘Authors’ table:

  1. Create a reference (not reference list) column on the ‘URLs’ table, name it e.g. ‘associated_author’.
  2. Set this column to be a formula column and input this: return Authors.lookupOne(urls=CONTAINS($id)). This will look for a record from ‘Authors’ where the ‘urls’ column contains this particular url, and point to it.
    Note: Because it’s a formula, you don’t need my “RUC” function to set it up in any way, it just works. As soon as a record from ‘Authors’ points to a record from ‘URLs’ in its reference list, said record will “know about it”.
  3. Filter the ‘URLs’ table by column ‘associated_author’ as needed.
  4. Ideally also set the text column in the ‘URLs’ table that contains the actual url to the ‘URL’ display type using the creator panel on the right. That will make it clickable.

Thanks for taking time explaining! :slight_smile:

I made a quick dummy setup to test and it… kind of works

  1. But your RUC function would still be needed to “paste” new options to the reference table (URLs), right? - the original problem
  2. The formula “works”, but instead of author names, it gives “Authors[1], Authors[2]…”

Ah, 2 was easy… :slight_smile:

return Authors.lookupOne(urls=CONTAINS($id)).Name

Glad it works! I agree on number 1, you still need to create referenced records when your users paste things. Just one final note regarding number 2: You might want to turn your formula column’s type into a “Reference List”. In that case, don’t add the .Name to the lookup but just return the record. You can then use the creator panel to tell Grist which referenced column to display.

Thanks, very nice!

I have just started scratching the surface here, but coming from Airtable, my feeling is there is sooo much potential in having the Python “backend”.

Any idea how future proof your RUC code is? While great potential with re using code and sharing snippets and “libraries”, I could also see how it could turn into a nightmare with “dead” dependencies in the long run.

I know the feeling! Grist using Python is a complete gamechanger in my mind. I find it mind-boggling how anyone would want to use JS and its borderline lunatic data model for an application that does, you know, do data modeling above all things. Python is totally the logical choice, and it works so well.

Your second point is a very good one. These things definitely need to be kept in mind. That being said, what I have on the RUC so far makes use of the functions that Grist also uses internally, so it’s not doing something hacky or outlandish to achieve what it does. Unless Grist’s inner workings get changed radically, this stuff will probably keep running without problems.

Thanks for confirmation!

While playing around with it a bit more, exported my testing db to disk and imported in Grist-elektron then back to web Grist, just to see how it flows, for some reason the functions in RUC → Import and RUC → Record started throwing weird Syntax Errors. Even recreating the “RUC” table from scratch doesn’t seem to resolve the issue… Very strange.

Hm, that looks mysterious. You can tell it’s not a “syntax error” in the RUC functions as Python would claim it is, because those worked fine before. What details are given when you expand the red error message?

Traceback (most recent call last):
  File "grist/engine.py", line 987, in _recompute_one_cell
  File "usercode", line 681, in gristHelper_Transform
    raise SyntaxError('invalid syntax', ('usercode', 8, 39, u'  def create_ref_from_input(cls, input: AltText|str|Record, table: UserTable, field_name: str="", fields: dict=None, input_processing_cb: FunctionType=None, prevent_doublets: bool=False, fields_for_doublet_check: dict=None) -> Record:'))
  File "usercode", line 8
    def create_ref_from_input(cls, input: AltText|str|Record, table: UserTable, field_name: str="", fields: dict=None, input_processing_cb: FunctionType=None, prevent_doublets: bool=False, fields_for_doublet_check: dict=None) -> Record:
                                        ^
SyntaxError: invalid syntax

This makes no sense, there’s no syntax error there. Strange. Any chance I might take a look at the document?

Yes, for sure, if you would like to :slight_smile:

I worked around the issue by recreating the app from scratch, but would certainly be interesting to know why. New with Grist and not much Python experience, but still I can’t make any sense of why it would flag syntax errors in copied code that works perfectly in a different db.

Hmmm, how can I most easily share it with you, no sensitive data or anything…

The easiest way would be: Click the “share” icon in the top right, then “Manage users”, then set “Public access” to “on”. Then go to the table we’re looking at here, copy the URL, and append the following to it by hand: /m/fork. Using that URL I can access the entire document readonly, but as soon as I try to change something, Grist will “fork” it into a new temporary document for me to play around with.

Hey Tom,

https://docs.getgrist.com/vBhAR2vxxaqt/Media-Databasewohist/m/fork

Would not surprise me if it is just me who made some obvious rookie mistake :grin:

Unfortunately it says access denied. Did you turn on “public access” as described?

I thought I did but apparently not. Can you try again now?

Thanks, it works now. Off the bat what I can tell you is that the RUC table isn’t really called "RUC’ - just the page is. The actual table, which you can see under “raw data”, is named “Table2”, so the formulas in the “Media” table can’t find it, causing an error. Maybe that’s really all there is to it? Though this “syntax error” thing is still a mystery.

Hehe, yes as mentioned I worked around it by recreating the app, just curious why it would insist of “inventing” syntax errors upon import?? :slight_smile:

Even after renaming actual table as RUC it gives errors in use, and somehow refers to a RUC[1].Input - table :thinking: