I spent yesterday troubleshooting a If() formula using Lookup and boolean fields. In the end I solved it by putting all my lookup fields inside a join() function to convert their array to a text (as suggested here Convert linked field value to text? - #8 by olgatrykush and in other places)
This morning, I tried to change one of my if statements to get rid of the boolean field and now I get this error, which makes no sense to me since all my join() functions are around lookup fields:
Could lookup field have switched from lists to array overnight, or the join() function have become specific to lists?
Here is my whole formula, which was working yesterday:
OK I solved it, but I’d still love to know if there is a better fix:
Yesterday, my lookup fields were dependant on a Link to Table A.
This morning, I introduced another Link to Table B, which also happens to have the same Link and lookup to Table A.
So in the interest of simplicity, I changed my Lookup fields to only link to Table B. However, that meant that suddenly I was looking up something that had already been looked up, which I guess is where the Array was created.
I guess I could fix it by creating a Join() field in Table B to convert the lookup to text at this stage.
I don’t fully understand how tables are structured and linked together, or the purpose of lookup fields. Could you provide more details about your use case so I can try to come up with a possible solution?
Sure! I will use simplified names to make things clearer.
I have Table A which I use as reference to a bunch of other Tables. It holds DNA sequence information that needs to be used in different contexts.
I created Table B with lookups to Table A to assemble DNA sequences automatically, combining information from Table A and Table B.
Then, I created Table C with lookups to Table A to assemble different DNA sequences automatically, combining information from Table A and Table C. That was working fine.
The next day, I created a lookup in between Table C and Table B: only assemble DNA sequence in Table C from data in Table A, B, and C if an entry of Table B is linked. Otherwise, assemble the DNA sequence using solely the data provided in Table C.
Because the same information I was pulling from Table A in Table C was already present in Table B thanks to its own Lookup to Table A, I then decided to edit my original lookup fields to Table B, so that I could get rid of one “Link To Table” column, reduce user input, and simplify the flow of data: It now became A → B → C instead of an incestuous A → B → C <-A triangle).
However, that meant that instead of looking up [text] fields in Table A, I was suddenly looking up [lookup] fields in Table B, which are apparently stored in an array incompatible with join().
I fixed it by converting to text my lookup fields in Table B using join() in a [formula field], then, from Table C, looking up the [formula] field in Table B instead of the [lookup] field. It works fine but it does result in an extra column for each [Lookup] field to be re-used in another lookup.
Thanks for the explanation. Your solution is logical because currently we cannot nest lookups in formulas. Therefore, we actually need an intermediate field in the middle table.
The only way I can think of to avoid that would be using link row fields and looking up the primary field.
Something like:
TableA
name: text
TableB:
name: text
tableA: link to table A
TableC:
name: text
tableB: link to table B
f1: formula → join(lookup('tableB', 'tableA'), ', ')
In this case the formula would work without an intermediate formula field.
