AnsweredAssumed Answered

Working on ODBC Tables

Question asked by TKnTexas55 on Jul 30, 2018
Latest reply on Aug 1, 2018 by wimdecorte

I am trying to pull data for inventory analysis, from the general ledger account for inventory.  There are transactions from purchasing and transactions from billing, one should offset the other.  They are not offsetting.  I have mapped what I have done.  I realize that this a FileMaker board, not Microsoft Great Plains.  However, there are very few who work with the tables directly via ODBC.  I identified the files that the information...   Whatever assistance anyone can provide will be greatly appreciated.  If I can solve this, it will save TONS of work and time.  I apologize for this length.

General Ledger file.
Goal is to match transactions in the inventory GL Account to determine if something has been invoiced but not yet billed to the customer.  This is our ‘drop ship’ account.

Using linked tables I want to provide the common field to these two types of transactions. 

“Purchasing Invoice” is coming from the payables module to the general ledger.  But it only notes the Receiving Number in the ORCTRNUM field.

“Sales Transaction” is coming from the billing module to the general ledger.  It only notes the final invoice number.  There is a field of information that is attached to both, the Order Number. Order Numbers are used to convert the payable transaction (an item of inventory) to a billed transaction.

Sales Billing file:
In the Billing ‘Header’ table this record shows the invoice number as well as the order number.

I would think that if I linked SOPNUMBE from this file to ORCTRNUM field in the GL Tran file above, I should have the order number, SMOR000065472 to compare with that from the Purchasing File below.

 

Purchasing File:

Using a link of the POPRCTNM field to another file (conveniently called SOP-POP Link I have the order number (SMOR000065472)

Here is the relationship graph:

Outcomes