Ksync query returns records, but not sending out Email Alerts in the application events 1

[vbdbcoder]

The query is very simple, to get a list of today's AP invoices that had different TERMS than the AP Vendor Set up. The query returns records correctly as below:

SELECT dbo.APVEN.VENDNAME AS "VENDNAME", dbo.APIBH.AMTDUEHC AS "AMTDUEHC", dbo.APIBH.IDINVC AS "IDINVC", dbo.APIBH.IDVEND AS "IDVEND", dbo.APIBH.DATEINVC AS "DATEINVC", dbo.APIBH.TERMCODE AS "InvoiceTERMCODE", dbo.APIBH.TERMSDESC AS "InvoiceTERMSDESC", dbo.APVEN.TERMSCODE AS "VendorTERMSCODE" FROM (dbo.APIBH LEFT OUTER JOIN dbo.APVEN ON dbo.APIBH.IDVEND = dbo.APVEN.VENDORID) WHERE dbo.APIBH.DATEINVC >= {%Last Friday Date YYYYMMDD%}
AND dbo.APIBH.TERMCODE <> dbo.APVEN.TERMSCODE
ORDER BY dbo.APIBH.IDVEND ASC, dbo.APIBH.IDINVC ASC

But the application events never executes and get it send the email.

Where could be the issue to look at?

 

[Jay Converse]

What is "Ksync"?

 

[vbdbcoder]

KnowledgSync

 

[Jay Converse]

Ah, the new name for Alerts server.

This is basic stuff, check your work and your setup. KnowledgeSync is a simple creature, if you look at the samples, you'll have all the info you need.

 

[vbdbcoder]

That is what I am hoping... The problem seems to be the sql table key... a simple select query won't be able to define a table key for the program to track the changed record (for the configuration requires a table unique key, while a dataset selected won't have that).

Now... that I am thinking... I need a table to store the returned record so as to use them in Alert, since I will have a unique key there defined by my own table.

Do you recall you had to do that back in the old Alert Server? I believed I did it once or twice.

 

[Jay Converse]

You don't understand how it works, it maintains its own old records. No "table keys" are required, Ksync does it for you.

 

[DjangMan]

You might have to create a database view so that KSync doesn't have to handle that join. We ran into an issue where the program couldn't handle a 'simple' join of PO tables. We had to create a view to simplify the output for the program's sake. FWIW, I commonly write views for Alerts, too.

 

[vbdbcoder]

Hi DjangMan,

If I push the records to a table (with an ID PK field) and monitor that table for change records, then KSync can detect and send the alert. That means I will run some kind of stored procedure to collect data and populate to a staging table. For a daily executed SQL, I don't mind running the SQL stored procedure for simple join, to get data to a table for Ksync to use. However, it will be expensive to detect change and execute based on join sql, not direct table data change.

Would you be able to provide a sample on how you detect change in a view for a simple join like mine?

Thanks

 

[DjangMan]

Your view is going to create a recordset. Have it return the last 7 days of data and make sure that you include some unique fields within the query. Those unique fields will help KSync to know what is 'new' and what is 'old'. When 'new' records appear in your view results then KSync will handle those records and mark them in its internal database.

Caveat: I haven't done much work with KSync itself. Someone asked me to help with a query that KSync didn't seem to be able to handle because of the join so we created the view and then they didn't need my help after that.

 

[vbdbcoder]

Thanks DjangMan. Your solution works.