Results 1 to 17 of 17

Thread: [solved] Concatenate fields in dropdown list

  1. #1
    User
    Join Date
    01-29-13.
    Posts
    64

    Default [solved] Concatenate fields in dropdown list

    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
    --
    Marco
    ---------
    Triste e' l'uomo
    che ama le cose
    solo quando si allontanano.
    (Baolian, libro dei pensieri Baol, I, vv. 1240-1242)

  2. #2
    User MacKP's Avatar
    Join Date
    06-19-09.
    Location
    Duisburg (Germany)
    Posts
    211

    Default Re: Concatenate fields in dropdown list

    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
    Mediendepot Ruhr
    For real-time chat the (inofficial) Chatroom in IRC:
    -> irc.freenode.net #contao | irc.freenode.net #contao.de

  3. #3
    User
    Join Date
    01-29-13.
    Posts
    64

    Default Re: Concatenate fields in dropdown list

    I'll try it immediately, thank you very much for this good news!
    --
    Marco
    ---------
    Triste e' l'uomo
    che ama le cose
    solo quando si allontanano.
    (Baolian, libro dei pensieri Baol, I, vv. 1240-1242)

  4. #4
    User
    Join Date
    01-29-13.
    Posts
    64

    Default Re: Concatenate fields in dropdown list

    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
    --
    Marco
    ---------
    Triste e' l'uomo
    che ama le cose
    solo quando si allontanano.
    (Baolian, libro dei pensieri Baol, I, vv. 1240-1242)

  5. #5
    User MacKP's Avatar
    Join Date
    06-19-09.
    Location
    Duisburg (Germany)
    Posts
    211

    Default Re: Concatenate fields in dropdown list

    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
    Mediendepot Ruhr
    For real-time chat the (inofficial) Chatroom in IRC:
    -> irc.freenode.net #contao | irc.freenode.net #contao.de

  6. #6
    User
    Join Date
    01-29-13.
    Posts
    64

    Default Re: Concatenate fields in dropdown list

    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

    Code:
    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

    Code:
    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
    --
    Marco
    ---------
    Triste e' l'uomo
    che ama le cose
    solo quando si allontanano.
    (Baolian, libro dei pensieri Baol, I, vv. 1240-1242)

  7. #7
    User MacKP's Avatar
    Join Date
    06-19-09.
    Location
    Duisburg (Germany)
    Posts
    211

    Default Re: Concatenate fields in dropdown list

    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
    Mediendepot Ruhr
    For real-time chat the (inofficial) Chatroom in IRC:
    -> irc.freenode.net #contao | irc.freenode.net #contao.de

  8. #8
    User
    Join Date
    01-29-13.
    Posts
    64

    Default Re: Concatenate fields in dropdown list

    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
    --
    Marco
    ---------
    Triste e' l'uomo
    che ama le cose
    solo quando si allontanano.
    (Baolian, libro dei pensieri Baol, I, vv. 1240-1242)

  9. #9
    User MacKP's Avatar
    Join Date
    06-19-09.
    Location
    Duisburg (Germany)
    Posts
    211

    Default Re: Concatenate fields in dropdown list

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

    regards
    Mediendepot Ruhr
    For real-time chat the (inofficial) Chatroom in IRC:
    -> irc.freenode.net #contao | irc.freenode.net #contao.de

  10. #10
    User
    Join Date
    01-29-13.
    Posts
    64

    Default Re: Concatenate fields in dropdown list

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

    --
    Marco
    ---------
    Triste e' l'uomo
    che ama le cose
    solo quando si allontanano.
    (Baolian, libro dei pensieri Baol, I, vv. 1240-1242)

  11. #11
    User MacKP's Avatar
    Join Date
    06-19-09.
    Location
    Duisburg (Germany)
    Posts
    211

    Default Re: Concatenate fields in dropdown list

    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
    Mediendepot Ruhr
    For real-time chat the (inofficial) Chatroom in IRC:
    -> irc.freenode.net #contao | irc.freenode.net #contao.de

  12. #12
    User
    Join Date
    01-29-13.
    Posts
    64

    Default Re: Concatenate fields in dropdown list

    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?
    --
    Marco
    ---------
    Triste e' l'uomo
    che ama le cose
    solo quando si allontanano.
    (Baolian, libro dei pensieri Baol, I, vv. 1240-1242)

  13. #13
    User MacKP's Avatar
    Join Date
    06-19-09.
    Location
    Duisburg (Germany)
    Posts
    211

    Default Re: Concatenate fields in dropdown list

    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
    Mediendepot Ruhr
    For real-time chat the (inofficial) Chatroom in IRC:
    -> irc.freenode.net #contao | irc.freenode.net #contao.de

  14. #14
    User
    Join Date
    01-29-13.
    Posts
    64

    Default Re: Concatenate fields in dropdown list

    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
    --
    Marco
    ---------
    Triste e' l'uomo
    che ama le cose
    solo quando si allontanano.
    (Baolian, libro dei pensieri Baol, I, vv. 1240-1242)

  15. #15
    User MacKP's Avatar
    Join Date
    06-19-09.
    Location
    Duisburg (Germany)
    Posts
    211

    Default Re: Concatenate fields in dropdown list

    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
    Mediendepot Ruhr
    For real-time chat the (inofficial) Chatroom in IRC:
    -> irc.freenode.net #contao | irc.freenode.net #contao.de

  16. #16
    User
    Join Date
    01-29-13.
    Posts
    64

    Default Re: Concatenate fields in dropdown list

    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
    ---------
    Triste e' l'uomo
    che ama le cose
    solo quando si allontanano.
    (Baolian, libro dei pensieri Baol, I, vv. 1240-1242)

  17. #17
    User
    Join Date
    01-29-13.
    Posts
    64

    Default Re: Concatenate fields in dropdown list

    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. =)
    --
    Marco
    ---------
    Triste e' l'uomo
    che ama le cose
    solo quando si allontanano.
    (Baolian, libro dei pensieri Baol, I, vv. 1240-1242)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •