Concat within a table

Hi @rbuschmeyer,

Do you see any errors when you tried to run the query in the Query Builder?

Can you try this instead?

SELECT LastName, Firstname, CONCAT(LastName,", ",FirstName) as DisplayName
FROM People
WHERE ID > 0;

We tested it using our own tables here: https://share.getcloudapp.com/Z4um20Oo

Let us know how it goes. ??

Should I use this code in phpmyadmin or in the Design Table dialog?

Kim, in the SQL Query Editor, my original command (without the semi-colon) works perfectly. The issue is getting that query to work by issuing the concat and inserting that data into the Name field. (I changed the name of the column to “Name” instead of “DisplayName.” The new column name works file in the Query Editor.

What I get is the following error when attempting to use in the Data Designer:

The following ALTER TABLE statement failed

ALTER TABLE People MODIFY COLUMN Name text NULL DEFAULT SELECT LastName, Firstname, Concat(LastName,”, “,FirstName) as Name FROM People where ID > 0;

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘SELECT LastName, Firstname, Concat(LastName,”, “,FirstName) as Name FROM Peop…’ at line 1

Is there some type of prefix or container that I need to use to make SQL run the command?

Follow-up… the concatenated Name filed needs to be visible in child tables so that it can be selected. That’s why this is critical.

I can also use the following query to actually SET the data within the column, I just cannot get it to work automatically when the record is created.

Update People
SET Name = CONCAT(IFNULL(LastName, ''), ', ', IFNULL(FirstName, ''))

Hi?@rbuschmeyer,

not sure about your requirement, but I guess you could check the table options: VIRTUAL_GENERATED, DEFAULT_GENERATED, VIRTUAL_STORED.

See Extra (? icon) in WPDA -Data Designer page.

Hope this helps,

Regards.

Hi @measy,

Thank you for your contribution!

Hi @rbuschmeyer, thanks for the updates!

Could you tell us more about the table and how it’s going to be used? (ie. in a data project where data is inserted, etc.) . The more detailed it is, the better! ??

We need more information about your scenario so we can provide the proper solution.

For example, if you simply wish to be able to use the column for other tables, creating a view might suffice?

We’ll wait for your response. ??

Ok, maybe this is a lack of understanding on my part with regard to how the plugin works vs. other low-code solutions.

Here’s my scenario:

I have two tables, Families and People (People is the Child table)

I need to display the name field (from People) AS LastName, Firstname in the sub-table of Families. I also need to utilize this field in other tables like in a similar fashion.

I believe what you’re telling me is that I can create a “view” to use in these other tables, but I have no clue how to create that within the plugin or how to utilize it.

• This reply was modified 2 years, 1 month ago by rbuschmeyer.

Hi @rbuschmeyer,

You can create a VIEW or execute any queries possible by going to WP Data Access > Query Builder. Here’s a demo: https://share.getcloudapp.com/QwujJ5yj

You can also read more about the Query Builder here: https://wpdataaccess.com/docs/query-builder/writing-queries/

Are the tables (both Families and People) already populated, and will need to be displayed only, or are they going to be populated by means of manual entry (by Data Form / Data Project)?

We’ll wait for your response. ??

Actually, both. We would like to use it for data entry as well as filtering/reporting.
This database will also track various times of service and other things, so I think it would be best for me to create a join table and a filter report from there.
I think what I was trying to accomplish was to have the “display name” available in the Child table tab views on the templates, but that’s a minor inconvenience.

Thanks for helping me through this.

Hi all,

May I suggest to rename column name? Name is a reserved word and can lead to issues.

In reports you can use a view as Kim suggested. This does not work for updates. MySQL has very limited support for updatable views (see link below), but even if a view were updatable, the plugin will not support it as it expects a primary or unique key on the base table. As an alternative you can add dynamic hyperlinks to one of more joined tables.

Does this help?

Thanks,
Peter

https://dev.mysql.com/doc/refman/8.0/en/view-updatability.html

Thanks, Peter. I think it does.

I think what we will do is use Templates for data management and then the View for people who need to just filter data.