Sage 100

Linking SO Order History to AR Invoice History to show performance of shipping by promise date - Problems... When I link the SO_SalesOrderHistoryDetail to the SO_SalesOrderHisoryHeader table, the crystal report runs and runs and runs.... The SO History detail table only has 176K records, Header has 40K records - not large sizes. If I can't get this step to work, then bringing Invoice history into the mix to measure sipping by promise date isn't going to happen. Anyone have insights on how to measure promise date performance?

For the future, Create a UDF to store the promise date in ARHistory. I have a client that we did this for last summer. Create a Access PTQ for matching the two tables. Crystal will run at least 10X faster!!!!

Wow, sounds familiar. I will have to look at my reports.

Any way you could add that table to a canned report so at least part of the table would be optimized?

You should be able to create a new report setting in AR Invoice History Report and link in the SO header or detail table (depending if lines had own promise date). This would utilize the AR_InvoiceHistoryWrk file.

The CFO is the one asking for the data, and he rarely opens MAS. His preference is something directly in Excel @JeffSchwenk - The promise date is already a field in the AR Invoice History detail table, so don't need a UDF now. I think for now I will present 2 data views. One worksheet showing the data from AR Invoice History showing all the shipment info. One worksheet of SO Sales Order detail showing only those records that have not shipped at all. But this does point out one of the holes in the MAS data structure, just like not being able to tell what FIFO tiers an invoice line pulled from.

Sounds like a good argument to use PTQs with a Pivot Table.

PTQ, PTQ, PTQ... All I hear about is PTQ! Now I distinctly remember that I have done PTQs in the past on earlier versions of Access, but this customer has Access 2007 or higher. I have downloaded Big Louie's word document on setting up a PTQ, but I'm just not getting something. These are the steps I do: 1. open Access. 2. Create Blank Database 3. Close the Table1 screen. 4. Click on Create. 5. Click on Query Design. 6. The table names dialog displays, so I close that. 7. Click on Pass Through Query. 8. Click on Property Sheet. 9. Paste in the ODBC connection string example I got from Jeff Schwenk years ago and modify it accordingly ""ODBC;DSN=sotamas90;UID=XXX|xyz;PWD=XXXXXX;Directory=\\BLS007\Apps\Sage\MAS\Client\v440\MAS90\;EnforceDouble=1;StripTrailingSpaces=1;SILENT=1;SERVER=NotTheServer"" note - make sure you copy over the existing ""ODBC"" in the connection string window. 10. I paste in the SQL statement I created in a Crystal Report to the Query1 window. 11. Close the query and save it with a meaningful name. 12 In the Queries List on the left, click on the query I just created. 13. IT WORKED! YEAH! Thank you once again 90 mInds, you've helped me work through some childhood trauma and come out a better person :) Maybe we should expand this to a self help counseling service...

Here are Doug Jenkins instructions and examples from his PTQ session at MOTM 2014. I went through it. It's excellent.

Now the next question on using PTQs in Excel: I open Excel, go to the data menu, and try getting Data from Access. But when I point to the database with the PTQ I just created, I get the message that I have no tables available. Does this mean that the only way I can use a PTQ in Excel is via MS Query? If so, I'm running into an issue with accessing a non-drive mapped location. Does a Make Table Query apply here? @RonGouveia - thanks for the link. I'll read that too.

When you click on the PTQ in Access, does it return data, if so, go to next step. if not, your PTQ needs to be fixed. Then create a link to the Access database using the Excel query. Data is returned to Excel. If you are linking MAS tables, create an Access select query pulling from your PTQ queries so that only the data you are interested in displays. Then direct your Excel query to the Access select query. Then run your pivot table from there. If you don't need to pull the raw data into Excel, you can create your pivot table to pull directly out of access. If you are more comfortable with Crystal, create a crystal report using the Access select query as the database. The older versions of Crystal do not recognize the 2007 version extensions so you would have to save the Access Db as 2003. This might sound confusing but it really is quite easy.

After reading Dougs document, I created a select query using the PTQs I had set up. Then in Excel / Data I was able to connect to the Select Query table. This does a copule things for me better than using MS Query. 1. It appears to be faster and more flexible than using MS Query i.e. not limited to mapped drives. 2. If the Sage data is moved, you simply change the connection strings in the one access database, not on all the Excel Queries out there in the universe. You know, 5 years ago I knew this. The joy of getting older is you get to learn things all over again :)

Exactly.....