Question on querying database

You can have the database concatenate fields together to make a readable name using a select statement or a view. The select statement is easy but only useful if you are using the SQL smart service. For a view, it works great, but the downside is that you can't update data in a view directly by writing to the datastore (using the mapped CDT to the view). One solution would be to add a generated column to the actual database table. The generated column could functionally concatenate the 2-3 name fields (first, last, middle, etc.) that you have in the same table. You would then use the table like you normally do, mapping a CDT to it and query entities, etc. As long as you don't attempt to update the generated column in your CDT directly I assume this will work. When you write the updated CDT (with first & last name changed, or other random columns) to the datastore and retrieve the current version the generated column would be correct.

1) I haven't tried a generated column in years, let alone in Appian, so there may be a gotcha
2) If you allow a user to change the constituent values in the CDT that you use to generate the extra name column, it is possible that until you write & retrieve the updated version from the DB, the users may see the 'old' generated value. i.e. Changing firstname='john' to 'Beth' and lastname from 'doe' to 'Something random' would mean your full name would still show as 'john doe' instead of 'Beth Something random'...until you write & retrieve the updated data.

You can have the database concatenate fields together to make a readable name using a select statement or a view. The select statement is easy but only useful if you are using the SQL smart service. For a view, it works great, but the downside is that you can't update data in a view directly by writing to the datastore (using the mapped CDT to the view). One solution would be to add a generated column to the actual database table. The generated column could functionally concatenate the 2-3 name fields (first, last, middle, etc.) that you have in the same table. You would then use the table like you normally do, mapping a CDT to it and query entities, etc. As long as you don't attempt to update the generated column in your CDT directly I assume this will work. When you write the updated CDT (with first & last name changed, or other random columns) to the datastore and retrieve the current version the generated column would be correct.

1) I haven't tried a generated column in years, let alone in Appian, so there may be a gotcha
2) If you allow a user to change the constituent values in the CDT that you use to generate the extra name column, it is possible that until you write & retrieve the updated version from the DB, the users may see the 'old' generated value. i.e. Changing firstname='john' to 'Beth' and lastname from 'doe' to 'Something random' would mean your full name would still show as 'john doe' instead of 'Beth Something random'...until you write & retrieve the updated data.