Help with Notes and reporting

[lvaleri]

I am trying to write a crystal report the includes information on customers. I have no problem with the general info, but I also need to include in the report the last two dates that notes where made to that account. I know the info is in sysnotes_sql but how or what do I link that to the arcusfil and how do I pull out just the dates for the last two notes.

Macola 7.5, Crystal 8.5

 

[crystalreporting]

If you already have a main report with the ARCUSFIL_SQL information, add a subreport to the SYSNOTES_SQL table. The subreport link will be ARCUSFIL_SQL.Cus_No to SYSNOTES_SQL.Name_Value. In your subreport, make sure you also add criteria to just show AR-CUSTOMER notes e.g. SYSNOTES_SQL.Note_Name = "AR-CUSTOMER".

Only showing the last two dates is a little tricky - it would be easier if you were wanting to show the last two entries, and maybe that's what you're trying to achieve. The last two dates could comprise of 50 notes as you can obviously have more than 1 note entered against a customer per day. Depending on what you want to do, simply get your notes sorting in the correct order, then add a running total or record count field and add selection criteria to only show the top or bottom two results.

Peter Shirley

http://www.linkcorp.com

 

[lvaleri]

Well I cannot seem to link the Cus_No field to the Name_Value field. Do I actually have to create the link?

 

[crystalreporting]

If you've added the system notes as a subreport, then yes there is an area within Crystal where you 'define' the link between the main report and subreport.

Peter Shirley

http://www.linkcorp.com

 

[dgillz]

lvaleri,

You most likely have to use a subreport to do this. What other tables are in your report?



Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com