Invoice payment fields

[Former Member]

Would you please help me locate the following fields:

Invoice number

Funding account name

Funding account identifier

Payment account

Payee identifier

Payee name

Payment amount

[K_Pauquet]

Hi Marianna,

you can quite esily find out where the data in a form is held bi activation the 'System Information' under the 'View' option in the strip menu. While this is activated, move the cursor over the field that you're interested in & in the bottom left the origin of this data will be displayed.

For example, in the incoming payment window of an existing payment, bring the mouse pointer over the 'Name' data field. The display on the bottom will read:

Name of Customer [Form170 Item=32 Pane2-3 Variable=1 OCRT, CardName]

Hence the data in that field is pulled from the 'CardName' column in the OCRT table.

All the best,

Kerstin

[Former Member]

There are no such fields in the table.

[Former Member]

I'm a little surprised... Not even these?

Invoice number

Payee identifier

Payee name

Payment amount

[Former Member]

They are in different tables depends on your need.

Have you tried my last query here:

You may try this to know more:

SELECT T0.ShortName 'Vendor',

Max(T2.CardName) 'Vendor Name',

SUM(ISNULL(T0.Debit,0) - ISNULL(T0.Credit,0)) as "Amount(LC)"

FROM dbo.JDT1 T0

INNER JOIN dbo.OJDT T1 ON T1.TransID = T0.TransID and T0.TransType = '46'

INNER JOIN dbo.OCRD T2 ON T2.CardCode = T0.ShortName

WHERE T1.RefDate >= [%0\] and T1.RefDate <= [%1\]

GROUP BY T0.ShortName

[Former Member]

I'm sorry, I thought this data is in VPM2. I am looking to extract data for PAID invoices.

[Former Member]

VPM2 has saved Invoice DocEntry and Amount Paid to Invoice only. You have to link more tables to get what you need.

[Former Member]

I see... So I'm looking to export the above date for paid invoices.

[Former Member]

Try this for some of your need:

SELECT T0.DocNum, T2.DocDate, T1.SumApplied, T0.CardCode, T0.CardName, T0.NumAtCard, T0.DocTotal FROM dbo.OPCH T0

INNER JOIN dbo.VPM2 T1 ON T1.DocEntry = T0.DocEntry

INNER JOIN dbo.OVPM T2 ON = T2.DocNum = T1.DocNum

[Former Member]

Would you please explain what info these fields display?

T1.SumApplied,

T0.CardName,

T0.NumAtCard,

T0.DocTotal

[former_member186095]

Hi,

Here is the field info :

sumapplied is the paid to invoice. it is the amount of invoice payment.

T0.CardName is vendor name

T0.NumAtCard is vendor reference number. See field reference no. under the vendor name field in the invoice.

T0.DocTotal is the total of document. if it is invoice, it is its document total in local currency.

Rgds,

JimM

[Former Member]

What is Funding account in your definition?

[Former Member]

Funding is the payer