Newbie alert - trying to create a CB Query field with a link

Hi all,

All new to CB but have worked with Joomla for 8 years, trying to figure out a way to create a small customer relations app, using CB.

For this I need to look up a customer in a non-CB table in MySQL and do this using a CB Query Field with the following query:
This query work fine, and if I don't do anything else, I get a fine list with the customers. However I need to be able to click a hyperlink on each customers name, and have therefor tried editing the "Profile Value Layout" to the following:
I get a fine link alright, however the [column_id] and [column_name] are not substituted with the values from my query.

I've tried using just [id] and [name], and then the substitution works, but give me the logged in users id and name.

So - whats the correct syntax to the the values in my link substituted with the values from my query?

Cheers

Edit: Using CB Query 5.0.4 (plug_cbqueryfield_5.0.4+build.2017.04.27.01.40.36.d2d5b6dda)

Your query doesn't look specific to a user. Are you sure you want to output every row in that table as a field? I'm guessing there should be a WHERE statement added to it to reduce the results down to be more specific to that user? At any rate the HTML is fine it's just in the wrong spot. Edit your query field and under Parameters > Display change Output to "Multiple Rows" then place your HTML in Row. If you're expecting a single row then set Output to "Single Row" then set Columns to "Multiple Columns" and Display to "Custom" now place your HTML in Custom.

Kyle,

Thanx for your response!
You are right - I need to narrow the query down to give me only the customers belonging to the specific user!

Works perfect after following your guidelines above!
Appreciate it very much!

Cheers

Hi again,

Have to ask one more question..

I've now changed the query to:
Also changed Output to "Multiple Rows" and placed the following in "Rows"
If just one customer name and id is returned from the selected record, it works and show this perfectly. However - a client can have more than one customer, and if thats the case, only the first value (customer) is shown as a result of the above output - not the rest...

When I edit the customer information on a client, I do this by using the "Query Drop Down (Multi-select)". When selecting multiple customers, this is stored in a "customer" column in comprofiler table, in the format "4|*|1|*|3|*|5" each representing a customer id from "#__eaci_customers".

Question is - how do I format the query and output above, show all customers id & names registered for the user? I thought this was what the selection of "Multiple Rows" was supposed to do for me - ie. run the query for each value in the "cb_customers" column on the selected record?

Hope you can follow me, as I feel this is described somewhat more complex than I intended

Cheers

You'll have to use SQL functions to format the |*| delimitered multiselect values so you can match it.


There are of course several alternatives like LIKE statements with wildcards or even REGEX.

Krileon,

I love you man - thanx a lot, it makes totally sense and works perfectly!!

Cheers