Linked records called such as L2 - 1 turn into [{“id”: 2, “value”: “L2 - 1”}]
I expected them to stay the same, so that L2 - 1 stays L2 - 1. Anyone any idea?
Currently I use replace functions. But it starts to add up lot of nested replace functions as I scale up the amount of records. I really hope there’s a smarter way to get around this.
Hi @360Creators ,
Unfortunately I believe you might be doing a Lookup of a Lookup which results in this and is a known bug: Lookup of Lookup fields appear as JSON and cannot be used in many functions (#1027) · Issues · Bram Wiepjes / baserow · GitLab. Currently the main workaround is to use aggregation functions in the intermediate table to convert a lookup field list of values into some single piece of text/number etc, and then look that up. This way you never end up with a nested “lookup of a lookup” and this JSON coming out.
Thanks @nigel!
Where do I find that aggregation function in the intermediate table? I’d love to try that.
@360Creators So I meant to make intermediate formula fields which use formula functions like sum
or join
to take a lookup value (which is a list) and combine it all into a single value. This way you never end up with a list of lists (which causes the json bug) at the very end of your chain. A worked example would be:
We have 3 tables:
- Locations
- Trips
- Years
Now say in the Years table, I want to have a formula field showing all of the Lat/Lng co-ordinates I visited in that year. Here if I used a lookup of a lookup you end up with the bug like so:
So one work around for this, is in the Trips table, I add a new formula field, which is all of the LatLngs joined into a single value. This way it won’t show up a list of values anymore, but a single value like so:
And now I lookup in the Years table this “LatLngs joined” field. And now i’m doing a lookup of a normal formula field, which doesn’t contain lists, and so I avoid the bug:
Hopefully this makes sense! Let me know if it doesn’t
Thank you very much!!
Totally got it now
I followed this process and was able to solve a similar issue, which is fantastic, so thanks for that. I have bookmarked it as I am sure I will need to refer to this again. I just have one small question. Is it possible to sort a returned list of values in a lookup any way? Minor thing, but if its possible then I will be super happy.
So for example, I want the Comments From list to be in alphabetical order, so the derived Commented list is in that order.
This was the formula I used btw in case someone is trying to display it the same way.
Hey @DataGecko, unfortunately, that is not possible at the moment.
Ok, thanks for that.
I’m new to Baserow and generally loving it so far. However I’ve hit a problem that seems to be linked to this thread. I’m using Baserow to calculate prices for my holiday let business. I’ve got data in a number of tables, aiming to minimise duplication of the data. I’ve got a problem pulling a field from one table so I can compare it to another field. Although the field “looks” like a number to my eye, Baserow is treating it as an array and therefore errors trying to compare it to another number to find the maximum of the two. It also errors using the greater than function.
This is an example of the field
“[{”“id”“: 1, ““value””: 2493.60000}]”
using formula: concat(field(‘Full Min’),‘’)
Has anyone got around this issue for a numeric value?
Hey @jacki, could you please share the field types you’ve used for all fields referenced in your formula?
Hi @olgatrykush,
Full Min is a formula with a number output. It references the following fields:
Full Override - number
Full Tgt - formula with number output
Full Tgt references:
Full BE - formula with number output
Full BE references:
Changeover incl VAT - formula with number output
WE night incl VAT - formula with number output
MW night incl VAT - formula with number output
I could go on… I’ve put up the calculation over a number of field and tables.
It was working fine until I wanted to compare the result of Full Min with another number.
Hi @jacki, I apologize for the delayed response. I’ve replicated a similar case, take a look:
Try this formula instead:
concat(field('Full Min'), "")
Is it what you’re trying to achieve?
Hi Olga,
No, what I actually want to do is:
if(field(‘Full Min’)<field(‘Full Ref’),field(‘Full Ref’),field(‘Full Min’))
But I get the message:
Error with formula: argument number 2 given to operator < was of type number but there are no possible types usable here.
Since ‘Full Min’ is resolving as an array not a number this isn’t working. Do you know how I can get around it?
hey @jacki, try using tonumber
function:
if(tonumber(field('Full Min')) < field('Full Ref'), field('Full Ref'), field('Full Min'))
Let me know if that works!
Sadly not
This time I got:
Error with formula: argument number 1 given to function tonumber was of type array but the only usable type for this argument is text, argument number 2 given to operator < was of type number but there are no possible types usable here.
It’s difficult to help without replicating the complete structure of your table. But based on the error message, we can try this option:
if(totext(field('Full Min')) < totext(field('Full Ref')), field('Full Ref'), field('Full Min'))