Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Sep 28, 2024 10:17 AM
Hi, I am having trouble creating an automation to update a record in a table. When I try to set up the "Find Record" step, I'm getting an error that "cannot assign list of string to string" and I don't know how to proceed or how to fix my data set up in order for the automation to work.
The issue (I think) is that in the table I'm trying to update has a linked field which is the unique identifier I need for the automation to find the value I want to add to the table I am updating.
I'll try to explain the data structure and what I'm trying to do.
Table 1: Contracts
Table 2: Contract Items (this is a list of "products" that are associated/organized with Contracts for clients in Table 1)
Table 3: Books (this has an association with Contracts (from Table 1) to a book). One book can have several contracts. One contract can have several books.
Table 4: Results (Products from Table 2 is the primary organizing principle for this table, and this is the one I am trying to update by running an automation to FIND the book (table 3) that is associated with the Contract from which the Contract Item is drawn, which is the unique item in this Results table. Multiple issues here: There could be more than one book associated with a contract (although this is rare, it does happen). The other issue is that in the Results table, I've set up the BOOK field to be a linked field using the Contracts Table as the organizing principle. So right now, the "Book" field in Results is filtering to find only books that are associated with the Contract number. But the Automation isn't letting me set this up to automatically add the book when a new record is created. If I am able to do it manually, it seems like I ought to be able to do it with an automation. But I'm stumped.
This shows how I'm able to add a book manually (and the results for my selection are filtered based on CONTRACT, showing only books that are associated with the Contract for which the Item in the record is associated).
Now, here are the key fields I'm using to try to associate the BOOK with the PRODUCT (which is a "Contract Item") based on the CONTRACT name, which is a look up field based on the Product linked field.
I am sorry I realize this is not very clearly written out -- I'm having trouble explaining it without having to describe the entire data structure of my Base.
I'd be so grateful if someone could help me figure out how to build the automation so I can get these book titles automatically added to a new record. I'm using the trigger "When record matches the conditions 'If Product is not empty'" and then trying to use the Find Records action. The idea would be that then I'd create the "Update Record" action based on the results from Find Records. I want to add the BOOK to the record in the Results table.
If this isn't possible, we can just add books manually, but I am really trying to reduce manual entry when it can be avoided.
Thanks!
Sep 28, 2024 09:13 PM
Hi,
the problem is data type mismatch. Link and lookup cell values are list of some data. Link contains id and name of a linked record (name means value of primary field). Lookup contains value from other field, defined in lookup settings. When you have 3 links in a cell, you will have 3 lookup values.
It's Array, or list of values. Also, result of Find Records usually an Array too.
Sometimes, you can try to add formula field, where you convert array to usual string.
It's done by concatenate or with add to empty string.
CONCATENATE({Field})
{Field}&""
But it seems like such method will cause problems when there are several values
Other, and more correct way is to change automation logic to make a task doable.
Also, you can add lookup of Book IDs into the Contracts table. Then add this data to a Product table, but I doubt about usual lookup
, maybe Rollup with ARRAYUNIQUE(values).
You should try several options and choose which is better. Then when you have a column of books IDs in a Product table, automation will be easier - just put the list of these IDs into a linked field. You can do this by copy-pasting whole field of IDs as well.
Oct 03, 2024 11:50 AM
Thank you for your reply. I can tell from your answer that I am out of my depth here. Sadly, I do not really understand the options you're giving, or how to implement them. 🤔 I need to learn more, i think!