Hi,
If I format a column either as Select (multiple values) or Look up from table and I need to copy/past certain value/data to that column, Coda doesn’t recognise the text (it is ok to some extent) but should give me an option to create straight away the new value as soon as I past the value in the cell.
Hello -
I understand you want to know why you can’t post the data directly as a new data row in the linked table, like we can in some other apps, and have the text convert over as a new entry in the other table. If Coda allowed this it would break the data rather than keeping it “living” but maybe it will become a popular request…
In the world of good database management, best practices are to avoid at all costs manually manipulating the data, but instead make forms, and filters, or mark rows as “deleted” instead of actually deleting them. Over time this allows you as a database manager to have systematic control, keep organized, eliminate sparse data (rows with missing info), avoid manipulation of the data by bad code, and backtrack history. This prevents contractions and bugs.
So if you have a lookup table, it would be standard practice to make that entry in that second table, and then select it from the lookup list in the first. You can copy and paste the lookup value, which isn’t text anymore, but becomes an actual reference to the data. In a sense it’s alive.
Hey Michael, thanks for the feedback but I have some concerns about that:
When fine grained permissions are implemented by coda, you will simply “lock” the column and you are done. Problem solved. The column is protected and nobody can create inconsistency
Think from a Data Entry perspective: You have a table that looks up different other tables. Now, if you inserted a new value in each single table, you would take ages to complete the data entry (i’m talking about copy/past actions, because currently Coda allows to create new value straight away when editing)
With the 2) in mind, you can simply provide the user with an option at pasting time that says: "Would you like to create new entries in lookups tables? (yes or no) with a default answer to no (like it happens to copy/past data from one section to another now)
Now the effect will be that the data entry (copy/past actions) will be more effective and efficient and waiting for 1) that will fix your concerns, the 3) is absolutely a right balance and mitigation…
Lastly, Coda is not a “Database per se” but is a much more flexible product… and this feature will enhance productivity and will provide the right balance between consistency and speed!
Are you saying you want faster speed transposing large blocks of data from prior tables? It sounds like you should look into bulk formatting the data, importing, or applying a formula to speed things up. If you are pasting in chunks of 5-10 rows, I would see it the same as a bulk data operation.
Sometimes there are brilliant solutions that require knowledge too, for example, maybe just putting " " around the text entry or an @ will convert it to the right datatype so that it creates the lookup… i’m not sure.
Imagine you have other data sources (excel sheets, csv, etc…) and that data doesn’t have other separated tables for certain columns (say Company like my example above).
Now, if you wanted to structure your data (with many different tables for certain entities), you would deal with each single table and pre-populate them first (we are talking about speed at data entry time).
What i’m trying to say here is that if we had an option that "auto creates entities/values in other lookups tables at past time and in a safe way) it would simplify a lot this process and speed up things!
I guess I would need to learn more about that specific situation as I did not get that from the first post. I am willing to bet you there is a way to parse the data (Be it comma, space bar, etc) to extract the list of types that need to quickly populate the table.
Have you seen the Unique() function? I use that to populate types in my tables
I don’t want to oversimplify the discussion, but I want to point out to others searching for this that Michael’s workflow works. Instead of formatting the column as lookup and then pasting data (does not work), create the column with the data, and then switch the column format to lookup - this automatically creates all references!
I think allowing pasting plain text references like @reference would be a great piece of UX, powerful and self-explanatory, but until then populate-first method works.
Sorry for reviving this post after so much time but I feel my use case is similar to this one.
Long story short, I have a document that helps me to add ingredients to my pantry and then match those ingredients to the recipes I have added to another table.
Problem:
Adding the recipes is a tedious process because I have to select all the ingredients on a select list.
As mentioned by @Michael_Armani, I think I’m already doing it as suggested. I have a table called Ingredient Database (where all the ingredients are stored) and I’m selecting the ingredients that match the recipes on a Select List.
By using chatGPT, I’m already able to convert a list of ingredients from a recipe I find online only to keep the name, so it’s closer to the name on my Ingredients Database table.
Is there a way to make a conversion of a comma separated text list of ingredients so it selects the appropriate Ingredients from the Select List connected to the Ingredients Database table?
I understand that this could be bad if the data is not added manually but it’s the real bottleneck of this document. I have few recipes because adding recipes is tedious.
Here are the databases of the document, if someone want’s to take a look: Pantry & Recipes (Coda Community)
If you have any idea on how I could optimize this process, please let me know. If you feel this deserves another post, please let know and I’ll delete and create the new one.
Thanks to you all!
@Geiras_ES did you ever find a solution to this matching problem?
Hey @Andrew_Farnsworth,
I haven’t found a solution to the matching problem yet and I’m still doing it manually for now. Maybe AI will help on the future, but for now, this is the bottleneck of my entire process.
Hi @Geiras_ES 
!
Maybe this (or similar) could help you here
In the sample you’ll find below, you’ll see 3 tables :
[Add Recipe]: It’s just a one row helper table used to convert a commas separated list of ingredients into a list of text values Coda can use and manipulate.Ingredients: To store all ingredientsRecipes: To store the recipes (
)In the table [Add Recipe], you’ll see 5 fields:
Recipe: A simple text field to add the name of the recipe you wish to add to the table Recipes
Commas separated list of ingredients: A simple text field where you can paste a commas separated list of ingredients (as its name implies )
Ingredients already in the table Ingredients: A lookup field from the table Ingredients. It lists all the ingredients present in the commas separated list of ingredients which are already in your table Ingredients and retrieve the rows using mostly this formula:
thisRow.[Commas separated list of ingredients].Split(",").ForEach(
CurrentValue.Trim().WithName(I,
Ingredients.Filter(Name.ContainsText(I,true,true))
)
).ListCombine()
where :
thisRow.[Commas separated list of ingredients].Split(",") splits the list by the commas, which now outputs a list of text values and each text value (ingredient) is represented as a CurrentValue.
Then, using ForEach(), each CurrentValue (so each ingredient in the previously split list of ingredients) is trimmed to avoid potential and unnecessary white spaces and given the name I with WithName().
All, that’s left here is to take the table Ingredients and keep only the rows where CurrentValue.Name contains the text currently stored within I using ContainsText().
(Note that ContainsText() take 2 interesting optional input which are both set to true here : ignoreCase and ignoreAccents. So, however an ingredient is written in the initial commas separated list of ingredients, if it exist in the table Ingredients, Filter() should find it )
The very last step here: [ ... ].ListCombine() is just to avoid having to deal with unexpected List of Lists down the line, somewhere 
Missing Ingredients in the table Ingredients: It’s a simple text field which will extract, from the commas separated list of ingredients, the ingredients that don’t exist yet in the table Ingredients using this formula :thisRow.[Commas separated list of ingredients].Split(",").ForEach(
CurrentValue.Trim().WithName(I,
If(
Ingredients.Name.ContainsText(I,true,true),
"",
I
)
)
).ListCombine()
It works mostly as the previous one does except that if the whole list of ingredients from the table Ingredients (Ingredients.Name) contains the text value currently stored within I (so if the ingredient is already in the table), it will output Blank ("") . Otherwise, it will output the text value currently stored within I. And to get rid of the Blank ("") values in the final result, the list of text values is flatten with ListCombine().
And last but not least
[Add Recipe]: A 3 actions button.The 1st of its action is to add to the table Ingredients each potentially missing ingredients (1 row, 1 ingredient) from the field [Missing Ingredients in the table Ingredients]. While this action is running, the brand new ingredients should be picked up but the lookup [Ingredients already in the table Ingredients] .
If there’s no “missing” ingredients to add, that action won’t do anything and move to the 2nd action.
The 2nd action is to add a row in the Recipes table with the various values from the fields in the table and then open this brand new row in a modal view where the Ingredients lookup field in the table Recipes is displayed as a subtable so you could, for example, correct the names of new ingredients or add more infos …
The 3rd action then cleans up the only row in the table Add Recipe .
The whole action formula within this button is this one :
RunActions(
// Action 1: Adds potential missing ingredients (as they are written) to the table Ingredients
If(
thisRow.[Missing Ingredients in the table Ingredients].IsNotBlank(),
thisRow.[Missing Ingredients in the table Ingredients].Filter(CurrentValue.IsNotBlank()).ForEach(
AddRow(
Ingredients,
Ingredients.Name,
CurrentValue
)
),
""
),
// Action 2: Add a recipe to the table and find the ingredients in the table Ingredients
AddRow(
Recipes,
Recipes.Name,
thisRow.Recipe,
Recipes.Ingredients,
thisRow.[Ingredients already in the table Ingredients]
).OpenRow(),
// Action 3: Clear thisRow
thisRow.ModifyRows(
thisRow.Recipe,"",thisRow.[Commas separated list of ingredients],""
)
)
This is all just an idea… Something to explore
(Sorry for the length )
Hello @Pch
Thank you for creating the sample document. I took a look at it and it helped me understand how to break down operations in my own document.
Your example works perfectly if the list of ingredients is comma-separated (which I can achieve by asking AI to remove measurement units).
I will try to merge some of the logic and formulas from your example into my own document.
However, I foresee some challenges already, for example with similar ingredient names as different recipe authors use different words.
I’ll report back after testing the learnings from the sample document
you provided.
Once again, thanks! I wouldn’t be able to come up with something like that on my own! Superb!
Cheers!
My pleasure @Geiras_ES 
!
Even though this is not a complete solution to your issue, I’m really glad to know that it helped you a little 
!
It really just was a possibility, nothing more … and it could be improved
(I sadly had not enough time to push further and be more precise )
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.