Does anyone know the table name that holds the information for Payables Transaction Inquiry - By Vendor? Furthermore, how would I programmatically enter a Vendor ID to into that table to narrow the results.

(0) Share

Report

Posted on by Community Member

Really new to this. Any help would be grateful.

I'm trying to automate the Payable Transaction Entry process for my firm. I've got most of the process going fine but I'm having an issue where there will be duplicate invoices if the invoice was entered before. If the invoice is manually entered then there will be validation that prevents saving the invoice but what i'm coding kinda skips over that.

So I was thinking of using Payables Transaction Inquiry by Vendor for a query to search if the invoice number is duplicate. However, I still have to search for the Vendor ID and then use a query to find the document number to see if there is a duplicate. So, any tips or code on how to do that, I'm really inexperienced. Literally just a student intern who has no experience with Databases.

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (1)

drummerboy_10 on at

Like (0)

Report
Hi Howell -

Why are you trying to automate this function? Have you thought about creating an integration to bring those transactions in from a spreadsheet?

The PM (Payables) tables you'll want to query for duplicates are:

PM00400 (PM Key Master File)

PM20000 (PM Transaction OPEN file)

PM30200 (PM Paid Transaction History file)

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

It's more of just a sunk cost. Everything is basically working but this function. Any idea how I can find a way to list only the entries for a certain vendor? Thanks.

Was this reply helpful? Yes No
drummerboy_10 on at

Like (0)

Report

Select * from <table name> where VENDORID = '<enter vendorid>'

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Okay, maybe I got a little bit ahead of myself there. How exactly would I code something to integrate VB with a table in GP.

Could I simply do

Select* from Microsoft.Dexterity.Applications.Dynamics.Tables.PmPaidTransactionHist ... and would that be enough?

Was this reply helpful? Yes No
drummerboy_10 on at

Like (0)

Report

Ok - let's back up all the way to the beginning just so I understand exactly what you're trying to do.

What is the business reason for modifying or automating the Payables Transaction Entry process? Are you trying to track additional data the current form doesn't offer? Are you trying to add additional validation to the form which currently isn't in place?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I'm trying to automate the payable transaction process because it takes far too much manual entry on my firm's part. During the process, I run into an error wherein there would be no validation against duplicate invoices by ensuring that a vendor could not have more than one of each invoice number. If I were to manually enter in 50 of the same invoices, GP would not let me but if I were to programmatically enter 50 invoices then it would work fine. I planned to enforce some validation by checking that the previous invoice number hasn't been entered. I want to be able to check Payables Transaction Inquiry by Vendor if the invoice number for a specific vendor has been entered before.

Dim CustomerMasterTableID As PmPaidTransactionHistTable

Dim CustomerMasterInvoiceVendor As PmPaidTransactionHistTable

Dim TabErr As TableError

Dim VendErr As TableError

CustomerMasterTableID = Dynamics.Tables.PmPaidTransactionHist

CustomerMasterInvoiceVendor = Dynamics.Tables.PmPaidTransactionHist

' Find the proper keys

CustomerMasterInvoiceVendor.Key = 1

CustomerMasterTableID.Key = 3

' Used Vendor and Invoice to symbolize the two variables that I was looking for; vendorname and invoicenumber.

CustomerMasterTableID.DocumentNumber.Value = invoice

CustomerMasterInvoiceVendor.VendorId.Value = VendorId

'Retriving the error

TabErr = CustomerMasterTableID.Get()

VendErr = CustomerMasterInvoiceVendor.Get()

' The Vendor will always be in database.

If TabErr = TableError.NoError And VendErr = TableError.NoError Then

MessageBox.Show(CustomerMasterTableID.DocumentNumber.Value)

Else

MsgBox("There Is an Issue")

End If

CustomerMasterInvoiceVendor.Close()

CustomerMasterTableID.Close()

Was this reply helpful? Yes No
Verified answer

JamesLyn 360 on at

Like (1)

Report

Is your firm aware of Integration Manager? It can be used to batch import invoices into the system with all of the checks and balances built in (i.e. duplicate invoices, missing vendors etc.).

I strongly recommend using IM to accomplish this, however I encourage learning so I'll help with the SDK problem.

Going back to the SDK solution, if you noticed, I set the table key to 3 in my previous answer to your other post. There are multiple "keys" per table which can be used and combine different fields as part of the query. Microsoft has explained this here: Table Keys. If you go to the Table Descriptions window in GP, you can find the detailed listing of all keys for each table. Unfortunately I do not have access to a GP installation, but look for a key that contains all the fields you wish to query on. Once you change the key, you simply have to set all the values that the key expects in the table you are performing the query on. Below is an example adding vendor id into the query assuming the key wants the document number and vendorid:

//Search for document number

Microsoft.Dexterity.Applications.Dynamics.Tables.PmPaidTransactionHist.Key = ### //add key that works for you

Microsoft.Dexterity.Applications.Dynamics.Tables.PmPaidTransactionHist.DocumentNumber.Value = docNum;

Microsoft.Dexterity.Applications.Dynamics.Tables.PmPaidTransactionHist.VendorId = vendorID;

TableError err = Microsoft.Dexterity.Applications.Dynamics.Tables.PmPaidTransactionHist.Get();

if (err == TableError.NoError)

{

//Invoice exists

}

else

{

//Invoice does not exist

}

What Sean was mentioning were the actual SQL tables that holds the information in the database and his solution was a SQL query. This can be executed from c#/vb though it is not as simple as using the built-in queries from the SDK as you have to handle the database connection, query and response yourself in your program.

~James

Was this reply helpful? Yes No
drummerboy_10 on at

Like (1)

Report

I agree with what James said about using either Integration Manager or some other integration tool (SmartConnect or Scribe) to accomplish what you're trying to do through code. Additionally, if you want batches to automatically post, then I would look at a tool like SmartPost to help accomplish this.

-Sean

Was this reply helpful? Yes No