Pastel 14: Exporting purchase order details as csv file

[michellepace]

Hi there,

Does anyone know if it possible to export to Excel as a CSV file? I'd like to use this CSV file to create Excel pivot tables. In particular I would like to export a file similar to:

Excel export: Each row is an item ordered on a PO
Column Names:
Supplier, PO Date, Delivery Date, Item Code, Item Description, Qty, Amount (Ex tax), Inventory Category, Purchase Date, Has GRN?

Could anyone suggest how I do this, will I need to use the Intelligence Reporting?

Thanks in advance,
Michelle

[michellepace]

Hi, sorry to ask again - but does anyone have any feedback on my question? I tried to phone Pastel themselves but they said they don't support Intelligence reporting.

[michellepace]

I haven't tried this yet... but I think my answer may be here: https://youtu.be/hL10FH-f-78?t=34m32s

[Kevin Smith]

Hi Michelle

Did you come right? Do you have the Intelligence Reporting Module (In Pastel, Help, About - look for the Intelligence Reporting module)? Most of what you are looking for can be done through excel, via a direct ODBC query to the Pastel Database. The main issue is that PVSW have not released a 64bit ODBC connector for PVSW10 and below - so you would need to have 32bit Office installed to be able to query the database, to draw the raw information you are looking for.

[michellepace]

Hi Kevin, Goodness I can't believe I am only replying now. So sorry (but thank you for replying). Unfortnately I have not been able to come right yet - - do you perhaps have any instructions you can share with me on how to connect to Pastel's pervasive database? Also, what credentials would I need to be using - - would it be the same admin credentials I use to login to Pastel?

[Kevin Smith]

Start - Run - PCC

If the control centre doesn't open, go into windows control panel, add remove programs, right click Pervasive, select change, and select to install the PCC under Java Utilities.

Once the control centre opens, expand engines, find your computer and expand to databases. Right click and add new db. Give it a name and browse to the Pastel company folder and click finish. If you are prompted for credentials, it will be the windows username and password for the machine hosting the data (ie the server). Once completed, you will be able to expand the tables and see the data in PCC.

You should be able to find most of the information you need in the history header (dates) and history lines tables (item information). PO's will be "documenttype" 106. I suggest you also filter on "searchtype" - 4 is inventory items, 6 for GL codes if used on PO's.

In excel, select the data tab on the ribbon, select "From other sources" then select "from Microsoft query". You database will show up in the list (unless you running 64bit office) and you can select your tables and fields and add filters in the query wizard. Setup your pivot tables on the second tab, and just refresh the table whenever you need updated information.

You may need to change security settings in the trust centre in excel relating to external content and data connections to allow the workbook to refresh the data.