I tried to solve a problem but after tens of tries, I was about to give up! May be you can help or tell me that it’s not possible
Let’s say I have two tables TableA and TableB.
TableA has primary field Name and two other fields City and Role.
TableB has primary field City and two fields Manager and Dev.
I add a new entry in TableA : [Alex, Tokyo, Manager].
Is there a way to automatically report “Alex” in the field Manager of the entry Tokyo in TableB?
In other words, I would like the field “Manager” of each city entry of TableB to be the list of the primary fields of each entry of TableA that have Role value equals to “Manager” and City value equals to “Tokyo”.
I tried to use the filter function combined to lookup but it looks like it can only read fields of one entry.
The table Cities had originally 3 fields. ‘Name’ and ‘Dev’ are just single text fields. The field ‘Manager’ will need a formula. The field ‘Staff’ was automatically added when I linked the field ‘City’ from the Staff table to the Cities table.
I added the following formula for the field ‘Manager’. filter(field('Staff'), lookup('Staff','Job role') = 'Manager')
The filter() function takes two arguments. The first one is the field I want to filter. In this case this is the field ‘Staff’. The second argument must result in a boolean value (true or false). So, if the job role of the staff member is equal to Manager, it should be shown, otherwise not.
Baserow does not allow to use multiple select fields in a formula. In that case, I suggest you create a new table with job roles and link to multiple rows of that table
Yes, but it includes some extra fields. First, I add a formula field to the Staff table that show the id of each row with the function row_id
Staff id: is a lookup field that shows the corresponding id’s of the staff members that work in a certain city
Manager: shows the id of the staff member that has the role of manager in that city. The formula is filter(field('Staff id'), lookup('Staff','Job role') = 'Manager')
Link to staff member: is a formula field that contains a link to the record of the staff member with the following formula: button(concat('https://baserow.io/database/59559/table/157257/247411/row/',field('Manager')),lookup('Staff','Name')). Please change the id’s of the database, table and views
Thank you very much for your precious help @frederikdc .
I tried to merge the 3 fields you propose to create in only one. It’s straightforward to merge the first two fields but I didnt manage to merge with the third formula. I have the following error : Error with formula: input number 2 to function concat must be directly wrapped by an aggregate function like sum, avg, count etc.
What I understood is that the error comes from the cases where I have two managers in on city. When splitting into different fields, the third formula is applied on each element on the Manager field whereas is one formula, it doesnt work ? Am I correct? Do you see any way to merge those fields into one?
Using this method, I end up with a link in a new tab. Is there a way to end up with a link to table, so that the link just opens the pop-up to the corresponding entry?
Thanks for your answer!
Sorry but I am not sure about your answer for the merging of the three fields. Have you merged them?
And for the pop-up, that is not currently possible to create a « link to table » using a formula, right? Is that a feature you have discussed? That would actually be great because in our case, that’s not very user-friendly to switch to a new tab to get the information!
Thanks
I merged only 2 fields: the base url and the id of the item.
You cannot create a “link to table” field with a formula. The link to table indicates the datatype of the column, not the items that should appear in it.
