Debtors Invoice

[Steve Hutchins]

Hi,

I need to amend the standard debtors invoice so if there is a debtor specific part number it will be displayed instead of our part number. The crystal report use a stored procedure for the data and I've tried to link the debtor specific field to the stored procedure and onto the report.

Obviously I'm doing something wrong as I can get it to show the debtor specific part number.

Can somebody either give me a clue or possible send me their invoice format?

Thanks
Steve

[Mike.Sheen]

The crystal report use a stored procedure for the data and I've tried to link the debtor specific field to the stored procedure and onto the report.

Obviously I'm doing something wrong as I can get it to show the debtor specific part number.

Hi Steve,

If I understand your question correctly, you've managed to modify the stored procedure to include your new field, but Crystal Reports does not see it?

I've encountered this many times - Crystal is somewhat stubborn / senile / recalcitrant when it comes to recognising new fields in a stored procedure - but it can be done!

I'll update this post with some step by step instructions with screenshots to show how to achieve what you need - so standby!

Mike

EDIT: Here are the steps:

NOTE: The image preview on these forums may not display the entirety of images - click on any images to see the full image

Step 1. Firstly, you should not modify our stored procedure, but make your own from a copy of ours. This is recommended because whenever Jiwa is updated we will re-create all our stored procedures - so any changes you make will be lost. The same applies to the report file - copy that and upload it with renamed file name via the Reports form.

Doing so is quite simple, if Management Studio, just right click on our stored procedure and select "Modify" - you'll see something like this:

Step1-Modify.PNG (9.25 KiB) Viewed 12919 times

Now, edit the code and change the ALTER to a CREATE and name your stored procedure to be identifiable an unique. In this example, the standard stored procedure is named "usp_Jiwa_Invoices_Invoice", so I'm going name my modified stored procedure to be "usp_Jiwa_MYINVOICE_Invoice"

Step1.1-AlterAndRename.PNG (8.4 KiB) Viewed 12919 times

You will also (obviously) need to modify the stored procedure to output your new field - in this example I just added a field named "MyField".

Run your script to create your new stored procedure.

You will also need to grant permissions - run the following command within management studio:

grant_all_user_tables

Step 2. Set Datasource Location. Open the report to edit, and choose "Set Datasource Location" from the database menu. You'll need to click and highlight the stored procedure in the current datasource area, then find your new stored procedure in the Replace with section (to find your new stored procedure you will be prompted for connection details) - then press the Update button

Step2-SetDatasourceLocation.PNG (19.74 KiB) Viewed 12919 times

Crystal will prompt you to enter a value for the invoicehistoryID parameter - enter one if you like, but at this point you don't have to provide a valid parameter

Step 3 Database Expert - rename stored proc. Despite you telling Crystal in step 2 to update the data source to be your new stored procedure, it will still show the name of the old one. From the database menu, choose "Database Expert" - and right click the "table" in the selected tables to be match your stored procedure name.

Step3-DatabaseExpert-RenameSP.PNG (19.55 KiB) Viewed 12919 times

Step 4 Verify the database. Choose from the database menu the "Verify Database" option. Crystal may (or may not - it's fickle) again prompt you for parameter values - enter in valid or dummy values.

Step4-VerifyDatabase.PNG (8.83 KiB) Viewed 12919 times

Step 4.1 Crystal will then prompt for connection details AGAIN - enter them as you did in step 2.

Step4.1-VerifyDatabase-ConnectionDetails.PNG (7.58 KiB) Viewed 12919 times

Step 4.2 Crystal will then give it's first hint that it recognises there is something different by showing this dialog:

Step4.2-VerifyDatabase-FixUpDatabaseConfirmationDialog.PNG (5.09 KiB) Viewed 12919 times

Press ok to that dialog.

Step 5 Crystal still won't be showing your new field, so perform Step 2 (Set Datasource Location) Again.
Now, you should see your new field appear.

Finally.PNG (4.44 KiB) Viewed 12919 times

[2can2]

Hi Mike, 2Can here. Thanks for the detailed procedure - I have often had hassles with this.

I have also found that if you aren't changing the Stored Proc name(say after you have built a custom one and then have to add another field later) then it often WON'T refresh. However if you set the option below it normally does after a close and reopen.

Another way to refresh your Stored Procedure is to look at your Crystal Report > Options (Dependency checker,Database tabs) and/or Crystal Report > Report Options under the FILE menu item. Check these settings Verify On First Refresh When this check box is selected, the report refreshes with the current version of the active database (unless it is a stored procedure) and resets its record buffers to the current record size the first time you refresh it per session. This option is selected by default. Verify Stored Procedures On First Refresh When this check box is selected, the report refreshes with the current version of the stored procedure and resets its record buffers to the current record size the first time you refresh it per session. This option is not selected by default.

Cheers

[Mike.Sheen]

Check these settings Verify On First Refresh When this check box is selected, the report refreshes with the current version of the active database (unless it is a stored procedure) and resets its record buffers to the current record size the first time you refresh it per session. This option is selected by default. Verify Stored Procedures On First Refresh When this check box is selected, the report refreshes with the current version of the stored procedure and resets its record buffers to the current record size the first time you refresh it per session. This option is not selected by default.
Cheers

Brilliant! Just tested your strategy and it recognised a new field added to the stored proc!

Thanks!

[Steve Hutchins]

Hi Mike,

Sorry for the delay in responding - your giving me far too much credit - I'm extremely rusty ....
I actually didn't get as far as you thought - I know what field I need to add to the stored procedure In_DebtorPartNumber .DebtorPartNumber but really have no idea how to actually add this to the stored procedure - (did have a go left out join but failed) - we will only need in in ship and bill type invoices (type 0).

Thanks
Steve

[Mike.Sheen]

Hi Mike,

Sorry for the delay in responding - your giving me far too much credit - I'm extremely rusty ....
I actually didn't get as far as you thought - I know what field I need to add to the stored procedure In_DebtorPartNumber .DebtorPartNumber but really have no idea how to actually add this to the stored procedure - (did have a go left out join but failed) - we will only need in in ship and bill type invoices (type 0).

Thanks
Steve

Ok, that's pretty simple - attached is a stored procedure which does this - it's called usp_MYCOMPANY_Invoices_Invoice so you should rename that to suit.

The key part is the join for IN_DebtorPartNumbers:

Code: Select all

LEFT JOIN IN_DebtorPartNumbers ON IN_DebtorPartNumbers.DebtorID = SO_Main.DebtorID AND IN_DebtorPartNumbers.InventoryID = SO_Lines.InventoryID

[Steve Hutchins]

Hi Mike,

Thank you for your response when trying to update the Crystal Report (invoice) with the new stored procedure I get the attached error ?

Thanks
Steve

[Mike.Sheen]

when trying to update the Crystal Report (invoice) with the new stored procedure I get the attached error ?

error message

I have updated the stored procedure - I did find one type which would cause errors for certain scenarios - Line 280 : "MAX(DebtrPartNo)" should be "MAX(DebtorPartNo)" - I must have tested with an invoice type that avoided that part of the stored proc.

I have attached a corrected stored procedure, but you'll need to change names to suit if you use that - so you can simply locate the typo in your version and correct it yourself.

I'm not certain this the a cause of your error, so if you still have a problem let us know and we'll continue the troubleshooting process.

Mike

[Steve Hutchins]

Hi Mike,

Loaded your new stored procedure (with our naming convention) and still get the same error .....

Thanks
Steve

[Mike.Sheen]

Hi Mike,

Loaded your new stored procedure (with our naming convention) and still get the same error .....

Thanks
Steve

I just tested this with 07.00.149.00 and 07.00.157.00 and have no problem.

Can you run the stored procedure from Management Studio and see if it produced an error ?

To do this, just run the following in Management Studio:

Code: Select all

usp_MYCOMPANY_Invoices_Invoice '6faafcb841114d788724'

In the above, you'll need to supply an SO_History.InvoiceHistoryID from your data - to find an InvoiceHistoryID, use this query:

Code: Select all

SELECT InvoiceNo, HistoryNo, InvoiceHistoryID
FROM SO_Main
JOIN SO_History ON SO_History.InvoiceID = SO_Main.InvoiceID
ORDER BY SO_Main.InvoiceNo, SO_History. HistoryNo