Custom Tables

10 July 2023 | Custom Tables

An advance export for CSVI custom table has been added where two or more database tables can be joined in export.

As an example, in this document we are explaining on how to join more than two VirtueMart tables with field virtuemart_product_id and virtuemart_shoppergroup_id so the fields of these tables can be exported in single go.

 Create a custom table export template.

1. Go to Components -> RO CSVI  -> Templates
2. Click New
3. Give the template a name, say for example Custom table export
4. Set Action to Export
5. Set Component to RO CSVI
6. Set Operation to Custom tables
7. Click Next: Select Destination to download the export file
8. Destination is set default to Download.
9. Click Next: Select options
10. The default values are OK, change if needed.
11. Click Next: Select fields
12. Click Next: Finalize
13. All done here
14. Click Edit to continue editing the template.

On Options tab the tables that need to be exported can be selected. If you only want to select data from one table, just select the table under Table Name. The power comes when you need to select data from multiple tables. You can select as many tables as you like and choose how to join them. The structure of the tables follows the same structure as it would when writing the SQL query yourself. Once done with adding the tables you can save the template. The template shows an example of how to join VirtueMart products and VirtueMart shopper groups table using VirtueMart product shoppergroups table as a join.

Add template fields for export on template fields page.

1. Go to Components -> RO CSVI -> Templates
2. Click on Template fields
3. In the Search Tools select your template
4. Click on New to add a single field

After all the tables have been set, CSVI still needs to know which fields that needs to be exported. On the fields tab click on the Edit template fields. This will take you to the Template fields page where you can add the fields that needs to be exported. Unlike the other exports, the Custom Table export allows you to choose which field from which table should be used for export. Selecting a table is needed in case you are exporting a field that has the same name in different tables, setting a table tells CSVI exactly where to find the field.

Run the export.

1. Go to Components -> RO CSVI -> Export
2. Select the template created earlier with name Custom table export
3. Click Export
4. Download dialog box will be shown to save the file if destination is set as Download in template settings.
5. The Log details page will be shown with information regarding the export if the Enable logging is enabled in the template

Grouping and Sorting

When it is needed to group or sort data, this can be done from the template on the Fields tab as well. Adding a field to group on or sort by also allows to select the table to specify where CSVI needs to look for the field. Select the table and then the field to use for the group by or sort on fields. Save the template to store all the changes.

More articles on this subject

Read more ...