Custom Field display

We are a event registration website. We need to provide a display on the front-end (or back-end) for our event organizers. I would ideally like to show in a table format based on product_alias = xxx:

Oreder Number, Product, Customer Name, Customer Email, CUSTOM FIELDS

the last column(s) are the one I am struggling with... I would like to display all CUSTOM FIELDS associated with the product. So if the fields in one of my products asks for "shirt size", "emergency contact", "contact phone" then I would like those three columns added to the query.

Thank you. I am having a tough time giving our users (event organizers) easy ways to download (example) how many of each t-shirt size they need to order for their event or who the emergency contacts are for each customer.

Hi,

This is not an option in HikaShop, you will have to edit the file "administrator/components/com_hikashop/views/product/view.html.php" and the view "product / listing" (backend template) to do that.
It require good PHP and MySQL knowledge.

We have on our todo list to create a new view in the backend to display anything, you will be able to select the kind of things to display, what to display, filter the results with many many filters choice..

Ok. I am not sure how I am supposed to get all the info to complete orders out of the system then.

I can't get a list of "emergency contacts", "t-shirt sizes", etc. How does this get done for other users?

Hi,

As Xavier said, it's not easy. You need to create your own view and do several queries to load all the data in PHP and then display it in your view. If you know PHP, that shouldn't be too difficult though.

First, you need to load all the confirmed orders for a product.
That can be done with such query:
SELECT * FROM #__hikashop_order_product as prod LEFT JOIN #__hikashop_order as order ON prod.order_id=order.order_id WHERE prod.product_code='my_product' AND order.order_status='confirmed'
(you need to change my_product by the code of your product)
You will get the orders number in the order_number column, the Product in the order_product_name column and the custom item fields in the corresponding column names

Then, you need to load the user of the orders with another query:
SELECT * FROM #__hikashop_user as user LEFT JOIN #__user as juser ON user.user_cms_id=juser.id WHERE user.user_id IN (XXX,YYY)
(you need to replace XXX, YYY etc by the ids of the user in the orders)
You can get the ids in the results of the first query in the order_user_id column.
You will get the Customer name in the name column and the Customer email in the user_email column.

With these two queries, you will have all the data for your view.

thanks. a couple of corrections:

SELECT * FROM
__hikashop_order_product as prod
LEFT JOIN __hikashop_order as order
ON prod.order_id=order.order_id
WHERE prod.order_product_code='xxx' AND order.order_status='confirmed'

I am still struggling with this one. The SELECT * is capturing too many columns for my needs. I am trying to display this table for users' products..

However different products populate different columns in hikashop_order_product. So one product may have a custom field = tshirt size while another may not. I don't want to display tshirt size for the product which does not need that info.

How might I only select

- the appropriate columns for product 1 where product 1's category has columns
- the columns for product 2 where product 2's category has columns

My current query:

SELECT *

FROM _hikashop_product AS p

INNER JOIN _hikashop_product AS pp

ON p.product_id = pp.product_parent_id OR p.product_id = pp.product_id

INNER JOIN _hikashop_order_product op

ON pp.product_id=op.product_id

INNER JOIN _hikashop_order o

ON op.order_id=o.order_id

INNER JOIN _hikashop_user u

ON o.order_user_id=u.user_id

INNER JOIN _comprofiler AS cp

ON u.user_cms_id=cp.user_id

WHERE p.product_alias='10' AND o.order_status='confirmed'

Thanks.

Hi there,

You have to use SELECT product_id, u.user_id (it's an example) instead of SELECT *
You should be able to get only the column you need.

I understand that I can select specific columns. I would like something dynamic. It would select product_id and then ALL columns which are custom fields for that product id but not other custom fields for other products.

Hi,

Like Eliot said, you have to specify what you need with for example:
SELECT p.product_id, pp.parent_product_id, pp.product_id, op.*, ... WHERE ...

And maybe add more conditions in the WHERE.

Is there any update on providing this sort of filtering in the next release? I know it has been on the TODO list.

Thanks.

It will not be in the next release (2.1.1) which is a bug fix release, but we hope to develop that for the 2.2.0
Regards

Xavier wrote: We have on our todo list to create a new view in the backend to display anything, you will be able to select the kind of things to display, what to display, filter the results with many many filters choice..

Xavier, does this mean, you will add a feature, where all potential custom fields can be added to the front end without deep PHP knowledge? Thx

No.

Xavier was probably referring to our future mass action system which will allow to generate listings of data with filters you would select yourself, being able to select what kind of data to display or do processing on the data filtered. That's a feature which will be added in the backend and it won't meet your requirements.

What you want is really something which will only be used for your specific case of events selling shop with different organizers of events. We can't add that by default in HikaShop. It's really something to be custom developed for you. We've already given advises on how to load the necessary data. All that's left is to display it in your view the way you want. If you know well PHP, that's shouldn't be a problem. Otherwise, I would recommend to find someone to help you with that project via our commercial jobs forum or joomlancers.com