[solved] Concatenate fields in dropdown list

[marco.zanetti]

Hello,
one of my catalogs (let's call it A) has a reference to another one (B). Since no text field in the B catalog is mandatory I had to reference the ID of the record itself. This is obviously hellish when it comes to insert a new record in A, since the drop-down list looks like a list of numbers, none of which is immediately associable with a name.

What I'd like to achieve is to concatenate two fields in the dropdown list, the ID of the referenced B record and its name if there's one).
Obviously I need only to save the reference ID in table A, but whenever that ID is found I'd like to be shown the concatenation od B's ID and name. Do you know how to achieve that?

Thank you so much

[MacKP]

Hi,
with the catalog Version in the SVN and the 'Berechnen'-Field (maybe calculate field-type) it should work.
There you can summarize two fields and takte that as the reference.

regards

[marco.zanetti]

I'll try it immediately, thank you very much for this good news!

[marco.zanetti]

I was wondering: how do I update my catalog installation that I got from the extension manager with the one I downloaded from SVN?

In the SVN folder I see a bunch of subfolders including "catalog". Inside of that I have the same structure I see in my /system/modules/catalog folder. Should I copy the first on the second one? What about the database? Is it enough to run the database.sql on my db?

Thank you very much

[MacKP]

You should have a lot of modules for the catalog.. and a folder 'drivers'. You must upload the modules in the modules folder of contao and the files in 'drivers' to system/drivers. After all that you have to run the install.php for the db update. That should it be.

I hop you did an checkout of the svn and not only the download of one packege (these are to old and don't work...).

regards

[marco.zanetti]

I flawlessly installed the SVN version of catalog, thank you for supporting! :-)

How can I use the "calculate" field to get what I need? It seems to me to be a field type oriented to math calculation (the example below the 'calculation formula' field is 'Enter the SQL calculation for the field, e.g. (price*1.15)*qty.'), how can I use it to extract the data I want and - most of all - to save the right data I need into the table?

The table with the reference is

CREATE TABLE `cat_opera` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pid` int(10) unsigned NOT NULL,
  `sorting` int(10) unsigned NOT NULL DEFAULT '0',
  `tstamp` int(10) unsigned NOT NULL DEFAULT '0',
  `nome` varchar(255) NOT NULL DEFAULT '',
  `descrizione` text,
  `immagine` text,
  `deleted` char(1) NOT NULL DEFAULT '',
  `artist` int(10) NOT NULL DEFAULT '0',
  `id_opera` varchar(255) NOT NULL DEFAULT '',
  `tipologia` int(10) NOT NULL DEFAULT '0',
  `tags` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8$$

the table I'm editing is

CREATE TABLE `cat_componenti_opera` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pid` int(10) unsigned NOT NULL,
  `sorting` int(10) unsigned NOT NULL DEFAULT '0',
  `tstamp` int(10) unsigned NOT NULL DEFAULT '0',
  `componente_reference` int(10) NOT NULL DEFAULT '0',
  `opera_reference` varchar(255) NOT NULL DEFAULT '',
  `quantity` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8$$

where opera_reference is the reference to opera -> id

What I'd like to see is a combo box with a value which is the concatenation of opera->id and opera->nome, but I want to save in cat_componenti_opera->opera_reference just the ID. Can I do that with the calculate fieldtype?

Thank you

[MacKP]

You can calculate like this:

tablename: the name of the table with both fields.

calculateformel like this:
CONCAT(field1, ', ',field2)

and than you can take this new field for the selectbox.

hope this helps?

regards

[marco.zanetti]

Hello MacKP,

thank you for your answer. I'm still missing how to specify the CALCULATE options for the field.
This is what I see:
Should I specify what's the table to take data from? How can I do that? As long as I can see Contao tells me it does not know how to select the data I told him to...

Thank you

[MacKP]

You must enter the Table in the Field 'column Name' i think.. maybe this is an misspelling error there with column Name? ^^

regards

[marco.zanetti]

Uhm... I think "column name" is the place where I should put the name I want to give to this column in the database.

[MacKP]

OH, yes.. sorry.. this is the field for the new column with the calculatet entry. This fild must be in the Table with the other fields, that you will calculate.

regards

[marco.zanetti]

This doesn't solve my problem, which is the supposed query error...

I'm missing the workflow: how does the calculation (once I get to calculate the field value to be shown) be saved?

[MacKP]

The calculate Field don't save anything. It's calculatet new everytime when it will be needet.
The calculated field should be the field for the select List output in the other catalog.
Don't know whats your Problem now...

regards

[marco.zanetti]

Uhm... maybe I got it.
I create a calculate field in the table to be referenced. By concatenating ID and name I create a dynamic field which value will be something like
1 - item A
2- item B
3- item K
and so on...



in the catalog that needs to reference the first one I put a "select" field, where I put the above created field as reference, right?

When I go editing the records in this second table I don't see any calculated value



even if the values seem to be there if I go editing one of the records



Am I doing something wrong? I think the problem is in the reference to the calculated field, which is populated dinamically. Am I right? If so the calculate field can't help me in what I'm trying to achieve...

Thank you

[MacKP]

Hi,
i don't get it why it wouldn't work... sorry. maybe you can figure it out by urselve....
ore someone else has an idea.


regards

[marco.zanetti]

I managed to make it work!
I still don't get how, as soon as I understand it I'm posting here the trick! ;-)

[marco.zanetti]

Okay, this is what I did.

Suppose I have a table named A which records have an ID and a name. Sometimes they have one, sometimes both.
Records in another table named B should refer to the ones of table A.

I created a text field (called id_and_name) in table A. Using the catalogFrontendInsert and catalogFrontendUpdate hooks I managed to create an extension which
- checks if ID or name is empty
- if one is empty copies the value of the not-empty one in the id_and_name field
- if both are not-empty the concatenation of ID and name is written in id_and_name field

I then added a field in the B table, a "select" field, which relates to the new id_and_name field in table A.

When I did it I was wondering how I could then handle all the relations between the two tables. What surprised me a lot was that catalog actually does NOT save in table B the id_and_name field value, it saves the ID of the record in table A which has that id_and_name. At the same time, in all the insert-modify-read modules, it shows me the id_and_name value. This is awesome, becaus I didn't break the references between the tables and was at the same time able to show the user the id+name combo.

Simply speaking with this workaround now I:
- see the combo id_and_name as option value in table B when I want to select a record from A
- get in the table B a reference to the ID of the record in A

which is exactly what I was trying to achieve. =)