New post
0

Event Based Schedule question

Hello,

I have a standard schedule report to export a file containing invoice information to be uploaded on Finance's database. The problem is that I only have a invoice added to the system date to work with, depending on the invoice status the report will only send completed invoices added on to the system in the last 7 days.
So far so good, however if the invoice status is incomplete for more than 7 days the report will not pick up on these records.
I am trying to add an event based schedule in order to solve this problem.
The dataset comes from a ODBC data source which I can find under the DSN Name list, and when it connects I can find the table I need. However when I try to build the query and click Parse I get the following error:

[Cache ODBC][State : S0002][Native Code 30]
[\PC17357\ChristianSteven\CRD\crd.exe]
[SQLCODE: <-30>:<Table or View not found>]
[Cache Error: <<SYNTAX>errdone+3^%qaqqt>]
[Location: <Prepare>]
[%msg: < Table 'SQLUSER.XORDER_INVOICE' not found>]

Any ideas?

Thank you very much in advance :)

6 comments

  • 0
    Avatar
    Sarah Turnwald

    Hi Martin,

    The SQLUSER.XORDER_INVOICE table being selected when building your query is not being picked up by CRD. It is possible that this table is not visible within the crystal report.

    The ODBC Data Source will have all the views/tables available for selection based on the user credentials Can you confirm that the credentials in the ODBC Data Source is the same that you are entering when selecting the DSN in the Event Based schedule?

    If the credentials are the same, then having the query to review will help. Another possible issue based on the information provided is that there is a syntax issue with the query itself.

  • 0
    Avatar
    Martin Ham

    Hello Sarah,
    Thank you for your reply.

    The system DNS is set up with system access rights, I also tried using the system login but no luck there.

    I can certainly see the table and access the information through crystal reports, CRD is able to find the data source and display the table as well as the fields in the table when making the selection, but once Parse is clicked we get the error.

  • 0
    Avatar
    Sarah Turnwald

    Hi Martin,

    CRD is looking for the SQLUSER.XORDER_INVOICE table. Can you confirm in the database, that this is exactly how the database table is written? If it is the exact same, I would need the query behind what you've selected in the fields (using the build button). Will it be possible for you to provide this?

  • 0
    Avatar
    Martin Ham

    Hi Sarah,

    I am not sure if it is supposed to be SQLUSER.XORDER_INVOICE

    The path which is in crystal is CACHE/JAC_Super/xorder_invoice, but how can I change this since all I can do is make a selection in CRD?

    Also the query I am running is extremely simple, it is just to pick up on invoices with status C and B, so I don't think it is the cause of the problem.

    Thank you!

  • 1
    Avatar
    Sarah Turnwald

    Hi Martin,

    The SQLUSER.XORDER_INVOICE that is being referenced is a table in the database which you have selected to connect to via the DSN. You will not locate the full name of this table unless you log into the system (i.e. SQL Management Studio) where the database is being stored. The path you provided appears to be a copy of the actual crystal report.

    Once you confirm that the table name within the database I am almost certain you will see that it is not what was pulled.

    You would then simply go to the Properties of the Event Based schedule, connect to the DSN and from here click 'Build' which will allow you to see the query behind the tables. You can then manually edit the table name as it appears in the database.

    Hope this helps!

  • 0
    Avatar
    Martin Ham

    Yay! We got it working.
    The path was not complete all it needed was "JAC_Super"."xorder_invoice".* etc..

    Thank you very much Sarah!

    May I add another question to this thread:

    Is it possible to specify a field for CRD to look at constantly for change, rather than any change on the records?

    In my example I'd like CRD to run a report when the invoice status changes from incomplete to complete for invoices processed under certain dates.

    Here is my current query, but I think CRD will look for any changes to these records and I'd like to focus it on invoice_status:

    SELECT "JAC_Super"."xorder_invoice".* FROM "JAC_Super"."xorder_invoice" WHERE "invoice_status"='I' AND NOT ("invoice_passed_date">= '<[m]CurrentDate;-7;yyyy-MM-dd>' AND "invoice_passed_date"<= '<[m]CurrentDate;yyyy-MM-dd>') AND "order_no" NOT LIKE 'FRU%'

Please sign in to leave a comment.