I am building a resource management tool. I have a “Project” table linked to an “Assigned Roles” table. There are several roles assigned to each project. The Assigned Roles table links the Project to the Role and staff member assigned to that role for that project.
I created a calculated field in the Project Table to display just the person assigned to the “Project Manager” role:
(if( field(‘Lookup Role’) = ‘Project Manager’, field(‘Lookup Name’), ‘’))
This calculated field is displaying the PM as expected.
In the Application Builder I am building a Master Project Page which displays some of the key Project fields in a Table Element. For some reason the calculated Project Manager field will not display the calculated “Project Manager” field in the Application builder.
The Name value for the field is “Project Manager”
The Type value for the field is “Text”
The only options I have for the Value setting for this field is:
Data source > Project Manager> [All] > ids
Which displays [object Object], [object Object], [object Object]
And
Data source > Project Manager> 0 > id
Which display is blank…There is no “Value” option showing for this field?
What am I missing? I expect this field to display like it does in the Calculated Table Field? Any ideas on how I can work around this issue if it’s a limitation?
(Note: I am not a programmer or a DB guy, I just know enough to be dangerous 
)
Hi,
A Link to table field in Baserow always contains a list of items, even if there is only one item. In that case, it is a list with only a single item. The consequence is that Lookup fields are also considered to be a list. But they are always referring to the Link to table field. This is why you only see the id.
The solution here is to make sure that your calculated field contains a text value instead of a lookup value. This can be done by adjusting the formula:
(if( field(‘Lookup Role’) = ‘Project Manager’, join(field(‘Lookup Name’),''), ‘’))
Adding the join function converts the lookup field to a text field which is much easier to address in the application builder.
This makes sense, but it still not working as expected. I am wondering if my approach is not correct.
This is my Project Role Assignment Table:
This is my Project Table that links to the table above.
So the formula you suggested above is showing two names, and is still showing a blank in my app as shown below?
Although there are several roles assigned to each project, I only want to display the name of the person assigned as the Project Manager. As you can see, I have two lookup fields in the Project Table. One for the assigned Roles, and the other for the staff members assigned to the respective roles. These lookups are used to calculate who is assigned to the Project Manager Role.
The goal is to display the name of the person assigned to the “Project Manager” Role in both the Project Table and the Homepage of the App.
Any ideas on why this formula is not working or for a better approach?
I found that the concat function does display the Project Manager name properly, but it still does not allow me to display this field in the Application.
I finally figured out a solution, although it seems a bit convoluted.
I created a new Project Field called “Project Manager” and used the join command on the calculated field.
join(field(‘Calc_Project Manager’),‘’)
And I am now referencing that field in the Application app…
So I needed five fields to make this work…
- The original table link field
- A lookup field for the role
- A lookup field for the person assigned to the role
- A calculated field to identify just the Project Manager.
- Finally a new field with the join command so that the I can pass the name assigned to the PM to the Application.
I thought it would be best practice to put the roles and the assignees in a separate table, but it now seems it would make sense to use a single select field and assign roles directly in the Project database. Unless I am missing another solution?
You can have less fields by using the filter() in the formula field and nesting functions. With the filter() function, you filter all the role assignments where the role is Project Manager. Wrapping the join() function around it converts the list of values (probably a list of 1 item) to text.
Using a single select field is also an option. It depends on the information you want to store. If there is a lot of associated information with the role like: hourly rate, user rights,… it is a good practise to store the roles in a separate table. A single select field is easier to use if you only need to assign a role without any related information.
1 Like
I am experiencing a problem that I am hoping you help with.
This calculated field was displaying just the value of the “Role Assignment”, but now it is displaying an array [{“id”: 2, “value”: “Michael Tranchina”}]
As far as I can tell nothing has changed?
I would use the filter() function instead of working with the if() statement.
In my case, I have a table Project (as you see in the screenshot) and a table with team members. Team members are linked to the projects. The Projects table has 2 lookup fields:
- name of the team member
- role of the team member
My formula field shows the name of the team member that is the project manager: filter(field('Team name'),field('Team Role') = 'Project Manager')
You can wrap the result with the join() function if you need the result to be plain text
1 Like
