Sage 100

Have a client that just realized that Biz Alerts hadn't been working for about 3 weeks. It seems that there is a maximum storage for some file. It blew out all the alerts. He's compressed the file and cleared some things out, but is having trouble rebuilding an import Alert. They are still on 4.05 (update to date in maintenance, but not upgrading). They are checking open and history SOs for duplicate Customer POs. They only want different SOs with the same PO number. However, he's getting all the SOs with the same PO number. I'm not skilled at Alerts. Anyone know how to get the query to bring back the correct data? Right now he's getting all SOs with the same cust PO number. It's not bringing back just the ones with the same PO, but diff SO numbers.

Regarding the ""some file"" it is the Access MDB file that Alerts is based on. This file cannot grow over 1 GB or Alerts will stop functioning.

Is this the log file that grows to a maximum size then shuts things down? I seem to remember this happening and I think @DawnAnastasi caught it and steered me in that direction. I also think one way to re-work these queries is via Crystal then take the SQL code Crystal generates and use it in BA? I'm not all that skilled her either -- just some .02

And it apparently wipes out the alerts when it reaches that size. So they are having to rebuild them all. This one is critical. I keep trying to get them to upgrade, but they aren't buying. They believe they are stable and have heard too much about the ""newer"" versions.

I recall the ""log file"" being the problem at times. Rename the existing log file and define a new log file in Access.

Re-creating the log file would be a good monthly/quarterly maintenance task to avoid this situation.

I'll pass that suggestion along @LeeGraham , but we are still having problems figuring out how to write the SQL. @WayneSchulz, I'll suggest trying to do it in Crystal...cool option. Anyone else have a SQL query to do this?

Do they have any backups of the .tsk files from before the log blew up? Those contain the alert definitions. Also, if you do a View SQL from within the alert and post it here I might be able to see what is wrong with the query currently.

I'll take a look but I think our sole BA client has an alert that runs weekly to clear out the log file.

The SQL below is intended to resolve the following three needs: 1: Check for new or changed records in the SO Entry file with the same Customer PO and Customer Code( but CC not equal to ""DRI"" ) and Customer PO Number does not contain ""VBL"" or ""RESHARP"" 2: Output the current new or changed record's SO Number along with the Current UserID (linked to AR's Sales Person Number by the ID) email address, and list all other SO Numbers with that same PO#. 3: Send a single email to the UserID's(via AR's SalesPersonNumber) email a message that has the current SO Number and lists all the matching records. SELECT ""SO1_SOEntryHeader"".SalesOrderNumber, ""SO1_SOEntryHeader"".CustomerPONumber, ""SO_03SOHistoryHeader"".SalesOrderNumber, ""SO_03SOHistoryHeader"".CustomerPONumber, ""ARD_SalespersonMasterfile"".Name, ""ARD_SalespersonMasterfile"".EmailAddress, ""SO1_SOEntryHeader"".SalesOrderNumber +""SO1_SOEntryHeader"".CustomerNumber AS SONumberCCNumber FROM ""SO1_SOEntryHeader"", ""SO_03SOHistoryHeader"", ""ARD_SalespersonMasterfile"" WHERE ((""SO1_SOEntryHeader"".SalesOrderNumber <> ""SO_03SOHistoryHeader"".SalesOrderNumber) AND ((""SO1_SOEntryHeader"".UsrId = ""ARD_SalespersonMasterfile"".SalespersonNumber) AND ((""SO1_SOEntryHeader"".CustomerNumber = ""SO_03SOHistoryHeader"".CustomerNumber) AND (""SO1_SOEntryHeader"".CustomerPONumber = ""SO_03SOHistoryHeader"".CustomerPONumber)))) AND ((""SO1_SOEntryHeader"".CustomerPONumber NOT LIKE 'VBL%') AND (""SO1_SOEntryHeader"".CustomerPONumber NOT LIKE 'RESHARP%') AND (""SO1_SOEntryHeader"".CustomerPONumber <> ' ') AND (""SO1_SOEntryHeader"".CustomerNumber <> 'DRI'))

That looks correct. Try stripping it back to just the SO number and PO number fields: SELECT ""SO1_SOEntryHeader"".SalesOrderNumber, ""SO1_SOEntryHeader"".CustomerPONumber, ""SO_03SOHistoryHeader"".SalesOrderNumber, ""SO_03SOHistoryHeader"".CustomerPONumber FROM ""SO1_SOEntryHeader"", ""SO_03SOHistoryHeader"" WHERE ((""SO1_SOEntryHeader"".SalesOrderNumber <> ""SO_03SOHistoryHeader"".SalesOrderNumber) AND (""SO1_SOEntryHeader"".CustomerPONumber = ""SO_03SOHistoryHeader"".CustomerPONumber)) If that works, then add back in the other restrictions one by one.

Another reason to insure that back ups are looking at ALL parts of MAS and are being retained for more than two weeks. In that same vein, end users should be saving EVERY modified Crystal report, TSK program and any other customized bit of data (VI's) in a separate folder for safe keeping.

Thanks everyone...working on all your suggestions! appreciate all of your input!