Sage 100

Freight Tracking Table We are in the process of creating an integration with a web store, fedex ship manager and an excel spreadsheet which will capture all the information on the order and the shipping costs. The integration is basically done with the webstore and captures the shipping charges which are calculated in Magento. FedEx Ship Manager is out-putting the estimated freight charge with the tracking numbers to an Access database along with the order number and tracking number for easy capture. The customer also gets an excel extract with the actual freight costs which often differ for address correction, residential delivery or other un-anticipated up charges. Let me know if you agree that it would be best to track the estimated and actual cost information in a UDT rather than extending SO tables because the orders are mainly prepaid and the captured as sales orders to facilitate shipping processes. Then when the order is shipped it is invoiced in Sage and all this data needs to be passed to the invoice. Capturing in seperate UDT may be simpler for reporting and UDF maintenance. Any words of wisdom from the GURUs out there?

A header UDF can pass from the SO to the Invoice to Invoice History.

But it wouldn't be associated with the tracking number which is where the costs are matched. The package is the basis of cost and a majority of orders are multi-package.

Timing has always been a bit of rattlesnake when I work on these kinds of projects... I mean, is it shipped before or after invoice creation?

be careful with the UDT because you are limited in the way the key is defined. Unless there is a feature I am unaware of, you cannot create a key by combining separate fields. The result is you must concatenate one or more Magento fields to create a unique key. It store the information fine, but trying to retrieve it later can be tricky and inefficient.

@DougHiggs - I noticed the unique endearing quality of the key field being defined first and limited.

I was thinking of using the Tracking # as the actual key. They are all FedEx, so they should be unique. I would then have the cost fields and the Sales Order number as the cross reference. They are always shipped before invoicing.

Any thoughts on how to clean your UDT (or other storage location) once invoice is created?

Don't want to clean it. Want to be able to report after the fact on shipping margins and create exception reports when actual > expected or actual > charged. BUT you are right that the table could become real big real fast. Perhaps best to keep out of Sage?

@GaryFeldman LOL... If you only need one field (tracking number) as the key then you should be OK to use the UDT.

@GaryFeldman you would want to have a purge program for the UDT. Either BOI if it can be done or have an MD create one. If they are on Premium then just schedule a query.

No such luck. They are standard. I will have all the data in SQL and Access databases except for the cost which is an Excel spreadsheet downloand. Otherwise I would just create a view of the data and report against a view of Sage data and real database data.

Hard to get my head wrapped around this issue. So the web store generates a freight number for the SO that is what the customer pays? FedEx manager also generates a number for you that is nearly the actual amount and then you get an Excel dump from FedEx of the actual amount (after adjustments)? I am also assuming this is PVX and not SQL. Is it also important to track these charges by package or is total sufficient? Major league headache..... If you can deal with totals, and depending whether you have direct linkage (BOI scripting) with the FedEx manager, then use web site freight charge as billable freight on the order, create a UDF for the FedEx Mgr amount. Then downstream, import the Excel data into invoice history header UDF. Then you can run reports 50 ways to Sunday. And in reality, what is having the detail package information going to benefit? If having the package detail information is important, then I would recommend importing the web site information into Sage as usual, maybe even import the FedEx Manager stuff. I would then create a couple pass through queries into Access pulling the data out of Sage, import the Excel data into another Access table. Then write a select query to tie the three tables into one Access. Once done, write a Crystal report to report on the data or use Excel Queries or Pivot tables to analyze the data.

Why do you need to move the data out of the Fed Ex Access database? An event script on the Invoice Table Pre-Totals event could do an ADO query to gather your data for the invoice. If the Access 2 GB limit is an issue then a subordinate table could be used just to hold tracking data.

First, thanks everyone for their input. Of course the situation is more complex. This company is a lot of things and Sage 100 has been barely used. We are doing this first site as a proof of concept for a company that has >3 dozen of these. I have to be as unobtrusive as possible to the organization because they have a lot of moving peices. Literally they also do print production and mailing services so I have a potential job ops opportunity if we do this one well. The complexity from a technical standpoint is that FedEx integrates with a custom Central Data Base that gets data from all 40ish sites and retrurns tracking numbers to them. that system is also a cluster, so I am trying not to mess with it until we create magic and replace the whole shooting match with 100!

Good Luck. You Da Man!!!! Keep us posted and please continue asking questions. We all love solving problems.