Query Pastel Partner from Excel

[PMH]

Hi there guys,
I'm new here and hope to gain some valuable info from some of the more clevererer lot and share my experiences with others running into similar problems.

In short: We have a manufacturing / retail / design business where we deal with a large amount of separate components (from stock) that needs to be combined into a single (unique) order. We do this by creating a unique spreadsheet quote for each job which gets transformed into a "pick slip" for the factory.

To improve accuracy of the Picking slip, I need to query stock levels in Pastel from within Excel (this is required to generate a sensible "pick slip" for the warehouse and not include items which is out of stock, or replace it with something else that is in stock).
I'm sure many businesses need the same functionality to optimize the workflow.

What I've done so far:
1) Created an ODBC connection to our Pastel database from Excel
2) Managed to figure out that the table called "MultistoreTrn" in Pastel Partner is the one storing inventory levels (adding / subtracting some columns together gets me to the actual "on hand" number as reflected from within Pastel).

My concern (and question):
1) We have about 1200 inventory items and every time I refresh the query, takes Excel about 10 sec - I can see it pulls quite a lot of resources and it sometimes "bombs-out" saying the memory on my local machine is full / or corrupt etc.
Is it possible to "ask" the server only to send the inventory levels of the specified inventory item? in stead of loading the entire inventory list to the local excel spreadsheet where it gets filtered?

2) The way I intend to use this query may result in two or more workstations running the same macro (which includes refreshing this query) from different machines. I therefor need it to be snappy and low on network resources.

Any ideas / advice appreciated!

(I'm by no means an IT expert. I'm probably more useful with a torque wrench and a hammer ; so please correct me if I'm at any point missing a much more obvious way to get to the same answer)


thank you!

[Alec Candiotes]

Hi there

Firstly well done for taking something as difficult as this. I can completely understand why you have gone the query route. Especially with your method of creating pick slips.

Refreshing ODBC queries takes a little bit of time, but 10 seconds is way to long for only 1 single query of 1200 lines. We have built BI Apps for Pastel and we query a massive amount of tables at the same time and some of them of 10's of thousands of lines and my experience is that a query set up correctly will be much quicker.

The reasons why it takes long though can vary. For instance your PC's spec can be a little low (small processor or to little memory or both). Another reason often is the way you land the data in Excel. Do you land it as standard text or in a data table? Finally, once you have queried data into Excel you should rather work with the data using data calculation principles than standard Excel formulas to get the answer you need. This speeds up the formula calculations dramatically (like by 10 times or more).

Regarding the loading of only some items during the query. Yes you can only call up specific items during a query. But I find this to be less effective in operations as you then need to manage what you call up consistently. Now simple queries become specialised and suddenly you have a dependency to use specialised programmers to manage it going forward instead of your own internal finance / ops team.

What many of our customers do with a crucial spreadsheet as like this, is that they turn it into an App / automated tool that runs in Excel. In this way you can make sure that it is user friendly and it can create usable answers quickly. Binding a view tables and creating connections between these tables often gets you the best result.

I you want, I'll be happy to have a look at your tool / spreadsheet over Teamviewer and give you some tips at no charge. You are welcome to have a look at the BI Apps that we have developed for Pastel Partner and Xpress which all operate in Excel on our website - https://mybi.co.za/.

I hope I have helped a little.

Have a great day!

Alec Candiotes
alec@mybi.co.za / 072 997 9553

[Kevin Smith]

Hi PMH

How are you drawing the data into excel? If you are pulling the entire multistoretrn table into excel, and then adding the values in excel to get Qtyonhand figure, it will be a little slower. Try perform the formula calculation in the query instead - this may speed up the report quite a bit. Below is an example of the query you could use in excel. You would need to change the formula in brackets to include all the fields you are looking for in the calculation ( eg opening qty, qty buy/sell/adjust last and all 13 periods for this year buy, sell and adjust,)

SELECT MultiStoreTrn.StoreCode, MultiStoreTrn.InvGroup, (qtybuythis01+qtybuythis02+qtybuythis02) AS 'qtyonhand'
FROM MultiStoreTrn MultiStoreTrn
WHERE (MultiStoreTrn.StoreCode>'000')
ORDER BY MultiStoreTrn.ItemCode

Let me know if that speeds up your query, or if you still need a hand.

[PMH]

Morning, thanks for the input.

Okay, Kevin your suggestion does speed up the query dramatically - thanx. My initial understanding was that the formula in the query performs the calculation AFTER pulling all data to Excel.
I used PowerQuery to calculate a new "on hand" column, delete all the unused columns and filter out the unused inventory items according to category.

The query now sends only 2 columns to my data table containing 500 lines - much much faster!


Alec, when you say "app inside Excel", do you mean they create buttons which links to macros to do a number of different processes?

I would like to contact you - will do so next week when all is back to normal after the long weekend - tx for the offer.

[Alec Candiotes]

Hi PMH

Apps can be fully automated where various processes run when opened or it can be button operated where macros execute when pressed.

Happy to assist you in next week.

Thanks Kevin for insight on how you write your SQL.

All the best!

[PMH]

Hi guys & girls,

So just a quick update: I've managed to speed up the process to pull data from Pastel's PSQL database tenfold by NOT using the built in Excel Query, but rather establishing a ADODB connection in VBA to the Pastel data source (set up in system DSN as a ODBC data sources).

Basically with the following code you can load any table from Pastel to Excel much much faster (and without Excel hanging):
Note: In this case I'm only selecting the Qty columns from MultiStoreTrn table as this is the only field relevant to me at the moment.

Hope this can help someone else.

---

'This is a workaround macro to pull data from Pastel Pervasive SQL using System DSN
'It is a much faster connection than using the "get data -> ODBC -> function in Excel.

Sub GetStock()

Dim adoConn As ADODB.Connection
Dim adoRs As ADODB.Recordset
Dim sSql As String
Dim StockExport As Worksheet
Set StockExport = Worksheets("Stocks")


ssqlstring = "SELECT ItemCode, OpeningQty, QtyBuyThis01, QtyBuyThis02, QtyBuyThis03, QtyBuyThis04, QtyBuyThis05, QtyBuyThis06, QtyBuyThis07, QtyBuyThis08, QtyBuyThis09, QtyBuyThis10, QtyBuyThis11, QtyBuyThis12, QtyBuyThis13, QtyAdjustThis01, QtyAdjustThis02, QtyAdjustThis03, QtyAdjustThis04, QtyAdjustThis05, QtyAdjustThis06, QtyAdjustThis07, QtyAdjustThis08, QtyAdjustThis09, QtyAdjustThis10, QtyAdjustThis11, QtyAdjustThis12, QtyAdjustThis13, QtySellThis01, QtySellThis02, QtySellThis03, QtySellThis04, QtySellThis05, QtySellThis06, QtySellThis07, QtySellThis08, QtySellThis09, QtySellThis10, QtySellThis11, QtySellThis12, QtySellThis13, QtyBuyLast, QtyAdjustLast, QtySellLast " & _
"From MultiStoreTrn " & _
"Where StoreCode = '001'"

Set adoConn = New ADODB.Connection
adoConn.Open "DSN=PastelQuery"

Set adoRs = New ADODB.Recordset

adoRs.Open ssqlstring, _
ActiveConnection:=adoConn

StockExport.Activate
StockExport.Range("F2:AW10000").ClearContents
StockExport.Range("F2").CopyFromRecordset adoRs
StockExport.Columns("b:dd").NumberFormat = "General"
Set adoRs = Nothing
Set adoConn = Nothing

End Sub

---

[Alec Candiotes]

Thanks for sharing PMH. I am happy you came right.