Hello!
A bit new to Baserow but quite familiar with Airtable. I’m trying to use the content of a linked field in a concatenate formula. Basically, I just need the linked field value in a text format.
For the life of me, I can’t figure out how to do that in Baserow.
I’ve played with the lookup & totext formula but that’s outputting the full array…
Any clue? Thx for your help!
Hi,
It should normally work with
concat(field('linked field'),field('other field'))
But If you combine formula’s and lookup fields, you might sometimes end up with the entire object of the linked field instead of just the text. Can you broadly outline the structure of your database? Especially which fields are involved in the concat() formula.
Hi Frederik,
In this instance I have 3 bases:
In the table Occupation locaux (office space occupation), I want my first column (Tranche horaire) to be something along the lines of: Weekday time - persons (office). Fields “Persons” and “Offices” being either linked fields or linked fields lookups.
Right now, it’s doing a number of things I don’t want:
What formula am I missing to solve this?
Hi,
I quickly regenerated your database structure with a table Staff
A table offices
And a table Occupation. This table links staff members to an office on a certain time of the week. This time of the week consists of 2 single select fields, being the Day (Jour) and Hour (Horaire). I also added a lookup field for the initlas
The field Name uses the following formula which seems to work:
concat(left(totext(field('Day')),2),' ',totext(field('Hour')),' ',field('Initials'),' ',field('Office'))
I needed to convert the single select fields to text so that I can use them in the concat() function.
If you still get this error, you need to take a look at how the name of the Bureaux is constructed because it looks like it is also a concat of multiple fields.
You cannot filter on lookup fields, but there is a workaround for it. Change the type of the Initials field to formula and use this formula:
join(lookup('Staff','Initials'),' ')
This merges your list of results into a single string. You can know filter this field by using the contains filter
Hi Frederik,
Thank you for taking the time to reproduce my issues.
The issue with the array display persists, likely due to the fact that the field Bureaux is a concat field and needs to remain one. What workaround can I find to be able to use concat fields in concat formulas in other tables?
I see the field Name in your example remains a linked field (why?) and that it keeps creating several linked fields when there are more than 1 initials (why?), what are my options to prevent this from happening?
@olgatrykush do you know of these issues and are there plans to allow using the concat formula on linked fields and have the output be plain text? Also, any plans on having the functionality to filter on lookup fields?
Cheers
Hi,
I had the problem of the array displaying in the past and it can indeed occur when you are using single select fields or linked fields in formula’s. However, I can not simulate it for now.
The field Name still has a grey background because the formula uses the field Office which is a linked field. Even if a linked field only contains one item, it is still considered as a list of items which brings us to your second question.
The reason why more than one linked field is shown is because a linked field is always a list of items. So if you refer to a linked field in a concat() function, it will execute this function to all items of that list.
A solution is using the join() function on the lookup field. I changed the field Initials to a formula
join(lookup('Staff','Initials'),' ')
This changes the field Initals to a text field. Referring to that field in the concat() function results in showing only a single item.
You can use the same method for the Office field. Create a lookup field and join the values. Use that lookup field in your concat formula. The result is as follows:
You can hide the new field Office name if it is disturbing the overview of your data.
Hello @gabrielle, getting back to you on these questions:
To use the concat formula with linked fields, it is necessary to first use the join function as Frederik explained above. I’ve checked with the team, and currently, there are no plans to allow the use of the concat formula on linked fields, but I’ll add it to the list of discussions as a potential new feature idea.
Yes, we do plan to make lookup fields filterable. Here’s an issue to track the progress: Lookup Fields/Formulas should be filterable/colorable/aggregatable in the grid view (#676) · Issues · Baserow / baserow · GitLab.
I believe I’m running into a similar issue, here is my formula:
concat('[', field('sellStrategy'), '] Sell ', field('multiplierSellPercent'), '% of ', totext(field('positionPurchasedCoins')), ', when coin price reaches x', field('multiplierGoal'))
This yields "[Multiplier] Sell 50% of [{""id"": 16, ""value"": ""174.72 XRP""}], when coin price reaches x2.00"
The positionPurchasedCoins is a lookup of a formula field.
I wish there was a function to extract the “value” when something like this happens. (jsonpath)
Hi,
Welcome to the community!
A lookup field is actually a list of values, even if you only have a single value in the list. You can use the join() function instead of the totext() function to convert the field to a single text.
