Magento Forum

   
[SOLUTION] Using Export/Import Advanced Profile to select items on a custom product attribute. 
 
chiefair
Mentor
 
Avatar
Total Posts:  1839
Joined:  2009-06-04
 

I am trying to filter some data but it seems to be giving me all items. i want to do where UPC > 0 (not nill) but it is giving me all items anyway have any ideas?

Unfortunately in Attribute Management, there’s no way of adding an attribute to the import/export filters. One way I have of handling this issue is to create a new Export Profile, use field mapping to limit the number of fields to the minimum needed for your dataset and export everything. Then do your data manipulation in Microsoft Access where you can create a query to pull up only UPC is NULL. You then take the profile ID number from this new profile and inject it into the launch script to run it manually or by cron.

Another way could be to come up with a new Advanced Profile and then add filtering to it. I don’t have UPC to worry about, but Hazmat can be a concern for us, so I’m going to use that as an example.

First, create a test export profile in the Import/Export Profiles. Choose all the appropriate items, mappings, etc to limit your data down and Save and Continue Edit to save this test export. Then go to your Profile Actions XML, select all and copy what’s in the window. Paste this into a text editor and save it. Go into Import/Export Advanced Profiles and create a new Advanced Profile. Give it a Profile Name and paste in your XML from the text editor to the Actions XML window. It will look like follows:

<action type="catalog/convert_adapter_product" method="load">
    <var 
name="store"><![CDATA[0]]></var>
</
action>

<
action type="catalog/convert_parser_product" method="unparse">
    <var 
name="store"><![CDATA[0]]></var>
    <var 
name="url_field"><![CDATA[0]]></var>
</
action>

<
action type="dataflow/convert_mapper_column" method="map">
    <var 
name="map">
        <
map name="sku"><![CDATA[sku]]></map>
        <
map name="store"><![CDATA[store]]></map>
        <
map name="type"><![CDATA[type]]></map>
        <
map name="name"><![CDATA[name]]></map>
        <
map name="price"><![CDATA[price]]></map>
        <
map name="weight"><![CDATA[weight]]></map>
        <
map name="qty"><![CDATA[qty]]></map>
        <
map name="hazmat"><![CDATA[hazmat]]></map>
        <
map name="status"><![CDATA[status]]></map>
        <
map name="manufacturer"><![CDATA[manufacturer]]></map>
    </var>
    <var 
name="_only_specified">true</var>
</
action>

<
action type="dataflow/convert_parser_csv" method="unparse">
    <var 
name="delimiter"><![CDATA[,]]></var>
    <var 
name="enclose"><![CDATA["]]></var>
    <var name="
fieldnames">true</var>
</action>

<action type="
dataflow/convert_adapter_io" method="save">
    <var name="
type">file</var>
    <var name="
path">var/export</var>
    <var name="
filename"><![CDATA[export_orm-d.csv]]></var>
</action>

Note in the map section that we have a field

<map name="hazmat"><![CDATA[hazmat]]></map>
that we’ll try to develop a special report for. First let’s discuss filtering.
<action type="catalog/convert_adapter_product" method="load">
    <var 
name="store"><![CDATA[0]]></var>
</
action>

In this section, we can filter on several stock attributes. The following are built into the stock dataflow export

<action type="catalog/convert_adapter_product" method="load">
   <var 
name=“store”><![CDATA[0]]></var>
   <var 
name=“filter/name”><![CDATA[a]]></var>
   <var 
name=“filter/sku”><![CDATA[1]]></var>
   <var 
name=“filter/type”><![CDATA[simple]]></var>
   <var 
name=“filter/attribute_set”><![CDATA[29]]></var>
   <var 
name=“filter/price/from”><![CDATA[1]]></var>
   <var 
name=“filter/price/to”><![CDATA[2]]></var>
   <var 
name=“filter/qty/from”><![CDATA[1]]></var>
   <var 
name=“filter/qty/to”><![CDATA[2]]></var>
   <var 
name=“filter/visibility”><![CDATA[2]]></var>
   <var 
name=“filter/status”><![CDATA[1]]></var>
</
action>

Our problem comes about when we want to use any but these items. Let’s modify our load section a little as follows to include filtering on type simple, status enabled and hazmat:

<action type="catalog/convert_adapter_product" method="load">
    <var 
name="store"><![CDATA[0]]></var>
    <var 
name="filter/type"><![CDATA[simple]]></var>
    <var 
name="filter/status"><![CDATA[1]]></var>
    <var 
name="filter/hazmat"><![CDATA[3]]></var>
</
action>

The hazmat entity, being a dropdown is slightly annoying to figure out. First, I have to figure out what my attribute ID is. In this case looking through table eav_attribute, we find that hazmat is entity_id 511 on entity_type_id 4 (product). In this instance, the value is determined by a dropdown, so we now have to find what values that hazmat can contain. Looking in table eav_attribute_optionfor attribute_id 511 gives us option_id 3 and 4, names can be found over in eav_attribute_option_value tell us that 3=orm-d and 4=hazmat. Let’s get orm-d items. So we run our modified Advanced Profile and find, woops, it gets us output filtered on the first two filters, but ignores our hazmat attribute.

 
Magento Community Magento Community
Magento Community
Magento Community
 
chiefair
Mentor
 
Avatar
Total Posts:  1839
Joined:  2009-06-04
 

This just isn’t going to do. Let’s take a look at app/core/Mage/Catalog/Model/Convert/Adapter/Product.php and find the section where it loads the above filters:

/**
     * Load product collection Id(s)
     *
     */
    public function load()
    
{
        $attrFilterArray 
= array();
        
$attrFilterArray ['name']           'like';
        
$attrFilterArray ['sku']            'like';
        
$attrFilterArray ['type']           'eq';
        
$attrFilterArray ['attribute_set']  'eq';
        
$attrFilterArray ['visibility']     'eq';
        
$attrFilterArray ['status']         'eq';
        
$attrFilterArray ['price']          'fromTo';
        
$attrFilterArray ['qty']            'fromTo';
        
$attrFilterArray ['store_id']       'eq';

        
$attrToDb = array(
            
'type'          => 'type_id',
            
'attribute_set' => 'attribute_set_id'
        
);

        
$filters $this->_parseVars();

        if (
$qty $this->getFieldValue($filters'qty')) {
            $qtyFrom 
= isset($qty['from']) ? $qty['from'0;
            
$qtyTo   = isset($qty['to']) ? $qty['to'0;

            
$qtyAttr = array();
            
$qtyAttr['alias']       'qty';
            
$qtyAttr['attribute']   'cataloginventory/stock_item';
            
$qtyAttr['field']       'qty';
            
$qtyAttr['bind']        'product_id=entity_id';
            
$qtyAttr['cond']        "{{table}}.qty between '{$qtyFrom}' AND '{$qtyTo}'";
            
$qtyAttr['joinType']    'inner';

            
$this->setJoinField($qtyAttr);
        
}

        parent
::setFilter($attrFilterArray$attrToDb);

        if (
$price $this->getFieldValue($filters'price')) {
            $this
->_filter[] = array(
                
'attribute' => 'price',
                
'from'      => $price['from'],
                
'to'        => $price['to']
            
);
            
$this->setJoinAttr(array(
                
'alias'     => 'price',
                
'attribute' => 'catalog_product/price',
                
'bind'      => 'entity_id',
                
'joinType'  => 'LEFT'
            
));
        
}

        
return parent::load();
    
}

Let’s add our attribute we want to filter on as follows:

public function load()
    
{
        $attrFilterArray 
= array();
        
$attrFilterArray ['name']           'like';
        
$attrFilterArray ['sku']            'like';
        
$attrFilterArray ['type']           'eq';
        
$attrFilterArray ['attribute_set']  'eq';
        
$attrFilterArray ['visibility']     'eq';
        
$attrFilterArray ['status']         'eq';
        
$attrFilterArray ['price']          'fromTo';
        
$attrFilterArray ['qty']            'fromTo';
        
$attrFilterArray ['store_id']       'eq';
        
$attrFilterArray ['hazmat']         'eq';

And save it to our local code store /app/code/local/Mage/Catalog/Model/Convert/Adapter/Product.php

Now when we run this Export Advanced Profile, it gives us only 259 results, not 9500 so something must be working as a head on the export file shows:

"sku","store","type","name","price","weight","qty","hazmat","status","manufacturer"
"3M 1300 5OZ"
,"admin","simple","Rubber and Gasket Adhesive, 5 oz","13.5000","0.3500","1.0000","orm-d","Enabled","3M Corp"
"3M 1300L QT"
,"admin","simple","Rubber and Gasket Adhesive, Quart","36.7500","2.1500","1.0000","orm-d","Enabled","3M Corp"
"3M 8001"
,"admin","simple","Weatherstrip Adhesive","6.9500","0.3500","1.0000","orm-d","Enabled","3M Corp"

Now to make this runnable from the command line or from cron. Note that the assigned Profile ID for standard profiles and advanced profiles is in the same sequence. Take the Advanced Profile’s profile ID and plug it into the script and now you can run your custom attribute export external from Magento.

This dataflow tutorial gives you some ideas as to programming up Advanced Profiles. Pay particular attention to the section on Customer and Product Adapters as it shows you how to filter.

 
Magento Community Magento Community
Magento Community
Magento Community
 
chiefair
Mentor
 
Avatar
Total Posts:  1839
Joined:  2009-06-04
 

See Other Comparison Operators on this web page. Explains the various comparison operators used in this code sample:

public function load()
    
{
        $attrFilterArray 
= array();
        
$attrFilterArray ['name']           'like';
        
$attrFilterArray ['sku']            'like';
        
$attrFilterArray ['type']           'eq';
        
$attrFilterArray ['attribute_set']  'eq';
        
$attrFilterArray ['visibility']     'eq';
        
$attrFilterArray ['status']         'eq';
        
$attrFilterArray ['price']          'fromTo';
        
$attrFilterArray ['qty']            'fromTo';
        
$attrFilterArray ['store_id']       'eq';
        
$attrFilterArray ['hazmat']         'eq';

 
Magento Community Magento Community
Magento Community
Magento Community
 
nafnaf1000
Sr. Member
 
Total Posts:  209
Joined:  2008-02-21
 

hey guyz. Thank you for your reply’s.

I have added UPC to the filter with a fromTO as the type.

$attrFilterArray ['upc']               'fromTo';

My XML says

<action type="catalog/convert_adapter_product" method="load">
<var name="store"><![CDATA[0]]></var>
<var name="filter/upc/from"><![CDATA[>90000]]></var>
</action>

but not getting anything out of it....

 
Magento Community Magento Community
Magento Community
Magento Community
 
chiefair
Mentor
 
Avatar
Total Posts:  1839
Joined:  2009-06-04
 

Will need to tinker with this a little bit as I\’d like to have the range working as well.

 
Magento Community Magento Community
Magento Community
Magento Community
 
d8bhatta
Member
 
Total Posts:  44
Joined:  2009-06-11
 

Good idea..

Actually i wanted to show custom csv column headers ..i got this from this thread!

Thanks chiefair

 
Magento Community Magento Community
Magento Community
Magento Community
 
nafnaf1000
Sr. Member
 
Total Posts:  209
Joined:  2008-02-21
 

@chiefair

Hey, Did you ever get this working on your store? as it dos not seem to work for me....

Thanks

 
Magento Community Magento Community
Magento Community
Magento Community
 
chiefair
Mentor
 
Avatar
Total Posts:  1839
Joined:  2009-06-04
 

The sweet thing about Magento is that every time you turn around to do the simplest thing, you end up starting a whole new project. It’s a great billable time generator.

Simple project, change from mod_php5 to mod_fastcgi for better speed and memory management = 500 errors instead of 404 pages, fixed

Simple module install = new mostly working improved function except for a niggling “Call to a member function on a non-object” error, working on it.

So, I had to shelve the one thing I was actually doing for fun and no profit. Am trying to get back to it.

 
Magento Community Magento Community
Magento Community
Magento Community
 
sander010587
Jr. Member
 
Total Posts:  9
Joined:  2009-12-18
 

Hello,

Nice instructions Chiefair, it is working with your given method. Unfortunately it is still a lot of work to build a profile. It would be nice if we can add the filter to the filters section under dataflow profiles so it can used to create the xml. (I will look into that a bit more).

Just something to speed up the process for finding the correct attribute value id without going in the database:
- Go to manage attributes
- select the attribute you want to look up the id of a specific value (in my case supplier)
- Click at the values/options tab and locate your attributes value (in my case the supplier I want to filter on is “E-Mega")
- I use Google Chrome which has the inspect element property: right mouse click on your value and inspect element
- It shows something like this: <input name="option[value][125][0]" value="E-Mega" class="input-text required-option” type="text">
- Now I know that the id I want to filter on is 125 for the supplier (E-Mega) and I use it in the schema as above.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
    Back to top