Show field transfer reference in A/R Invoice screen

[Former Member]

We want to see when an invoice has been paid in the A/R invoice screen. So when you search for the invoicenumber in the A/R Invoice screen it has to show the transfer reference information (table ORCT - TrsfrRef) and the date (table ORCT - TrsfrDate).

For that we are creating two UDF (e.g. Transfer Info and Tranfer Date).

Does someone know what query we will need to 'copy' the transfer reference information (with a formatted search linked to the query) into the UDF.

So when you search for the invoice number in the A/R Invoice screen the query has to find the fransfer reference information and date of that particular invoice.

Our customers always pay there invoices full (so not in terms).

Thanks in advance!

Best regards,

Tim

[Former Member · Accepted solution]

Hi

Assign this query to the UDF to get  Transfer Info  
SELECT T0.[TrsfrRef] FROM ORCT T0  INNER JOIN RCT2 T1 ON T0.DocNum = T1.DocNum WHERE T1.[DocEntry] =$[OINV.DOCNUM]


Assign this query to the UDF to get  Tranfer Date
SELECT T0.[TrsfrDate]  FROM ORCT T0  INNER JOIN RCT2 T1 ON T0.DocNum = T1.DocNum WHERE T1.[DocEntry] =$[OINV.DOCNUM]

[Former Member · Accepted solution]

Thanks for the queries.

Somehow I don't get the formatted search right.

In the UDF Transfer Info:

I selected ALTSHIFTF2.

I selected the option: Search in Existing User-Defined Values According to Saved Query

I selected the query for Transfer Info

I selected the box: Auto Refresh When Field Changes

I selected: Document Number

I selected the option: Display Saved User-Defined Values

I have also tried other options, but I didn't become the Transfer Info information in the UDF.

Any idea what the problem is?

Best Regards,

Tim

[vijay_kumar1 · Accepted solution]

Hi Tim,

Is the invoice which you are trying to get the values paid through Bank Transfer ?

Also, if you press shift+f2 in the udf, does it display any value?

Regards,

Vijay kumar

[Former Member · Accepted solution]

When pressing SHIFT+F2 there is no information (something which I already tried).

The payments are from a Bank Transfer (Banking - Incoming Payments - select BP and invoice(s) - Payment Means).

Best Regards,

Tim

[vijay_kumar1 · Accepted solution]

Hi Tim,

Could you try this query as a formatted search

SELECT T0.[TrsfrRef] FROM ORCT T0 INNER JOIN RCT2 T1 ON T0.DocEntry = T1.DocNum WHERE T1.[DocEntry] = $[OINV.DocNUM]

Regards,

Vijay kumar

[former_member204969 · Accepted solution]

Try this query:

SELECT T0.TrsfrDate
 FROM ORCT T0  INNER JOIN RCT2 T1 ON T0.DocEntry = T1.DocNum
 Where T1.DocEntry = $[OINV.DocEntry]

and select the option Refresh Reguralry

[Former Member · Accepted solution]

Thank you for the answer.

Somehow is doesn't seem to work. I've tried several options but I don't get the information in the UDF.

Before you select the option Refresh Regularly you have to select Auto Refresh When Field Changes and then choose a field. In our case we have selected the field Document Number. Is this correct?

-

---

I have solved it and the query works!

Thanks for the solution!

Best Regards,

Tim

Edited by: T. Verholt on Sep 12, 2008 4:56 PM

[K_Pauquet]

Hi Tim,

in version 2007 you only need to rightclick on the header of the invoice, then select 'Reconciled Transactions' & you will be presented with a complete list of all payments, credit notes, journals etc that are or have been reconciled with this document.

From that list you can use the orange link arrows to drill back to those documents.

All the best,

kerstin

[Former Member]

Thanks for this solution.

The advantage of the UDF is that you have the information directly on screen. With the option you mentioned you still have to click several times before you have the information you really need.

Best Regards,

Tim

[Former Member]

Hi Tim

There are 3 ways to get this information as follows:

1. Click on money bag (payment means) on menu bar or right click on document and select Payment means or press CTRL Y. This will display the payment(s) against this invoice.

2. Add UDF and use the OINV.ReceiptNum if only 1 payment per invoice.

3. Add UDF and link OINV to RCT2 if multiple invoices per payment or multiple payments per invoice.

Kind regards

Peter Juby