We currently send tracking information to customers with Sage Alerts (KnowledgeSync).  

We do the same thing and use Knowledge Sync for this.

I just downloaded and installed the KS event pack for Sage 100 but the closest event it has is the order confirmation.

I don't see the shipping tracking # column in there.

Can you tell me how you did it? Is the tracking number in the email linked to UPS or FedEx tracking?

Is there an event just for that? I would think that would be one of the most used and basic event!!

Thanks

The basic KS Event pack does not have that. You have to create your own. It would be best to work with your reseller. KS is pretty easy to leave.  If you know Crystal you can pick up KS quick.

Step 1: I created an ODBC connection to Sage 100 that auto-logged in to the company

Step 2: I linked the necessary tables to an Access database using this ODBC connection

Step 3: I created the following SQL query in Access:

SELECT

AR_InvoiceHistoryHeader.ShipToName AS ShipToName

, AR_InvoiceHistoryHeader.ShipToAddress1+'<BR>' AS ShipToAddress1

, AR_InvoiceHistoryHeader.ShipToAddress2+'<BR>' AS ShipToAddress2

, AR_InvoiceHistoryHeader.ShipToAddress3+'<BR>' AS ShipToAddress3

, AR_InvoiceHistoryHeader.ShipToCity, ', '+AR_InvoiceHistoryHeader.ShipToState AS ShipToState

, AR_InvoiceHistoryHeader.ShipToZipCode+'<BR>' AS ShipToZipCode

, AR_InvoiceHistoryHeader.ShipToCountryCode AS ShipToCountryCode

, AR_InvoiceHistoryDetail.InvoiceNo AS InvoiceNo

, AR_InvoiceHistoryDetail.ItemCode AS ItemCode

, AR_InvoiceHistoryDetail.ItemCodeDesc AS ItemCodeDesc

, AR_InvoiceHistoryHeader.CustomerNo AS CustomerNo

, AR_InvoiceHistoryHeader.ShipDate AS ShipDate

, AR_InvoiceHistoryDetail.UnitOfMeasure AS UnitOfMeasure

, AR_InvoiceHistoryTracking.TrackingID AS TrackingID2

, AR_InvoiceHistoryHeader.ARDivisionNo AS ARDivisionNo

, SO_ShipToAddress.EmailAddress

, AR_InvoiceHistoryHeader.InvoiceNo+AR_InvoiceHistoryDetail.DetailSeqNo AS InvoiceLineItem

, AR_InvoiceHistoryHeader.CustomerPONo AS CustomerPONo

, AR_InvoiceHistoryHeader.SalesOrderNo AS SalesOrderNo

, SO_ShippingRateHeader.ShippingCodeDesc

, Left([SO_ShippingRateHeader].[ShippingCodeDesc],InStr([SO_ShippingRateHeader].[ShippingCodeDesc]," ")-1) AS CarrierOld

, Format(AR_InvoiceHistoryDetail.QuantityShipped,'#,##0.000') AS QuantityShipped

, IIf([AR_InvoiceHistoryDetail].[QuantityBackordered]=0,0,Format([AR_InvoiceHistoryDetail].[QuantityBackordered],'#,##0.000')) AS QuantityBackordered

, AR_InvoiceHistoryHeader.ShipToCode

, [AR_InvoiceHistoryHeader]![ARDivisionNo] & [AR_InvoiceHistoryHeader]![CustomerNo] & [AR_InvoiceHistoryHeader]![ShipToCode] & [AR_InvoiceHistoryHeader]![ShipToCity] & [AR_InvoiceHistoryHeader]![ShipToState] & [AR_InvoiceHistoryHeader]![ShipToZipCode] AS ShipToCustomerNo

, IIf([AR_InvoiceHistoryHeader].[ARDivisionNo]='02','123 main st <font color=red>|</font> small town <font color=red>|</font> ca <font color=red>|</font> 92222<br><font color=red>o</font> 800.000.1234 <font color=red>f</font> 888.888.3888</font>','1234 lakeshore dr <font color=red>|</font> larger town <font color=red>|</font> ca <font color=red>|</font> 92223<br><font color=red>o</font> 866-667-8658 <font color=red>f</font> 888.879.4247</font>') AS OurCompanyAddress

, IIf([AR_InvoiceHistoryHeader].[ARDivisionNo]='02','Division 2','Divion 1') AS OurCompanyName

, IIf(AR_InvoiceHistoryHeader.ARDivisionNo='02','','') AS OurCompanyLogoLink

, IIf(Left(AR_InvoiceHistoryTracking.TrackingID,2)='1Z','UPS',IIf(Left(AR_InvoiceHistoryTracking.TrackingID,2)='GE','TNT',IIf(Len(AR_InvoiceHistoryTracking.TrackingID) In (12,14,15),'FedEx',IIf(Left([SO_ShippingRateHeader].[ShippingCodeDesc],InStr([SO_ShippingRateHeader].[ShippingCodeDesc]," ")-1)='DHL','DHL','')))) AS Carrier

, IIf(IsNull(AR_InvoiceHistoryTracking.TrackingID),'',IIf([Carrier]='',AR_InvoiceHistoryTracking.TrackingID,'<a href=' & IIf([Carrier]='UPS','wwwapps.ups.com/.../track([Carrier]='FedEx','www.fedex.com/Tracking([Carrier]='DHL','www.dhl-usa.com/.../tracking.shtml([Carrier]='TNT','www.tnt.com/.../tracking.do)))) & AR_InvoiceHistoryTracking.TrackingID & '>' & AR_InvoiceHistoryTracking.TrackingID & '</a>')) AS TrackingLink

FROM (((AR_InvoiceHistoryHeader

LEFT JOIN AR_InvoiceHistoryTracking ON AR_InvoiceHistoryHeader.InvoiceNo = AR_InvoiceHistoryTracking.InvoiceNo)

INNER JOIN SO_ShipToAddress ON (AR_InvoiceHistoryHeader.ARDivisionNo = SO_ShipToAddress.ARDivisionNo) AND (AR_InvoiceHistoryHeader.ShipToCode = SO_ShipToAddress.ShipToCode) AND (AR_InvoiceHistoryHeader.CustomerNo = SO_ShipToAddress.CustomerNo))

INNER JOIN AR_InvoiceHistoryDetail ON (AR_InvoiceHistoryHeader.InvoiceNo = AR_InvoiceHistoryDetail.InvoiceNo) AND (AR_InvoiceHistoryHeader.HeaderSeqNo = AR_InvoiceHistoryDetail.HeaderSeqNo))

INNER JOIN SO_ShippingRateHeader ON AR_InvoiceHistoryHeader.ShipVia = SO_ShippingRateHeader.ShippingCode

WHERE (((Format([AR_InvoiceHistoryDetail].[QuantityShipped],'#,##0.000'))>0) AND ((AR_InvoiceHistoryHeader.InvoiceDate)>=Date()-3))

ORDER BY [AR_InvoiceHistoryHeader]![ARDivisionNo] & [AR_InvoiceHistoryHeader]![CustomerNo] & [AR_InvoiceHistoryHeader]![ShipToCode] & [AR_InvoiceHistoryHeader]![ShipToCity] & [AR_InvoiceHistoryHeader]![ShipToState] & [AR_InvoiceHistoryHeader]![ShipToZipCode];

Step 4: Test the query in access to make sure it works. It might be a slow depending on how many records you have.

Step 5: I created a query in knowledge sync:

I selected the SQL query in access (this may be a little slow) and selected all fields.

Step 6: I created an event in knowledge sync:

Description Tab: Unchecked repeat notification

Queries: select your query

Reports/Files/Copy: No action required

Email Subject: {OurCompanyName} Shipment Notice

Message Text:

{BEGIN*HTML}

<table>

<tr><td>Customer Acct#:</td><td>{ARDivisionNo}-{CustomerNo}</td></tr>

<tr><td>Ship Date:</td><td> {ShipDate}</td></tr>

</table><p>

<b>Shipped To:</b><BR>

{ShipToName}<BR>

{ShipToAddress1}{ShipToAddress2}{ShipToAddress3}{ShipToCity}{ShipToState} {ShipToZipCode}{ShipToCountryCode}<BR><BR><BR>

To Our Valued Customer,<BR><BR>

Please note that the following items have shipped as of today.<BR><BR>

<TABLE cellpadding=3 border=1>

<TR>

<TH><font face=verdana size=-1>Customer PO#: </TH>

<TH><font face=verdana size=-1>Sales Order#: </TH>

<TH><font face=verdana size=-1>Item Code:</TH>

<TH><font face=verdana size=-1>Description:</TH>

<TH><font face=verdana size=-1>Qty Shipped: </TH>

<TH><font face=verdana size=-1>Qty Back Ordered: </TH>

<TH><font face=verdana size=-1>UOM: </TH>

<TH><font face=verdana size=-1>Carrier</TH>

<TH><font face=verdana size=-1>Tracking</TH>

</TR>

{BEGIN*REPEAT}

<TR>

<td><font face=verdana size=-1>{CustomerPONo}</td>

<td><font face=verdana size=-1>{SalesOrderNo}</td>

<TD><font face=verdana size=-1>{ItemCode}</TD>

<TD><font face=verdana size=-1>{ItemCodeDesc}</TD>

<TD align=right><font face=verdana size=-1>{QuantityShipped}</TD>

<TD align=right><font face=verdana size=-1>{QuantityBackordered}</TD>

<TD><font face=verdana size=-1>{UnitOfMeasure}</TD>

<TD><font face=verdana size=-1>{Carrier}</TD>

<TD><font face=verdana size=-1>{TrackingLink}</TD>

</TR>

{END*REPEAT}

</TABLE><br><br>

<font face=verdana size=-1>Thank you,<BR><BR>

The {OurCompanyName} Shipping Department<BR>

<img src={OurCompanyLogoLink}><BR>

{OurCompanyAddress}

{END*HTML}

Subscriber Advanced:

Message Break Database Field: ShipToCustomerNo

Database field to email: EmailAddress

Schedule: Add a schedule

Step 7:

Set the ship to address email addresses in Customer Maintenance - those customers that have them will get tracking numbers email

NOTES:

The message board might have messed up some of the formatting

Woa dude you don't have to do all that in Access, KS will do that for you.

I was wondering about that, is there a simpler way to accomplish this?

KS doesn't easily do a lot of that - if I tried to link that many tables in KS the query did not work. Also KS makes it hard to have IF/ELSE statements to set up the tracking links and have different signatures / subjects for each division.

Even if you do it straight in KS it won't be much simpler.

Did you know that your Access part will run a lot faster if you replaced the linked tables with SQL Specific Pass Through Queries. Also unless you are instantly posting the invoices I would suggest running if against the SO Invoice Data Entry tables.

I actually much prefer running it against the history tables so that we don't just have a small window to run it (we update within an hour) and we can run it again if it fails the first time. Because it's running automatically on a server it really doesn't matter if the query takes 90 seconds to run.

Tell me more about these SQL Specific Pass Through Queries? Can you give me an example?