I think I have a solution to your problem. You need to change your list field in the works table to use  'Get options from MySQL query (advanced)' type. Then you'll need to create a MySQL statement that will retrieve values from the creations and performers table and combine them into one value. It should look something like this:

SELECT c.num, CONCAT(c.title, ' ', (SELECT p.first_name FROM <?php echo $TABLE_PREFIX ?>performers p WHERE p.num = c.performer))
  FROM `<?php echo $TABLE_PREFIX ?>creations` c

This is just example code, so you will have to make a few changes to get it working.

So this statement selects num values from the creations table as well as the associated title combined with a second select statement from the performers table. The MySQL CONCAT function is used to combine multiple strings into one string. The MySQL select statement for the performers table will return a string with the performers first name, using the num value stored in the performer field in the creations table.

Let me know if you have any questions.

Thanks!

Greg

I will try to make this clearer, sorry.

I first enter the first_name and the name of the performers in the "performers" table.

In the "Creations" table, I enter date, venue, etc. and I can then choose a performer in a list (multi check list) that comes from the "perfoermers" table.

I then create a work in the "works" table, where I can associate a creation to it (drawn from the "creations" table). In that multi check field I was able to show the date and city but the performer is shown only by its "num" and not first_name, name. See attached file.

In clear;)

I am trying to associate a creation that has one or more performers to a work.

To help us clarify exactly what you are looking for, are  you trying to associate a work with a creation, just using performer names as a label, or are you trying to associate a work with a perform that has a creation?

Let me know and we'll see about designing a solution for you.

Thanks!

This doesn't concern the front end but only cmsAdmin.

I have 3 tables:

"works", "creations", "performers".

in the "creations" table, I have a list fields where I can select "performers" (by first_name and name).

in the "works" table, I would like to be able to select from a list that comes from the "creations" table.

it works but I'd like to show the "performers"  first_name, and  name. For now it only shows  "num" field, which doesn't help.

Thanks for your help