Catalog Ext- linkinking two catalog tables?

[smahajan]

I am trying to create a property listing using catalog ext module but no idea how you can use one catalog items list as a select field in another catalog or more easily as a FK?

[Ben_360fusion]

You can create a new field and set the type to 'select'

This will give you an option to select another table in the databse. just selct a catalogue you have already created and chose the info you want to show eg id, title or nane.

[smahajan]

Ben, thanks for your prompt reply.
I was trying this for the last 2-3 days after reading your post i checked the only thing i was doing wrong was:
I made listing in a separate table, used select field to display these items in the catalog, but was not able to get the list of newly added items as i was using the "select items" checkbox to select the items, so every time i used to add a new item i had to edit the field and check the newly entered item. so not checking the "select items" solves the issue displaying the items by default. :P

Many Thanks once again.

[valentin]

that was just what I was looking for. cool.
but one other question:

if i show an item in the frontend as a list, i have the title field which brings me to my item.
but how can i make this select field, from another catalog linkable to the item of that other catalog? is that even possible?

------------------------------------
Example:

I have a catalog with "Clubs" and one with "DJs".

Every DJ has a "Residence Club" where he plays every weekend. Now I can create all those different clubs and assign one club to every DJ via the select field.
In my Frontend I´m listing all DJs with their Names, Music-Style and Resicence Club.
Is it possible to have a link on the "Name" to get to the Item "DJ" and a link on the "Club" to see more Info about the Club?
And if, how can it be done?

[Ben_360fusion]

I think i know what you mean but not sure exactly how to achieve this.

As far as i know a list just shows details and can link to one catalog (the one you are listing).

If you add the select menu for one of the catalogs you can try adding the alias field as a select option and then pull this alias into the template using the raw data in the array and surround it with a <a href""></a> link.

<?php echo $entry['data']['club_name']['value']; ?>

This would rely on the alias being listed in the data array.

add 'print_r($this->entries)' to your list template to find the data in the array.
eg:
<?php if (count($this->entries)): print_r($this->entries) ?>

Hope this helps.

[valentin]

Wow, very clever thought.

It´s a bit late for me to try this now. But might find tomorrow. I´ll keep you posted!

[thyon]

It's much easier if you use the reference module to lookup the data and display it.

In a recent project I used the reference module to lookup 1) the artwork supplier 2) the printer company and 3) the framer, all came from another catalog. If you setup their own detail page areas, and aliases, they will auto generate the links to jump to the detail for each.

In my original design I intended you to view the detail of a Game, but also the region it comes from, or other catalog data in another catalog.

[valentin]

Okay, this sounds even better. I understand the solution but somehow I can´t get the right results.
This is what I did:

This is the reader from catalog "Veranstaltungen":


The field "Ort" is a select field - it chooses a Value from the Catalog "Veranstaltungsorte".

I placed this modul on my Reader Page and everything is displayed correctly.
Now I´ve put this catalog-related-module underneath the reader-module on the same page:



But all I get in the frontend is the "Hallo" from the headline and not the related module.

If been using the related module before, but not over this select field. What am I doing wrong?

Thanks for advice,
Valentin

Update
------------------------------------------------------------------------------------------------------------------------------------------------
Damn, I was using the "related" instead of the "reference" module.
Just figured now where I was making the screenshots from the Backend and switched languages to english.
Okay, I´ll try - should be working now!

[thyon]

Database design is the most important design decision. Which directions you are going to query your catalogs will make you change how you design them and inter-relate them, e.g.

DJ Catalog
Name, Last Name, Alias, Music Style [Select-Taxonomy], Residence Club [Select-Catalog]

Club Catalog
Name, Location, City, etc. (no links to any DJ's here)

If the link to another catalog is a single link, e.g. residence club is SELECT, then you can simply use a Reference Catalog. If its more than 1 item you can use a CatalogList Module at the bottom of your catalog reader. There you can use any catalog variable displayed on the reader page in the Condition clause as a where clause, e.g.

Condition (in Catalog List for Clubs)
FIND_IN_SET(id,'{{catalog::clubs}}')

where clubs would be a TAGS field (instead of select)

[valentin]

Hey Thyon,

thanks for helping. I appreciate this very much.
I´ve set up a referrence module now because I only need one link.
I´ve also named the database columns of the two differend catalogs similar - in hope this would help.

Have a look at a screenshot of my reference module where "Veranstaltungen" would be "DJ" and "Veranstaltungsorte" the "Club":



But again, all I see is the "Hallo" Headline.

Can you see what I´m doing wrong? Sorry for bothering you with this, but it seems that once I figured this out I´ve made quite a step fwd in understanding your great catalog module!

This is where I´m testing this: http://jazz.annenviertel.at/index.php/konzerte.html

[thyon]

the reference value must match, which means you cannot have text = ID, since that will never match. you have to ensure that this happens:

(*) Catalog Club

Match Field [id:internal] (as the ID is linked to your select file in the catalog below)

(*) Catalog DJs
Reference field [venue_name:select]


This will then do the following:
SELECT (fieldslist you check) FROM catalog_club c, catalog_dj r WHERE c.id=r.venue_text

[valentin]

okay, I see now. I´ve changed the match field to "id" now.
still it doens´t show in the frontend.

this is how my reference module looks now:


and this is the select field "venue_name" in "Veranstaltungen" (DJs).


That should be alright, or?

[tosca]

<deleted>

[thyon]

It all looks right to me. I suggest now to use MySQL Query Browser or PHP My Admin to inspect the database directly. Then you can see why the values are not working out.

[valentin]

do you think it might be cause i´m using the RC1 2.8?
I´m probably not able to solve this via phpMYadmin, not enough skills!

But thanks anyway. All the best!
Valentin

[valentin]

yeah, got it working.
of course, you were right.

it had something to do with the database tables.

during this whole "conversation" in the forum, I have renamed the column "event_venue" to "venue_name".
After renaming it back again he found the corresponding items!

So, to make the solution short:

You have to select the [id:internal] as the "Match Field" in the Reference Modul.
For me this was unclear. I intuitively selected the field with the same name...

Thanks!

[thyon]

Not much about DB design is intuitive ;-)

[valentin]

Not much about DB design is intuitive ;-)

Gonna print t-shirts, man!