Categories
View Replies (25)

Resolved Export orders based on Order status

  1. MesquiteMan
    MesquiteMan
  2. RO CSVI
  3. Friday, 19 February 2016
  4.  Subscribe via email
Joomla! 3.4.8, VM 3.0.12, CSVIPro 6.5.2, CSVI VM Plugin 6.5.2

I use a program called shipworks to manage my shipping. When I ship an item with shipworks, it connects directly to the VM database and changes the order status to shipped but the Status of ordered products stays what it was before. Unfortunately, there is no way around this. Is it possible to do an export of orders and their items based on the order status, then make changes to the Status of ordered products and import so that the Status of ordered products is updated to be the same as the order status? If so, can you tell me what VM choice to make and what fields are to be used?

I have tried with a template using VM Order Items and there is a field there called order status. I use it to filter for certain order status codes such as shipped but it returns orders that have an order status of shipped but where the Status of ordered products is confirmed. I was under the impression that order status was the order status, not the Status of ordered products. If I can do an export of all the orders with a status of shipped with all of their Status of ordered products, then I can make the change of the Status of ordered products to shipped and re-upload. Then I will be able to get the data I am looking for.

Here you can see the fields I have chosen for my template. It is returning the Status of ordered products rather than the order status on export.
status.jpg

This is what I am calling the Status of ordered products:
productstatus.jpg

This is what I want to be able to filter by:
status1.jpg
0
Responses (25)
rolandd
rolandd
Accepted Answer Pending Moderation
Hello,

Is it possible to do an export of orders and their items based on the order status, then make changes to the Status of ordered products and import so that the Status of ordered products is updated to be the same as the order status?
You can do an export of the order items, not the order export. This has a filter called Order status which you can set to the status of the order items you want. The export will then export only the order items that match the set status.

You will need to export the fields order_item_sku, virtuemart_order_id, order_status_name (or order_status). These are the minimal fields you need to run the import. In your exported file you only need to change the value of the order_status_name (or order_status).

If I can do an export of all the orders with a status of shipped with all of their Status of ordered products
This is not possible, the order export does not export the status of individual order items.

Then I will be able to get the data I am looking for.
Can you use the scenario I explained above?
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 1
MesquiteMan
MesquiteMan
Accepted Answer Pending Moderation
Roland,

This gets confusing with the similar names. Let's call it the order status. This is the overall status of the order itself. Let's call the order status beside each item when you open the order in VM the product status.

When editing the template, in the operation section, I have the action=Export, Component=Virtuemart, and operation=order item. For fields I have the following:
fields1.jpg

When I do the export, I get the product status, not the order status. I need the order status. For my export, I need the product sku, product name, customer name, date, order id, and order status.

What am I doing wrong?
Attachments (1)
fields1.jpg fields1.jpg
  1. more than a month ago
  2. RO CSVI
  3. # 2
rolandd
rolandd
Accepted Answer Pending Moderation
Hello,

This gets confusing with the similar names.
Very much so :)

When I do the export, I get the product status, not the order status.
Correct because there is no field to export the order status only the product status.

You are not doing anything wrong. The order item export does not export the order status only the product status. The order export only exports the order status.

I need the order status. For my export, I need the product sku, product name, customer name, date, order id, and order status.
That cannot be done as it is now. The order status is not available in the order item export at this point. I can look into adding it in. Also need to think of an appropriate name as order_status is already in use ;)

In your list of fields you can omit either order_status or order_status_name because they are the same thing. The order_status is just the code and the order_status_name is the full name of the order status.
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 3
MesquiteMan
MesquiteMan
Accepted Answer Pending Moderation
I have been using CSVI since way back in VM1.1 days! I like having the code and the status name. Don't know why but I do!

If I do a template using operation=order rather than order items, I should be able to get the information I need, correct? At least the available fields make it look like I could. However, when I choose one item to filter by, I get a HUGE long list of the same item shown. Here are screen shots:

Here is how I have the operations section set
operations.jpg

Here are the fields I have chosen
fieldsnew.jpg

Here is the filter contents:
selection.jpg

I forgot to get a shot but I clicked on the last item CJC1R. I then clicked save and when I came back to the options page, I have a HUGE list of duplicate entries of the same item. For the screen shot, I zoomed as far out as Firefox will allow and it still will not all fit on the screen!
longlist.jpg

Of course if I try the export, I get a no data results.
Attachments (4)
operations.jpg operations.jpg
fieldsnew.jpg fieldsnew.jpg
longlist.jpg longlist.jpg
selection.jpg selection.jpg
  1. more than a month ago
  2. RO CSVI
  3. # 4
MesquiteMan
MesquiteMan
Accepted Answer Pending Moderation
Oh yeah, in case you question it, after doing this one, I deleted it and created a new one. This time I added the VM Order ID filed and placed it at the top in case that matters. Still got the same results.
  1. more than a month ago
  2. RO CSVI
  3. # 5
MesquiteMan
MesquiteMan
Accepted Answer Pending Moderation
I did another test. I believe the really long list may be the result of old order migrations with VM Migrator from vm1.1.9 to VM3.0.12. The item CJC1r is an old sku I am not using on the new site but it is still on some of the older orders. If I choose CJC1r10 from the list and save, that is the only one that shows up. If I go back and add CJC1r, I get the terribly long list again. I even set the date range this time to only filter on orders since the new site went up. Not sure how to go about fixing this. I still have old orders from the old site that need to be filled and exported in this sheet.
  1. more than a month ago
  2. RO CSVI
  3. # 6
MesquiteMan
MesquiteMan
Accepted Answer Pending Moderation
And one more test! This time, I used the same operations as above and the same fields. In options, set the same order status' but this time, I choose CJC1r10, CJC1r12, CJC1r14, CJC1r16, CJC1r18, and CJC1r20. I clicked save and when I went back to options, this is what I see:

list.jpg
Attachments (1)
list.jpg list.jpg
  1. more than a month ago
  2. RO CSVI
  3. # 7
MesquiteMan
MesquiteMan
Accepted Answer Pending Moderation
Also, I have more CJC1r skus but they do not show up on the search list. I have CJC1r22 through CJC1r48. Even if I search on them specifically, they do not show up. Why is that?
  1. more than a month ago
  2. RO CSVI
  3. # 8
rolandd
rolandd
Accepted Answer Pending Moderation
Hello,

You know there is an Edit button below the post if you want to add more information to it :) Going to combine all your posts in one here.

I have been using CSVI since way back in VM1.1 days! I like having the code and the status name. Don't know why but I do!
Perfect !!

If I do a template using operation=order rather than order items, I should be able to get the information I need, correct?
You won't get the product status.

Of course if I try the export, I get a no data results.
If you can post the debug log of your export we can see what filters are being applied.

This time I added the VM Order ID filed and placed it at the top in case that matters.
The location of the fields have no influence on the export.

If I go back and add CJC1r, I get the terribly long list again.
Would it be possible to send me a dump of your VirtueMart tables? I should be able to reproduce the long list of items then. This will make it easier to track down why that happens. You can send the dump in a private message.

I have CJC1r22 through CJC1r48. Even if I search on them specifically, they do not show up. Why is that?
If they have never been ordered they won't show up. The search is only on ordered items. If they have been ordered, I would need to check closer as to see why that won't happen. With the dump, that will make it easier to investigate.
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 9
MesquiteMan
MesquiteMan
Accepted Answer Pending Moderation
My notes in Red
Hello,

You know there is an Edit button below the post if you want to add more information to it :) Going to combine all your posts in one here.
[color=#ff0000]Sorry about that! On another support forum I use, they get pissy if you go in and edit the post to combine things![/color]

I have been using CSVI since way back in VM1.1 days! I like having the code and the status name. Don't know why but I do!
Perfect !!

If I do a template using operation=order rather than order items, I should be able to get the information I need, correct?
You won't get the product status.
[color=#ff0000]I don't want or care about the product status actually. All I need and want is the order status. I am sorry I did not make that more clear in the original post. I had tried doing an export with operation=order and had the issue with no data so I assumed I had to use operation=order items which of course only gives me the product status. In the original post, I was looking for a way to change the product status to match the order status so I could then do an operation=order items export and get the orders I am looking for. My shipping software changes the order status in the database but does not change the product status. If I do an export based on product status, I get all orders from way back and have no idea what the order status is. If I can get the operation=orders to work, I will be golden![/color]

Of course if I try the export, I get a no data results.
If you can post the debug log of your export we can see what filters are being applied.
[color=#ff0000]Will do this evening.[/color]

This time I added the VM Order ID filed and placed it at the top in case that matters.
The location of the fields have no influence on the export.

If I go back and add CJC1r, I get the terribly long list again.
Would it be possible to send me a dump of your VirtueMart tables? I should be able to reproduce the long list of items then. This will make it easier to track down why that happens. You can send the dump in a private message.
[color=#ff0000]OK, you are getting a little over my experience level here! I can certainly work in phymyadmin and am not scared to do so. What tables do you need or is there a better way to do this? If I use phpmyadmin, do you want them in sql or csv?[/color]

I have CJC1r22 through CJC1r48. Even if I search on them specifically, they do not show up. Why is that?
If they have never been ordered they won't show up. The search is only on ordered items. If they have been ordered, I would need to check closer as to see why that won't happen. With the dump, that will make it easier to investigate.

[color=#ff0000]I don't believe there is a problem and it is working as designed. However, woudl it be possible to be have it so it will search all products in VM that match? This is a something I export on a regular basis. As is, I will need to go into the template and do a new search to make sure new ones have not been ordered every time I want to run the export. I would also not be able to use cron (have not done so before but may decided to start). If it included all skus that meet the criteria rather than just what has been ordered, I woudl be able to use the export without having to remember to make the changes each time. The less I have to remember, the less chance I will mess something up![/color]
  1. more than a month ago
  2. RO CSVI
  3. # 10
rolandd
rolandd
Accepted Answer Pending Moderation
Hello,

I don't want or care about the product status actually.
Aha, that got lost in translation.

If I can get the operation=orders to work, I will be golden!
We will and let's focus on that.

I can certainly work in phymyadmin and am not scared to do so. What tables do you need or is there a better way to do this? If I use phpmyadmin, do you want them in sql or csv?
Yes, you can use PhpMyAdmin. What you do there is select the database in the tree on the left pane. After that click Export on the toolbar at the top. This will give you the export page. There you click Go. This will export the complete database in SQL and is probably the easiest way to go.

However, woudl it be possible to be have it so it will search all products in VM that match?
The search filter searches all the sold products because you can't export orders of products that have not been sold because they don't exist.

This is a something I export on a regular basis. As is, I will need to go into the template and do a new search to make sure new ones have not been ordered every time I want to run the export.
Thinking about a new approach here, the latest version of CSVI has the option to export everything since the last run. So you run your export on Monday morning where you set the filters. On Tuesday morning, you remove the filters and just export the orders since Monday morning by setting the order date to last run. On Wednesday morning, don't make any changes to the template but just run it and you get the orders from Tuesday till Wednesday. So you continue, it will always be the orders since last run. The reason the template needs to be changed on Tuesday morning is because you need to have a baseline for the Since last run. Perhaps this is something of use to you? This way you can also use it with cron.

I looked at the debug log you sent, there is nothing wrong with the query, so if there is nothing in the export file all I can think of is that the query hasn't found any results.
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 11
MesquiteMan
MesquiteMan
Accepted Answer Pending Moderation
My replies in red. (I hope that is a good format for you!)
Hello,

I don't want or care about the product status actually.
Aha, that got lost in translation.
[color=#ff0000]Sorry about that![/color]
If I can get the operation=orders to work, I will be golden!
We will and let's focus on that.

I can certainly work in phymyadmin and am not scared to do so. What tables do you need or is there a better way to do this? If I use phpmyadmin, do you want them in sql or csv?
Yes, you can use PhpMyAdmin. What you do there is select the database in the tree on the left pane. After that click Export on the toolbar at the top. This will give you the export page. There you click Go. This will export the complete database in SQL and is probably the easiest way to go.
[color=#ff0000]I did not realize you wanted the entire database. I can certainly do that. Not many developers I would trust with that but I have "known" you for years now and trust that it will be fully deleted and destroyed once testing is done. I surely don't want my customer data, especially e-mails getting out on a spam list somewhere.[/color]

However, woudl it be possible to be have it so it will search all products in VM that match?
The search filter searches all the sold products because you can't export orders of products that have not been sold because they don't exist.
[color=#ff0000]Yes, but if it did not find the item on the list, it would just skip over it and not return any results. I believe one of your older version of CSVI allowed this but could be wrong.[/color]

This is a something I export on a regular basis. As is, I will need to go into the template and do a new search to make sure new ones have not been ordered every time I want to run the export.
Thinking about a new approach here, the latest version of CSVI has the option to export everything since the last run. So you run your export on Monday morning where you set the filters. On Tuesday morning, you remove the filters and just export the orders since Monday morning by setting the order date to last run. On Wednesday morning, don't make any changes to the template but just run it and you get the orders from Tuesday till Wednesday. So you continue, it will always be the orders since last run. The reason the template needs to be changed on Tuesday morning is because you need to have a baseline for the Since last run. Perhaps this is something of use to you? This way you can also use it with cron.
[color=#ff0000]My issue is one of the products I make is made to order and I have a fairly long lead time. The export I do is for just orders that include those items and this is how I keep track of my list of orders that have to be made. I only run that export twice a month or so and it is really not a huge issue to need to go into the options and do a new search for any new items, as long as I remember to do it! I was thinking of cron for other exports I may want to set up in the future.[/color]

I looked at the debug log you sent, there is nothing wrong with the query, so if there is nothing in the export file all I can think of is that the query hasn't found any results.
[color=#ff0000]There are absolutely orders in VM that meet the criteria of the export. I double checked it numerous times. I will check again just to make sure I didn't do something wrong[/color].

I just did a brand new template. I triple checked that I had everything correct. Attached is a screen show of the optoins page. I chose a date range that I KNOW has orders that meet the filter criteria set. I actually double checked them in CM before setting the filters. I am still getting the no data results. I am also getting duplicate entries in the order product filter even though I only clicked on the product in the search box one time and it only showed one before I saved. After saving, if open options again, that is when I have duplicates.


filters.jpg
Attachments (1)
filters.jpg filters.jpg
  1. more than a month ago
  2. RO CSVI
  3. # 12
MesquiteMan
MesquiteMan
Accepted Answer Pending Moderation
I have sent the db export to you via pm. I know this is COMPLETELY unrelated to CSVI so if you don't answer, I will understand but since you are a Joomla! database guru, I thought I would ask! If I have installed some extensions and then removed them since I don't like them, can I safely delete the tables they sometimes leave behind? I have found that many extensions do not clean up and remove their database tables and like to keep things tidy!
  1. more than a month ago
  2. RO CSVI
  3. # 13
rolandd
rolandd
Accepted Answer Pending Moderation
Hello,

Not many developers I would trust with that but I have "known" you for years now and trust that it will be fully deleted and destroyed once testing is done.
Thank you, I appreciate it. All data will be handled confidential. I have the file, so you can delete it.

Yes, but if it did not find the item on the list, it would just skip over it and not return any results. I believe one of your older version of CSVI allowed this but could be wrong.
That is something I will investigate with your data. The code in that respect hasn't changed as far as I can remember.

My issue is one of the products I make is made to order and I have a fairly long lead time. The export I do is for just orders that include those items and this is how I keep track of my list of orders that have to be made. I only run that export twice a month or so and it is really not a huge issue to need to go into the options and do a new search for any new items, as long as I remember to do it! I was thinking of cron for other exports I may want to set up in the future.
All clear, just thinking outside the box where possible :)

There are absolutely orders in VM that meet the criteria of the export. I double checked it numerous times. I will check again just to make sure I didn't do something wrong.
Now that I have a copy of your data, I can check it as well.

can I safely delete the tables they sometimes leave behind?
Yes you can, if you no longer need them.

There is a good reason for extensions not deleting their tables on uninstallation. CSVI is one of them ;) The reason behind it is, some people uninstall the extension and then install the new version. By doing that you would lose all your settings, not something many people are pleased with. That is the reason I implemented the option in Maintenance to delete all CSVI tables. A bit of a workaround but at least you can delete them if you want and so consciously.
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 14
MesquiteMan
MesquiteMan
Accepted Answer Pending Moderation
Thank you Roland. I will anxiously await your reply! Please let me know if you need anything else.

On the database tables...there are a few that I installed, tried to work with, and found them to be complete JUNK so I uninstalled them. I do understand a developer wanting to leave them in case the user decides to reinstall or upgrade and not lose data.
  1. more than a month ago
  2. RO CSVI
  3. # 15
rolandd
rolandd
Accepted Answer Pending Moderation
Hello,

I believe I have everything I need, so will check it out. Tomorrow I will have feedback for you. Thank you.
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 16
rolandd
rolandd
Accepted Answer Pending Moderation
Hello,

So I have been checking out the issues you are seeing and I am going to run by them one by one here. My test setup is your database with CSVI 6.5.2 and the patch provided to you in the other thread. After applying the patch, one thing I had to do was clear my caches because the old JavaScript file was loaded.

I have CJC1r22 through CJC1r48. Even if I search on them specifically, they do not show up. Why is that?
That is because these items have never been ordered, at least they are not stored with that SKU in the order items. So there is nothing to be found when searching for them.

If I go back and add CJC1r, I get the terribly long list again.
I added this product as an ordered product to the options but after reloading the template I only see 1 product. Not sure what has happened there in your case.

There are absolutely orders in VM that meet the criteria of the export. I double checked it numerous times. I will check again just to make sure I didn't do something wrong.
The testchambers template I exported as well without any changes to it, this came up empty. The reason for that is, the selected products are stored with their IDs and not their SKUs. This is a change due to the patch that has been applied. So here you just need to re-add the products.

Let me know how it goes and if there is anything else you want me to check.
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 17
MesquiteMan
MesquiteMan
Accepted Answer Pending Moderation
Roland,

I am not sure what you are saying here. I am still not getting any results with the testchambers template. It comes up empty every time. The server cache has been cleared every time before I run the export. I just tried it again without making any changes to the template and got no data available. I then went into options in the template and deleted all the products in the filter. I added back CJC1r10 and tried to save the template. I got a blank screen and firebug showed no issues. Then went back to the main home page for the admin area and a warning message popped up. I refreshed the page and the error message was gone but when I went to CSVI and clicked on templates, then testchambers, the same error message showed back up. Here is the error message:

error.jpg

Now I have no idea what to do. I can't get the data I need and have no idea what is going on or how to get it. PLEASE HELP!
Attachments (1)
error.jpg error.jpg
  1. more than a month ago
  2. RO CSVI
  3. # 18
rolandd
rolandd
Accepted Answer Pending Moderation
Hello,

The error you see is because of a mix-up with the patch that has been applied.

Attached is a template version of the chambers that did export the data for me. You can load this template by going to Maintenance -> CSVI Pro -> Restore templates. After that select this template and it should show up on the export page.

Let me know if you get any output with this template.
Attachments (1)
testchamber_roland.xml
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 19
rolandd
rolandd
Accepted Answer Pending Moderation
Hello,

I have dived into this some more. Please follow these steps and let me know how it goes.
  1. Load this patch file via the maintenance menu
  2. Go to Maintenance -> Tasks and click Reset and confirm the dialog.
  3. Go to your testchamber template and edit it.
  4. On the Options tab, remove all the products you have set in the product filter.
  5. Add all the products you want to filter on and save the template
  6. Go to export and run the export of the testchamber template.
  7. Do you get anything in the export file?
Attachments (1)
patch_orderitems_20160225.zip
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 20
  • Page :
  • 1
  • 2


There are no replies made for this post yet.
Be one of the first to reply to this post!