Commonly Used Tables:
RM00101 – Customer Master
RM00102 – Customer Addresses
RM00103 – Customer Master Summary
RM00104 – Customer Period Summary
RM00106 – Statement Email Addresses
RM00201 – Customer Class Master
RM00301 – Salesperson Master
RM00303 – Sales Territory Master
RM00401 – RM Key File
RM10101 – Distribution Work and Open
RM10201 – Work/Unposted Cash Receipts
RM10301 – Work/Unposted Sales Transactions
RM10501 – Commissions Work
RM20101 – Open Transactions
RM20201 – Open Transactions Apply
RM30101 – Historical Transactions
RM30201 – Historical Transactions Apply
RM30301 – Distribution History
RM30501 – Commissions History
MC020102 – Multicurrency Receivables Transactions
RMDTYPAL – RM Document Types:
0 – Reserved for balance carried forward records
1 – Sale / Invoice
2 – Reserved for scheduled payments
3 – Debit Memo
4 – Finance Charge
5 – Service / Repair
6 – Warranty
7 – Credit Memo
8 – Return
9 – Payment
VOIDSTTS – Void Status in RM20101 and RM30101:
0 – Not voided
1 – Voided
2 – NSF check
3 – Waived finance charge
CSHRCTYP – Cash Receipt Type in RM20101 and RM30101:
0 – Check
1 – Cash
2 – Credit Card
BACHFREQ – Batch Frequency:
1 – Single Use
2 – Weekly
3 – Biweekly
4 – Semimonthly
5 – Monthly
6 – Bimonthly
7 – Quarterly
8 – Miscellaneous
HISTTYPE – History Type in RM00104:
0 – Calendar
1 – Fiscal
DCSTATUS – Document Status in RM00401:
0 – Reserved
1 – Work
2 – Open
3 – History
DISTTYPE – Distribution Type:
1 – Cash (CASH)
2 – Terms Taken (TAKEN)
3 – Accounts Receivable (RECV)
4 – Writeoffs (WRITE)
5 – Terms Available (AVAIL)
6 – GST (GST)
7 – PPS (WH)
8 – Other (OTHER)
9 – Sales (SALES)
10 – Trade (TRADE)
11 – Frieght (FREIGHT)
12 – Miscellaneous (MISC)
13 – Taxes (TAXES)
14 – COGS (COGS)
15 – Inventory (INV)
16 – Finance Charges (FNCHG)
17 – Returns (RETURNS)
18 – Debit Memo (DRMEMO)
19 – Credit Memo (CRMEMO)
20 – Service (SERVICE)
21 – Warranty Expense (WARREXP)
22 – Warranty Sales (WARRSLS)
23 – Commissions Expense (COMMEXP)
24 – Commissions Payable (COMMPAY)
25 – Unit Account (UNIT)
26 – Rounding (ROUND)
27 – Realized Gain (RZGAIN)
28 – Realized Loss (RZLOSS)
29 – Unrealized Gain (URZGAIN)
30 – Unrealized Loss (URZLOSS)
BCHSOURC – Batch Source:
RM_SALES – RM Transaction Entry, posted in batch
RM_CASH – RM Cash Receipts, posted in batch
XRM_SALES – RM Transaction Entry, posted w/o batch
XRM_CASH – RM Cash Receipts, posted w/o batch
Sales Entry – from SOP posting
Blank – could be created by auto-posted transactions, like Debit Memos created by NFR entry
MXWOFTYP – Maximum Writeoff Type:
0 – Not Allowed
1 – Unlimited
2 – Amount
[Note: If MXWOFTYP = 2 then MXWROFAM contains the amount of the writeoff allowed, otherwise MXWROFAM is zero]
CRLMTTYP – Credit Limit Type:
0 – No Credit
1 – Unlimited
2 – Amount
[Thanks to Curtman for these]
Email_Type in RM00106:
1 – To
2 – Cc
3 – Bcc
STMTCYCL – Statement Cycle:
1 – No Statement
2 – Weekly
3 – Biweekly
4 – Semi-Monthly
5 – Monthly
6 – Bi-Monthly
7 – Quarterly
[Thanks to Janeece Moreland for these]
—
Last Updated: Sep 12, 2018
Hi Victoria,
I am trying to build a tax report and I am currently getting the correct Tax IDs and distributions from SOP30200 and SOP10105 for the Sales Transactions but I haven’t been able to find the relevant information for the RM tables to capture Debit and Credit Memos in particular, would you be able to guide me to a table for these?
LikeLike
Hi Steven,
Have you looked at the TX30000 table? I typically start with that for tax reporting.
-Victoria
LikeLike
Hi Victoria.
i’m using GP10.
When I was applying sales document. I’ve got message “Unhandled script exception. Index 0 of local array is out of range in script ‘CommitApply’. Script terminated.”
I’ve already run Checklink for Sales, but it’s not fixing the problem. Do you have any idea to fix this ??
LikeLike
Hi Mulia,
Typically a fix for this would require examining your data in SQL and fixing it there. I would recommend working with your GP partner on this.
-Victoria
LikeLike
Hi Victoria:
Computer was disconnected to the server while I was applying a received payment to the invoice.
Here’s the situations I am in right now:
1. Invoice still shows “open”, but can’t see the invoice when trying to re-apply the payment.
2. Payment transaction doesn’t show under the customer, so it won’t show in bank deposit.
3. Running GP 10.0, doesn’t have access to download Professional Services Tools Library.
Thanks,
LikeLike
Hi Jan,
PSTL won’t help with this. Here is what you can try:
Get all users out of GP.
Run Check Links on the Sales Series.
If it comes back with errors, run it again. This should remove orphaned records created by disconnecting from the server while entering a transaction.
Go to Microsoft Dynamics GP | Tools | Utilities | Sales | Reconcile. Choose Outstanding Document Amounts under Reconcile, select the customer in the From and the To in the middle, click Process.
If that does not work, the fix will involve updating things directly in SQL – this needs someone to look at the specific data in this situation, so there is no generic script that will always work.
-Victoria
LikeLike
Hi Victoria:
Thanks a lot. You are so right. Ran “Check Links” removed orphaned records, so I was able to apply invoice to the invoice again.
By the way, the Log Report shows there’s another transaction like this. It shows under “RM History file”.
Record shows “The RM keys record for this transaction is missing. The keys record has been created”.
Any suggestions to fix it? The record shows on AR aging report, not on AR report from Smartlist.
LikeLike
Hi Jan,
Glad that worked. For your other question, once it’s in history, it’s a lot harder to fix and likely requires database manipulation if even possible. Depending on how old it is and if it is valid but missing data, there may be different options to address it. If it’s important to fix, I would recommend talking to your GP partner or to Microsoft support to get help on it.
-Victoria
LikeLike
Hi Victoria, love your site, very helpful. One quick question, I want to be able to change the HOLD status in customer account via SQL based on some conditions, for example UPDATE RM00101 SET HOLD = 1 WHERE XXXXXX.
My question is, is that OK to do or if you do it via GP (I have 2010 version still) via Sales -> Cards -> Customers if changing the HOLD status there also changes the value in other columns/tables?
Thanks for any insight you may provide.
LikeLike
Hi CelticsDresden,
Normally this is pretty safe. However, without knowing details of your GP environment and what other products and customizations you have it’s impossible to say if this would cause any problems to anything else in GP.
-Victoria
LikeLike
Thanks Victoria,
That’s quite helpful to know. Yeah, I doubt anything in my environment will be affected, so will just start experimenting in my test environment to see how it goes. Just didn’t want to overlook something within GP itself, like if it affected other table values as well. So seems fairly straightforward.
LikeLike
Hi Victoria,
Some how during my year end close I eliminated the Sales last year numbers from GP. They did not roll over when I closed 2019. Is there a way to get them back in the system?
Thanks,
Kevin
LikeLike
Hi Kevin,
I can see this happening if you inadvertently close the year twice. Microsoft Support might have scripts that can recalculate these, but I’ve not used them and do not have any of my own for this. I typically don’t bother closing the year in the RM module and instead create custom reports and SmartLists to see the correct current year, last year, etc. data.
-Victoria
LikeLike
What is the easiest and cleanest way to add an account (RM00101) record from a 3rd party database. I have SQL access.
LikeLike
Hi Brian,
For importing data into Dynamics GP, I would recommend one of the tools made for this – either Integration Manager, eConnect, or SmartConnect. I typically do not recommend inserting data directly into SQL.
-Victoria
LikeLike
Hi Victoria,
First of all, many thanks for all that you do. This is my first time writing you, but I have been using resources from your blog for years as a GP Admin.
I have a quick question, if you had some mass updates to do to the Apply Date (DATE1) and Apply to GL Date (GLPOSTDT) in the RM20201 table, would you consider doing it by SQL? Or should I tell my users to get to work doing it all manually in the Apply Sales Document screen?
700+ documents btw…
Regards,
Brian
LikeLike
Hi Victoria, I echo the words of thanks that many have shown you. I am looking to find the invoice line item details (e.g. item description, unit price, etc.). We do not use SOP or Inventory module. Invoice details are imported using a custom tool. I have reviewed the tables listed in some of your queries as a starting point. No luck so far.
LikeLike
Hi Anderson,
Do you know where/how are you importing your invoices to?
-Victoria
LikeLike
I think we are importing the records into GL? The process goes something like this:
1. Export flat invoice file from source system.
2. Use a custom application to import into a Receivables batch Dynamics GP.
3. Batch is posted to GL.
When I generate an invoice out of Dynamics GP, it only contains a summary row of data, which is the subtotal.
Thanks for you assistance.
LikeLike
Hi Anderson,
If you’re able to generate an invoice from GP, you are at the very least importing it into the Receivables module. However, if you’re not importing line item detail, then how can you show it on an invoice or report on it? It sounds like you may want to have an in-depth discussion about this with your Dynamics GP partner to better understand what you are currently doing and to go over what you are looking to do to have them help you come up with the best solution.
-Victoria
LikeLike
Thanks Victoria, for the quick response. That was my line of thinking after reviewing the various posts on this page and seeing an answer provided to another poster. I will follow up with my Dynamics GP partner.
LikeLike
Victoria, do you know what date the DATE1 field in table RM20201 represents? How is it getting updated? Thanks.
LikeLike
Hi Jack,
DATE1 in RM20201 (and also RM30201) is the Apply Date on the Apply Sales Documents window. Once a transaction is applied, the only way to update that field in the GP user interface is to unapply and reapply the transaction with a different date. Note that this is the ‘subledger’ date; there is a separate field (GLPOSTDT) for the Apply Posting Date, which is the GL date.
Hope that helps.
-Victoria
LikeLike
Yes, thanks Victoria. That information is very helpful.
LikeLike
I’ve just been trying to establish if it is possible to specify a VAT number on a per transaction basis, rather than just using the VAT number of the customer, for example, in the case of B2C transactions where the debtor ID used is effectively a particular credit card payment processor, but customers may use that and specify a VAT number for the particular transaction. I can’t see any fields at the receivables transaction header level that look like they would accommodate that.
Thanks
LikeLike
Hi Tony,
You could record this in the user defined fields if this is on SOP transactions…or if you are already using all of the user defined fields or this is not SOP, consider Extender.
-Victoria
LikeLike
Thanks Victoria. We’re not using SOP, and it does give me some confidence I’m not missing anything obvious.
LikeLike
I’m trying to add receivables distributions to represent a promised charitable contribution on a sale, and I thought we do it by balancing debit and credit lines to nominals set up for the purpose. My question is what is the impact of the distribution type we choose? I’m tempted to use “OTHER” (8) since nothing else seems to fit, but I’m not too sure what this affects exactly.
LikeLike
Hi Tony,
OTHER is probably the best choice. Many of the other TYPES have to match up to something on the transaction, which this will not, so you don’t want to use them.
-Victoria
LikeLike
there are so many comments it is amazing. I wanted to see if you could add this to your page but did not know if it was already there.
case stmtcycl
when 1 then ‘No Statement’
when 2 then ‘Weekly’
when 3 then ‘Biweekly’
when 4 then ‘Semi-Monthly’
when 5 then ‘Monthly’
when 6 then ‘Bi-Monthly’
when 7 then ‘Quarterly’
else ”
end as StatementCycle from rm00101
LikeLike
Janeece,
Thank you very much! I have added these to the list, I am sure they will come in handy,
-Victoria
LikeLike
Hi Victoria,
I’m trying to create a table of Customer Numbers and the GL Accounts their sales are setup to post to (as shown in Cards > Sales > Customer > Accounts > Sales). Would joining RM00101.RMSLSACC on GL00105.ACTINDX produce this list?
Also, in RM00101 what to the below columns refer to?
RMCSHACC
RMARACC
RMSLSACC
RMIVACC
RMCOSACC
RMTAKACC
RMAVACC
RMFCGACC
RMWRACC
RMSORACC
Thank you!
LikeLike
Hi SpacecommanderJ,
Here is the full list of the accounts:
RMCSHACC RM Cash Account Index
RMARACC RM AR Account Index
RMSLSACC RM Sales Account Index
RMIVACC RM IV Account Index
RMCOSACC RM Cost of Sales Account Index
RMTAKACC RM Discounts Taken Account Index
RMAVACC RM Discounts Avail Account Index
RMFCGACC RM Finance Charge Account Index
RMWRACC RM Writeoff Account Index
RMSORACC RM Sales Order Returns Account Index
Yes, you can join each of those fields to the ACTINDX in GL00105 to get the account number (in the ACTNUMST column).
-Victoria
LikeLike
Hi Victoria, I changed the setup of AP aging from Document Date to Due Date. The change reflected right away on Inquiry payables summary. The problem is when I ran a smartlist Aged Payables, this changed to due date has not reflected yet. And since Payables doesn’t have an aging function like A/R, how can I make this change reflect on smartlist? Is there some SQL command I can use? Also the AP aging buckets are not updated in smartlist. Your help is very much appreciated. Thank you.
LikeLike
Joe,
Where do you see AP Aging buckets in a SmartList? I am wondering if that is a custom SmartList that you need to change the logic for?
-Victoria
LikeLike
Hi Victoria, I am new to your site and think it’s great! Has anyone ever asked you why RM00104 summary table doesn’t break out sales tax, and if there’s a another table that can be used with RM00104 to get to the actual/net sales amounts?
LikeLike
Hi Cindy,
The RM00104 table is just a brief summary. I believe that depending on your settings on the Receivables Setup Options the sales totals may or may not include tax, and I think that a lot of people are not aware of that. But it’s a good reason not to use summary tables, as they may not show what you think.
In any case, you can always get the tax details and sum them up by using the transaction tables – RM20101 and RM30101.
-Victoria
LikeLike
Hi Victoria, thank you so much for being such a valuable resource. I am new to GP and I am tasked with identifying credit memos and the invoice that is was applied to. In the apply screen I can see both invoice and credit memo numbers. Can you please help me to locate what table can I locate this data in? Thank you very much.
LikeLike
Hi Thomas,
Take a look at my view for AR Apply Information: https://victoriayudin.com/2010/02/15/sql-view-with-ar-apply-detail/. I think that will help with your question.
-Victoria
LikeLike
You are terrific! Thank you!
LikeLike
Hi Victoria
What are the various PSTGSTUS types in the RM10101 tables (GP V 9)
Thanks
Colin
LikeLike
Hi Colin,
I believe 0 is unposted and 1 is posted.
-Victoria
LikeLike
Hi Victoria,
I have a question about Late Fees. What’s the difference between using “Waive” or “Void” on getting rid of a late fee? Is one better than the other?
Your response will be greatly appreciated.
Thank you.
Joe
LikeLike
Hi Jie,
My understanding is that the only difference is that a waive gets a different ‘void status code’ than a void does, thus allowing you to see/report on what was voided vs. waived in case that is important when you are evaluating a customer’s account. Otherwise, the process is pretty much the same as to what GP does with it. My 2 cents – it’s more important to be consistent than which one you use. If you use void for some customers and waive for others, then you really cannot use your data for any meaningful reporting/statistics in the future.
Hope that helps,
-Victoria
LikeLike
Victoria,
Sorry for the late response.
Where do I see the report on what was voided vs. waived? I tried finding but couldn’t find any even on smartlist. Do I need to be in SQL to get this report?
Many thanks.
Joe
LikeLike
Hi Joe,
I don’t believe there is a report or SmartList in GP that will show this, maybe an individual transaction will. You can see this in SQL in the VOIDSTTS column of the RM20101 and RM30101 tables:
0 = Not voided
1 = Voided
2 = NSF check
3 = Waived finance charge
-Victoria
LikeLike
Victoria,
Have a Payment Posting issue. Getting error message “Error From Dynamics GP: Document number already exists in either RM00401, RM10101, RM10201, RM20101 or RM30101”. Verified document numbers do exist in RM00401, RM10101, and RM20101. I expect them to exist in these tables, so what am I missing?
LikeLike
Hi Steven,
Is this a cash receipt? If it is not posted yet, it should only be in RM10201, not in RM20101.
Also, for what it’s worth, I have never seen an error message like this, which makes me think it might not be an out-of-the-box GP error message, but instead something from a customization or 3rd party product.
-Victoria
LikeLike
Victoria,
Yes it is a cash receipt insert. This only occurs when multiple payments are entered for the same customer using the same credit card for different InvoiceNumbers. Previously in some custom code where CreditCard was a series of digits (XXXX.YYYYMMDD.00X) I have modified this number for the set of payments that won’t process. In this case these are actually Credit Cards, AMEX, VISA, etc. The Invoice exists in the RM20101 table but not the PAYMENT. INV vs PYMNT for DOCNUMBR.
LikeLike
Hi Steven,
Sounds like this may have to do with whatever product you’re using for credit card processing. As I mentioned, the error message does not sounds like a GP message I have seen before. What product are you using? Have you talked to them?
-Victoria
LikeLike
Hi Victoria, I am mapping my data to import into QuickBooks from expired Small Business Financials 8.0. Do you know where the Customer Master Email field is hiding??
LikeLike
Hi Larry,
The email is specific to a customer address, not just a customer. It is stored in the SY01200 table.
-Victoria
LikeLike
Victoria, thank you for your site. Its a great reference. We’re decomming GP 9 app and I’ve been asked if I can collect receivables collection activity notes on our customers in the system, I can’t seem to locate a Table related to Collection activity notes are stored.
LikeLike
Hi David,
I am not sure if it would be the same in the older version, but I believe currently notes are in the CN00300 table. And they are linked to the RM documents by the data in CN20100.
Hope that helps.
-Victoria
LikeLike
Hi Victoria,
We are adding new customer however we would like to copy all address from another customer there are over 1500 addresses. Entering them manually would take a while. Would the following do the trick?
insert into [RM00102]
([CUSTNMBR], [ADRSCODE], [SLPRSNID], [UPSZONE], [SHIPMTHD], [TAXSCHID], [CNTCPRSN], [ADDRESS1], [ADDRESS2], [ADDRESS3], [COUNTRY], [CITY], [STATE], [ZIP], [PHONE1], [PHONE2], [PHONE3], [FAX], [MODIFDT], [CREATDDT], ,GPSFOINTEGRATIONID], [INTEGRATIONSOURCE], [INTEGRATIONID], [CCode], [DECLID], [LOCNCODE], [SALSTERR], [USERDEF1], [USERDEF2] )
(
SELECT ‘NewCustNmbr’, [ADRSCODE], [SLPRSNID], [UPSZONE], [SHIPMTHD], [TAXSCHID], [CNTCPRSN], [ADDRESS1], [ADDRESS2], [ADDRESS3], [COUNTRY], [CITY], [STATE], [ZIP], [PHONE1], [PHONE2], [PHONE3], [FAX], [MODIFDT], [CREATDDT], [GPSFOINTEGRATIONID], [INTEGRATIONSOURCE], [INTEGRATIONID], [CCode], [DECLID], [LOCNCODE], [SALSTERR], [USERDEF1], [USERDEF2]
FROM [RM00102] where custnmbr=’FromCustNmbr’
)
Please advise
Kind regards,
-Alex
LikeLike
Hi Alex,
Yes, I don’t see why this would not work.
-Victoria
LikeLike
Hi Victoria! Thank you very much for the resources you share! I have a question, I am using Smart connect to apply payments and was wondering there is any harm to updating the RM20201.TRXSORCE column with an qualifier to identifier that the transaction was applied by Smart Connect? I want to make sure that there won’t be any issue running Checklinks later.
LikeLike
Hi Eve,
The TRXSORCE should not be changed. I would leave that how it is or you risk breaking links to other data in GP. That could easily cause significant issues for both check links and reconcile processes. If you need to track something else, I would do it in a separate, custom table, since I don’t really see a column in RM20201 that is not being used. Just my 2 cents.
-Victoria
LikeLike
Victoria,
Thank you for all the information you make available. I often check here first when I have questions. I am not an official techie but we are a small company so I fill in some here.
We do have a GP provider who is going contacting MS for us but I thought I’d place the question here to see what you might be able to shed light on. We’ve recently been trying to deal with inaccurate payment application/balances on our AR Customer accounts. As part of this, we attempted to run a delete/recreate of the RM00401 table. Now, when we run the Sales /Receivable Transaction history files for Check links, it gives an error “cannot insert the value NULL into column ‘NEGQTYSOPINV’ table RM00401; column does not allow nulls. ” The RM00401 table now only has the open transactions in it and will not rebuild the history. (Which is causing all sorts of havoc within our system) I see that the column NEGQTYSOPINV only exists one other place (IV10200) and there are no NULLS in this table/column. In fact, the only valid entry in this field appears to be 0 . Our GP provider assumes that there is corrupt data within one or more of the history tables but cannot trace which one. Can you tell us where this NEGQTYSOPINV column is pulling (or compiling) from or how to locate the corrupted data? Any input would be great!
LikeLike
Hi Holly,
I have not run into this particular problem before, so I am not able to shed any light on this. I agree that it sounds like there is bad data somewhere, but I could not tell you where without a thorough examination of your data. Your GP Partner is doing the right thing – I would recommend talking to MS Support about this as a next step.
-Victoria
LikeLike
Victoria,
I thought I would update you on how this issue was resolved. As per our MS support contact, we used their PSTL toolkit to Recreate the SQL Object/Recreate selected table and data for the RM00401 table and then reran the checklinks process on the sales work and history tables. All worked perfectly and we are now running normally.
LikeLike
Hi Holly,
Thank you so much for the followup – I am very glad you got it resolved!
-Victoria
LikeLike
I recently started a batch for cash receipts. In the middle of the batch I had to attend a meeting and when returning I had been kicked out of GP. I re-entered GP and attempted to pick up where I left off. I left off with PYMT8863. When I returned to the cash receipts screen it picked up at PYMT8864. When I try to return to PYMT8863 it give the error “You cannot display this record while another user is editing it”. However, all users were kicked out at midnight last night so it’s impossible for any other users to be editing it. When I search invoices that were applied under PYMT8863 they show “Open” and when clicking amount remaining “PYMT8863” comes up but this receipt can not be found or edited. I’m not sure what to do because I can’t delete PYMT8863 because I can’t find it and I can’t start a new cash receipt because these invoices don’t show up to be applied because they’ve already been cleared by PYMT8863.
LikeLike
Hi John,
This is a pretty common thing and good lesson to not leave GP running with transaction windows open when you leave for the day. 😦
Unfortunately, the typical fix for this either involves running Check Links and Reconcile (with all users out of GP) and/or fixing data directly in the database by someone knowledgeable. If you have someone on staff to do this, please ask them. If not, you will want to ask your GP partner or Microsoft support for help.
-Victoria
LikeLike
Victoria,
I have a batch that partially posted, was deleted, recreated, and posted again. Yep. The batch only posted partially the second time, 36 of 90 transactions. The error on the report is A control ID is missing or invalid. I see records in the RM10201 table that are reflected on Customer Cards as work, these records are missing from the RM00401 table also. The same batched was used both times. The payments remaining in the batch show as applied and amount remaining is 0.00. What am I missing here?
LikeLike
Hi Steven,
Having the same batch ID is no problem, we re-use the same batch IDs all the time. For example, we know if something is in a batch called SALES we can post it, but no other batches in SOP should be posted. That’s not going to cause any issues by itself. However, having transactions that are not in the RM00401 table is a problem. I would try running check links to see if it fixes those.
-Victoria
LikeLike
Do you happen to know what the status field in the RM20400 and RM20401 tables indicate? These are for Scheduled Payments. We have a 1 and a 3 in these tables.
LikeLike
Sorry Lisa, I am not seeing this information anywhere. 😦 You might need to ask Microsoft. If you find out, please let me know and I will add the info to this page for future reference.
-Victoria
LikeLike
I am trying to find out the same exact thing.
LikeLike
similarly, I am seeing the 1 and 3 in the status field of the PM20400 table. 1 appears to mean unposted and 3 means posted, but I can’t find any legitimate documentation on this.
LikeLike
Hi Victoria,
I’m using field RM00103.TTLSLYTD to get a customers sales total year to date. For some reason that amount does not match actual sales history to date – any idea why there would be a difference? What triggers this field to update? I would assume this field includes all posted transactions for the current year.
Thanks,
Kevin
LikeLike
Hi Kevin,
That field updates every time a sale is posted to the customer. Unfortunately, this does not use any sort of date logic. It simply resets when you run the year-end close for the sales module. So here is a practical example of what happens with the YTD summary fields:
– You start using GP with no prior data on 1/1/2015. The YTD fields are fine all through 2015.
– In the beginning of January 2016, you’re still entering and posting 2015 sales transactions into GP.
– In January of 2016 you’re also starting to enter and post 2016 sales transactions into GP.
– On January 12th of 2016, you run the sales module year-end close routine. This moves EVERYTHING posted so far to the Last Year totals and resets the YTD totals to 0. So whatever 2016 transactions you have posted up to that point are now included in the last year totals in RM00103 and only transactions you’re going to post after the year-end closed will show up in the YTD numbers.
This also works the same way for payables transactions. 😦
Most companies I have worked with cannot possibly have a clean cut off where they are done posting all the 2015 sales and payables transactions before they start posting 2016 transactions. Once in a while I run across a company that can do this, but it’s very rare. All this to say, I personally find the YTD numbers in GP useless and create my own reports for this. You can see some examples of these under the GP Reports section of this blog.
Hope this helps,
-Victoria
LikeLike
Thanks Victoria – I was actually using that field for an internal website we could use for quick customer info. Think I’ll just create a calculated field to get the real YTD number.
Always appreciate the help!
LikeLike
Couldn’t they just set the date in GP or on the computer to close on December 31st ?
LikeLike
Hi Larry,
Not sure what you mean. Are you saying change the date, then do the sales module close? If so, that will not work. The subledger (sales or payables) close does not look at that or any other dates. It just takes everything posted at that point in time.
-Victoria
LikeLike
Hi victoria, it is possible to print a report from window CASH RECEIPTS INQUIRY ZOOM ? I need to re-print the document but the window don’t have the print button.
LikeLike
Amilcar,
Since there is no print button, as you point out, the only things you can do are: (a) print the report from somewhere else, (b) create a customization, (c) buy our GP Reports Viewer add-in for GP – this will give you the ability to add a custom SSRS, Crystal or Excel report to any window in Dynamics GP.
-Victoria
LikeLike
Thanks
LikeLike
Victoria
I need to count the number of addresses a customer has.
Can you please help me with that.
thanks,
ricky
LikeLike
Hi Ricky,
You should be able to use the following to do this:
selectCUSTNMBR Customer_ID,
count(*) Num_of_addresses
from RM00102
group by CUSTNMBR
order by CUSTNMBR
-Victoria
LikeLike
Hi Victoria,
I have an odd situation. We have a customer with approximately 20 unpaid invoices. They are all from within the last 2 months. However, I cannot apply a payment to any of these invoices or apply a credit memo, nor can I void any of them.
I have run Checklinks on all the RM tables, run reconcile, etc and nothing fixes it. They all appear on the AR aging but I just can’t void or apply anything to them. Any good hints on where to start with trouble shooting? Should I rebuild the RM00401?
Any thoughts would be hugely appreciated!
LikeLike
Heidi,
Are you getting as error? Are they simply not in the list? Do these show in the RM00401 table? If so, with what status?
-Victoria
LikeLike
That’s the weird thing…no errors, just simply not in the list when trying to apply a credit memo/payment or when trying to void the document. They all do show in the RM00401, I couldn’t find duplicates in Work/Open/Hist. I can view the documents by drilling into them in the inquiry by Customer window without issue, etc. EVERYTHING seems normal about them except they aren’t available to apply or void.
Thanks in advance for any hints you may have.
-Heidi
LikeLike
Heidi,
What is the DCSTATUS for these documents in RM00401?
-Victoria
LikeLike
Hi Victoria,
All 20 of them have a DCSTATUS of 2.
LikeLike
Heidi,
Ok, that means they are in the RM20101 table. What is the VOIDSTTS in that table for them?
-Victoria
LikeLike
Thanks Victoria. All 0’s. Void date is 1/1/1900, they all have a correct Currency ID, date invoice paid off is 1/1/1900, their ORTRXAMT and CURTRXAM are all correct (the original amount of the invoice). Everything looks correct….
The odd thing is, this customer was a customer a long time ago, they broke away and then came back. All of their ‘older’ invoices are still there, in history, etc, having been paid off long ago. It’s just all these new invoices since they came back in July that are troublesome.
LikeLike
Heidi,
I am not sure what you mean by “broke away”. Do you have modifications or customizations involved? If not, this may be something you need to take up with Dynamics GP Support.
-Victoria
LikeLike
“Broke away” meaning they left us and then came back as a customer. 🙂
No customizations or modifications or 3rd parties even.
I was thinking a case too, but wanted to ask the expert first! 🙂
Thanks for your quick replies, they are much appreciated.
Heidi
LikeLike
Hello Victoria – I am trying to join my GL tables (GL20000/GL30000 and GL00100) with RM10101 to get detail by year of all sales and financial transactions hitting various revenue accounts for the time period and am having little success. My query is pulling duplicated lines of the sales transactions so the value of my data output is significantly higher than the actual GL balances per GP. I have attempted to use “DISTINCT” functions to limit this duplication however nothing seems to work. Any suggestions?
LikeLike
Ashley,
What columns of data do you want to see on your report? Why the need to link to RM10101? You can get the customer and invoice information directly from GL tables unless you are not posting to the GL in detail.
-Victoria
LikeLike
I am tasked with a data extraction that finds all invoices that have been short paid. I have accomplished that part without issue. The next part of the task is to find the check number for the payment that we received for the short pay. Can you enlighten me as to how to accomplish this?
Thanks
LikeLike
Victoria how are you? I have a question for you. My RM10101 has some inconsistant data due to the fact that it has been getting loaded from 3 seperate ERPs and some indivuals. I am about to update a couple of hundered records with new short names so I can have one field that will correctly relate all 3 ERPs. Please give your cautions or advice.
LikeLike
Hi Jerome,
RM10101 does not have a short name, did you mean RM00101? 🙂 Also, it would be helpful to have a complete picture before giving any advice. Can you please explain what the issue is in a little more detail?
-Victoria
LikeLike
Sorry Victoria I am just now seeing your reply 😦
Yes I meant RM00101. I have 4 different sources for the data in my RM 101 table . The custnum field has some numeric from ERP1 and ERP3. My SRT names are from ERP1 and ERP2.
So now I am building an Customer Export from ERP3. I need a consistent field to relate all the customers in ERP3. So I want to make the Shrtname field equal to the custnum in ERP3.
So I am considering doing an update to about 300 customer records.
LikeLike
Jerome,
Thanks for the additional information. I don’t see any reason you would have a problem with this. GP does not use the customer short name for anything other than informational purposes, so you can update it in SQL and use it to link to your other data.
-Victoria
LikeLike
Victoria,
We provide 2 services to most of our customers.
Is there a way to run an aged historical balance to only show the outstanding balances of only one type service with minimal customization or will the aged trial balance grab all the invoices for a customer?
My thought is to denote the type of service provided when generating the invoice then use the aged TB to call only those invoices meeting that criteria. Also is this possible with customer statements?
Alternatively, is it just better to have two customer IDs?
LikeLike
Hi Mark,
I cannot think of an easy way to do this with the out-of-the-box GP reports. Either for the HATB or statements. You can certainly accomplish this with custom reports, I have done this for customers in the past. Please let me know if this is something you’re interested in getting help with – my company provides custom report design as a consulting service.
-Victoria
LikeLike
Hi Victoria,
We accidentally ran the finance charge process for many of our customers Is there any way to mass void the finance charges based on a date? For example, we know it happened today and they are all finance charges. We have the list of customers and amounts in excel.
LikeLike
Jelana,
Sorry I am not aware of any way to mass void these. 😦 You could try a macro, but often those are even worse than doing it manually.
-Victoria
LikeLike
Victoria,
Good morning! I have an issue where incoming AR payments are not being applied across transactions. We are receiving full payment for an original sales order and a $50 late fee. The payment is applying to the original document but is not being applied to the late fee. I expected GP to apply overpayment in receipt order. What am I missing here?
LikeLike
Hi Steven,
Are you importing these or entering these manually? Either way, you can specify what documents the payment gets applied to. If you’re using Auto-Apply, there is only Document Number order or Due Date order, so that may not work for you.
-Victoria
LikeLike
Hello,
We are sending out statements matching to see if the historical aged trial balance match as of 12/31/2014. I have found that properties with a credit memo/debit memo that was unapplied or applied after 12/31/2015 are not showing the correct balance on the statement but showing correctly on the Trial Balance.
Is there any way to fix this
LikeLike
Jan,
IMHO there are some inherent flaws in the out-of-the-box statements. I typically recommend creating custom statements if they’re not doing what you need.
-Victoria
LikeLike
I have a situation where I use the PTSL to unapply RM customer payments, but when I tried to reapply them, the customer now has a credit balance. The Receivables Transaction inquiry Screen shows invoices that are “applied”. When I drill down to see what payment was applied to invoices, some show no payments., but the amount remaining is zero. I feel like AR is incorrect as a result because it shows a credit balance and invoices that have a zero remaining balance. How can I fix this? I’ve tried several times to unapply/reapply, but the issue is the same.
LikeLike
Hi Jelana,
I would start out by running the sales reconciliation utility on outstanding document amounts and also current customer information. You should have all users out of GP and make a backup of your data prior to doing this. If that does not help, you will likely need to get your GP Partner or Microsoft support involved to help you fix this directly in the tables.
-Victoria
LikeLike
Hi Victoria,
I un-applied all of the payments/invoices and reapplied. After doing this, my statement is correct, but the collections main window is incorrect. Will the sales reconciliation utility possibly fix this also?
LikeLike
Hi Jelana,
Glad to hear you were able to fix the statement. Yes, it is likely that the reconciliation utility will fix the Collections information. I would certainly try it. You can run the reconciliation just for the one customer so it does not take a huge amount of time.
-Victoria
LikeLike
Hi Victoria-
Wondering if you’ve ever solved the problem of determining when an RM invoice has been ‘paid off’ twice. I use that field to determine when to pay commission and the problem comes when we pay off an invoice, and they unapply the payment and apply another payment at a later date. That could lead to me paying commission twice on that invoice. I’ve combed through the fields available and can’t come up with the logic to flag these. Let me know if this has ever come up for you. thanks!
LikeLike
Jason,
There is absolutely nothing in GP that will track this. When you unapply a transaction, no history is kept of this having been done. The new ‘paid off’ date overwrites the old one and again, no history is kept. I can think of two options that may help you:
Create some custom tracking. This would require a SQL trigger and a table to track all the history. Then you would need to build the results of what’s in the history table into your commissions report logic. More work, but allows unlimited unapply/reapply scenarios.
Hope that helps,
-Victoria
LikeLike
It does-
Thanks a lot!
LikeLike
Hi Victoria,
We had a system error when posting a cash receipt last month. When I check the invoices and aging it shows that the cash receipt was applied so I just let it go. What I am noticing now is that it did not post to the GL. If I try to drill down to the cash receipt nothing happens – almost as if its not there, so I can’t see it. And now when I run the paid transaction removal those payments will not come off of the aging report. It shows the invoice total and then the paid amount right below it. Any advice on how to remedy this?
Thanks!
LikeLike
Hi Rachel,
As a first step, I would recommend running check links on the sales series until it says ‘no errors’. If you’re not familiar with running check links, please talk to your GP administrator, as it should be done after backups and when no users are in GP. After the sales series check links comes back with ‘no errors’, run the Reconcile utility on the outstanding document amounts. 90% of the time these two steps will fix this issue by clearing out the references to the payment that does not actually exist and let you re-enter it.
-Victoria
LikeLike
Hi Victoria, I’m setting a Trade Discount of 10% in one customer in the Customer Maintenance windows. I created a Invoice with this customer and the trades discount in the sales transaction entry is 0.00. How the trade Discount in the transaction can auto fill the % with the customer card information?
regards,
LikeLike
Julio,
Are you using the Invoicing module, the SOP module, or the Receivables module to enter your invoice?
-Victoria
LikeLike
Hi Victoria,
Thank you so much for this blog. I am new to GP and I come here often to find solutions, tips, etc.
Do you know if GP has a way to view all of the changes that have been made to a customer card? We have a customer whose name was changed recently in the customer card, and we are trying to track down when the change happened, who entered it, etc.
Thank you!
LikeLike
Hi Carrie,
GP does not track this kind of information out-of-the-box. You would have had to set up something to track this ahead of time. No way to go back at this point. About the only thing you might be able to see is the last modified date on the customer card. That will be stored in the MODIFDT field of the RM00101 table.
-Victoria
LikeLike
Thanks for the quick response. That’s what I thought, but I wanted to verify first.
LikeLike
I’m using the SOP.
LikeLike
Julio,
Thanks. In that case, I have to say that I have never seen this not work. We use trade discounts with SOP transactions all the time internally and have many customers that do, as well. Silly question, but you’re saving the change to the customer record before starting to create the SOP transaction, right? If so, the only other thing I can think of to ask is whether it is possible that you have a customization/modification that is interfering with the out of the box functionality?
-Victoria
LikeLike
Thank you very much for you fast reply. Yes, we have a customization. I will check the modification and make some testing. I will let you know.
regards,
LikeLike
Hi Victoria! How are you?
Is there anywhere that GP stores historical balance forward records? I want to be able to figure out what a customers balance forwarded from June to July of 2014 for instance. Or is there anywhere GP archives the aging info for the RM00103?
Right now I am using a calculation I have written but there are exceptions that I am having a hard time with.
LikeLike
Hi Jerome,
I do not believe GP stores this anywhere, certainly not archived aging info. Unfortunately, balance forward customers are not so common, so I do not have any experience with tracking their balances. Sorry not to be able to help more.
-Victoria
LikeLike
I am having some difficulty pulling Voided payment type back from GP into CRM. I do not find the APTODNM in either the RM20201 or RM30201 tables. Returns, Credits, and Payments are all fine. Where is the VOID transaction apply written?
LikeLike
Steven,
In Dynamics GP a voided transaction cannot be applied to anything and nothing can be applied to it. So you will not find voided transactions in any apply tables, they will only be in the transaction tables – RM20101 and RM30101.
-Victoria
LikeLike
Thank you! I will rewrite my Scribe job with this in mind.
LikeLike
Hi Victoria,
Can you explain the difference between original transaction amount and current transaction amount on RM20101, when the document type is an invoice? I’m trying to gauge the total amount I’ve collected from customers, but I’m not sure what to do with those two column’s values to get accurate data.
Original Transaction always has a number, which seems quite obvious, but Current Transaction sometimes has a value, and that value can be equal to or less than the Original Transaction value, or it can be 0. In the case that the Current = 0, why would there be a line item for a received payment? Does that mean it was just fully paid off? If that is the case, why does it not just equal the Original Transaction amount all of the time?
Thanks!
Charlie
LikeLike
Hi Charlie,
The original transaction amount is the total amount of the transaction. So if an invoice had a subtotal of 100, freight of 10, and tax of 7.70, the original transaction amount would be 117.70.
The current transaction amount is what is still due on that transaction right now. If it is 0, that means the transaction has been fully applied/paid. If it is not, there is still something due on the transaction. So, for example, if I wanted to print a report of what was still due on all RM invoices, I would use the current transaction amount field. This field gets updated every time you apply documents in GP.
The above is true for all document types in RM, not just invoices. Hope that helps.
-Victoria
LikeLike
Victoria,
Working on report for new customer growth by month for each of our offices, can the CREATDDT field from the Customer Master RM00101?
Thanks,
Raul
LikeLike
Hi Raul,
This may depend on how customers are created. For example, if they are always manually entered by users, then yes. If they are imported, you might need to double check the import populates that date correctly.
-Victoria
LikeLike
great helfull
LikeLike
Hi Victoria,
I just tried to print Receivables Statement, but my report still gave me $0.00 transactions even after selecting in printing options the “Exclude Fully Applied Payments”. It should not show the fully paid payments right?
I also tried to use combination of: No Activity, Zero Balance Due, Exclude Fully Applied Payments but gave me “No records”.
Do you know can I not show the $0.00 transactions?
Thanks in advance!
LikeLike
Rotchine,
Unfortunately, I think the only way to do this would be to move all fully paid transactions to history. This is just one of the many reasons I am not crazy about the statement printing functionality in out-of-the-box GP.
If you’re looking for something more functional and user friendly, consider GP Reports Viewer – easily emailing and printing statements is just one of the features.
-Victoria
LikeLike
Hi Victoria,
Thanks for that! Did you mean, to move all fully paid transactions to history by using ROUTINE> SALES> PAID TRANSACTION REMOVAL?
But my only option for now is to use default GP report writer for some customizations…. 😦
LikeLike
Rotchine,
Yes, if you run the Paid Transaction Removal routine, the fully paid transactions should come off the GP statement report.
-Victoria
LikeLike
Victoria,
Once I’ve done running the Paid Transaction Removal, does it will affect any latest or unpaid transactions?
Thanks,
Rotchine
LikeLike
Rotchine,
Paid Transaction Removal moves fully applied transactions from open to history tables, so it will not touch unpaid transactions. I am not sure what you mean by ‘latest’ transactions?
If you are not sure of something, it is always best to test in a test company before doing anything with your live data.
-Victoria
LikeLike
Veronica-
Our GP Vendor did the upgrade in Jan and are aware of the problem. They are stumped as well. Right now the Aging report is off- the trial balance matches what we had before the upgrade however the aging is now different. No one knows what happened- our technician said he would look into it but so far it remains a mystery. We checked all the other accounts on the TB sheet. Looks like the only problem is linked to AR- AP is still correct in both categories. Inventory was correct. So we have no idea if the records were partially corrupted and the upgrade caused them to go daffy. I was hoping you might have an idea- I love the website. It is awesome.
LikeLike
Hi Marybeth,
Yes, it does sound like there is possibly ‘bad’ data in the AR tables. However, it’s very difficult to suggest anything without being able to look at the data. Have you already run check links on the sales series? And Reconcile?
If so, based on what you already found, I would probably start by doing a search for any records in the apply tables that do not actually exist. See if running this in SQL against your company database returns anything:
select
a.*, t.*
from
(select APFRDCTY, APFRDCNM from RM20201
union
select APFRDCTY, APFRDCNM from RM30201) a
left outer join
(select RMDTYPAL, DOCNUMBR from RM20101
union
select RMDTYPAL, DOCNUMBR from RM30101
union
select RMDTYPAL, DOCNUMBR from RM10201) t
on a.APFRDCNM = t.DOCNUMBR
and a.APFRDCTY = t.RMDTYPAL
where APFRDCTY is null or APFRDCNM is null
or RMDTYPAL is null or DOCNUMBR is null
-Victoria
LikeLike
Hello,
Is there any column that will allow me to detect if a customer record has been modified in a timeframe smaller than the day tmestamp in DEX_ROW_TS ? Thanks Stephen
LikeLike
Stephen,
No, not out of the box.
-Victoria
LikeLike
Hello,
on closer inspection it looks like DEX_ROW_TS is actually filled with a full timestamp such as: 2014-03-17 20:24:48.237.
One idea we are formulating is a periodic scan sweeping through RM00101 picking out all modified records since the last scan. We can then update a secondary system with demographic information.
Stephen
LikeLike
Sorry, I misread your question before. What is it exactly that you are trying to accomplish?
-Victoria
LikeLike
Hello,
I am using GP native web services to push customer creates and updates into GP13. We are also looking at a bi-directional flow back into the originating system.
Stephen
LikeLike
Hi Stephen,
Thanks for the additional detail. The problem with using the DEX_ROW_TS is that you might get a lot of false positives. If someone simply looks at a record and clicks ‘save’, the DEX_ROW_TS will update even though they might not have changed anything. If that’s ok, then no problem. If you want to avoid that, you can create triggers on the RM00101 and RM00102 tables for inserts and updates (or use the eConnect Requester) to track only real changes.
-Victoria
LikeLike
Certainly- In the Financial Module I run a summary trial balance report to get the number for AR for a specific period. From there I go back into Sales, Select ALL REPORTS and then run a Historical Trial Balance Report for the same period. Before the upgrade all of the months of 2013 were closed and balanced. Now they are off starting in June of 2013. I was told to run a report to help locate the errors. Microsoft GP-Tools- Routines-Financial -Reconcile to GL. Because 2013 is closed and financials were completed I was asked to locate the errors and repair them in 2014. And right now I am going through each client file in the Aged AR to locate the problem.
LikeLike
Marybeth,
Thanks for the additional detail. So the same report printed after the upgrade is not the same as it was printed before the upgrade? This gets me a little worried about the rest of the data after the upgrade. Which report is different? The GL trial balance or the AR historical trial balance?
I agree with not going back to 2013 to fix anything. The problem is I don’t think I can help in a few blog comments – you really need to understand why there is a difference after the upgrade. Is the report different/wrong? Did the data change? Something else? Who did the upgrade? Was it done internally, or did your GP partner do it for you? Have you talked to them about this?
-Victoria
LikeLike
Victoria-
We just upgraded GP to 12.00.1.1482. Before the upgrade AR Trial Balance and AR Aging matched. After the upgrade they did not. I first tried the the reconcile to GL function, it took me back 8 months and includes periods that were closed at the time of the upgrade. After doing quite some digging I found a payment that was posted to our customer where the payment shows as HIST but the invoice is OPEN. When I highlight the invoice and click Applied payments I can see the payment on the screen. If I highlight that payment and click document number I get the error that the payment doesn’t exist. Any ideas how to fix this? Or even a simpler way to identify what accounts no longer match as it took me quite a bit of time to uncover this one?
Thank you
LikeLike
Hi Marybeth,
When you say you’re comparing the “AR Trial Balance” to the “AR Aging” – can you be more specific? Usually the AR Trial Balance is the same as the AR Aging. Can you please let me know specifically what reports you are running, or where you are looking?
-Victoria
LikeLike
Good day Victoria,
Seeking your very useful advice once more.
A staff member accidentally deleted a customer record, is there anyway to restore the deleted customer record without doing a complete restore of our database?
LikeLike
Carleesha,
You should be able to manually re-enter the customer record.
-Victoria
LikeLike
Hi Victoria, I am such a nob, I forgot to tell you that the customer currently has sales history in our database. Will re-creating the customer record automatically link the record to the sales history?
LikeLike
Carleesha,
I am curious how GP allowed the customer to be deleted if there are transactions for it. Was it done in SQL? In any case, the only way to find out if this will work would be to try it. You don’t have anything to loose at this point. I would run check links and reconcile afterwards.
-Victoria
LikeLike
We were wondering the same thing but we had no one working in SQL at that point We will definitely try your suggestion though and keep our fingers crossed.
Thank you Victoria.
LikeLike
Hi Victoria, the recommendation worked along with manually removing some empty records from a few RM tables that were causing the reconcile to fail.
LikeLike
Hi Victoria,
Congrats on the MVP status again, you surely earn and deserve it. I am working on a real-time integration between Oracle and GP 2013. We are using e-connect to pull in Sales/Invoices, Debit memos and Credit memos (RMDTYPAL 1,3,7) from Oracle into GP RM. I understand that GP does not allow me to auto-post these Docs via e-connect. However I do need all of these to auto-post so that the customer balances can be updated back in Oracle in real-time along with other balance changes. This ensures the Oracle Max credit limits are not exceeded for Customer Orders in Oracle. Can this be accomplished through Setup>Posting settings for a 3 doc types mentioned? Or do I need to consider a Dexterity SDK background application, or possibly a 3rd party autoPost.dll? I’m really looking for the shortest path to victory here. Thanks!
LikeLike
Hi Chris,
There is nothing you can do with GP out-of-the-box to post imported transactions automatically. I would recommend looking at PostMaster by Envisage to accomplish this for you.
-Victoria
LikeLike
Thanks Victoria, I will look into PostMaster. If we decided to write this ourselves, would a windows service using the Dexterity SDK suffice for development? Just want to consider all options.
LikeLike
Chris,
I think it would be pretty difficult to write this yourself. We’ve done a lot of customizations over the years and my experience is that if it’s already been written, it will cost you less to buy it than to write it yourself. Just my 2 cents.
-Victoria
LikeLike
I agree on that for sure. Thanks again.
LikeLike
I’m using excel to create a report which can search by item in order to pull up all SOPs with that item on them, and also return customer name and email information. I think I’ll need columns for item number, SOP number, Customer Name and Email (user def 1 in RM00102 is where we keep that), I’m just not 100% sure which to use so any help would be greatly appreciated. Thanks!
LikeLike
Hi Tom,
Could you please be a bit more specific in what you need help with? I am just not quite sure what you’re asking. 🙂
-Victoria
LikeLike
Thanks for the reply Victoria. I’m asking which tables to use given the query I’m trying to create.using the fields I indicated. I’d attach screenshot but I don’t think I can here. Thanks again.
LikeLike
Tom,
Sounds like you need SOP10100 and SOP10200 for the order information – these will actually have everything you’ve listed except the email address. The table to check for that will depend directly on where you are storing the email address. If you’re actually using the Customer User Defined 1 field for that, it will be in table RM00101. However, that’s limited to 20 characters and is typically too short to store email addresses.
-Victoria
LikeLike
Great, working so far! For SOP# does it matter if I use 10100 or 10200? Thanks 🙂
LikeLike
The SOP number will be in both the tables, you can use whichever. Either way, you will need both tables, as the SOP10100 will have the customer information, whereas the SOP10200 will have the item information.
-Victoria
LikeLike
Hmm, I may have to rethink this. I want every SOP that the item is listed on to be returned in the search- that isn’t happening right now.
LikeLike
It sounds like you need to do this in SQL, then pull it into excel if its necessary. To pull all this into excel will get really ugly – you’d have to bring in so many different fields and then do V-lookups. You’re better off creating a SQL view, pulling that into Smartlist Builder or Excel Builder and working from there.
LikeLike
Victoria, as always you’re blog has been an incredible help, however this one has got me stumpted. It’s my understanding that the “Average days to pay” in GP is displaying the number of days from the document date. Our customers can have varying terms based upon the order type, so I while 45 days may be late for 1 order, it could be early for another one. I’m trying to create a view that shows me the average days late (or early) based on the due date. I can’t see to figure out how to identify when the document is paid in full. Do you have any suggestions? From there I can see trends (slowing, increasing) and look at different time frames.
Thanks in advance for any suggestions.
LikeLike
Joe,
GP stores the apply date of when the document was paid in full in the RM20101 and RM30101 tables. The field is DINVPDOF.
-Victoria
LikeLike
That’s exactly what I needed, thanks again Victoria!
LikeLike
Victoria love the site and appreciate all the help you give us “mere mortals” I have a question regarding credit card fees.is it possible to charge a transaction fee if a customer pays by credit card. I am a wholesaler and with fees being charged at up to 2.5% per transaction it can take out a big chunk of GP Margin. I am using dynamics gp 2010
LikeLike
Hi Mark,
I don’t think this is possible in out of the box GP. It would need to be a customization or entering a ‘debit memo’ in addition to the invoice…which is a little messy and would need to be calculated manually. 😦
-Victoria
LikeLike
Hi Victoria – I hope this is a quick question. I have a RM payment that was entered with a document date of 1/10/4111. We can’t apply or void the payment. How can I can change the document date? Is there a SQL script available? Can we open up the year 4111 in GP and then void?
Thank you!
Bob
LikeLike
Hi Bob,
I’ve not done this myself, but looks like this forum post will answer your question: https://community.dynamics.com/gp/f/32/t/100531.aspx.
I strongly recommend Doc Date Verify, so this does not happen again.
-Victoria
LikeLike
Victoria,
We have an issue with someone posting documents in the sales -> “transaction entry” window (not “sales transaction entry”) individually and immediately getting an error stating that her batch is in batch recovery. The documents make it to RM20101. She’s not posting it in a batch, it’s inserting a record with her user name in SY00500 (which i understand is normal), and after posting, the bachsttus becomes an 11. The record shows 0 documents in the batch (since this isn’t really a batch) and the batch number doesn’t show up in batch recovery. Then when she tries to open the sales transaction entry window she gets a transaction level posting error (“your transaction hasn’t finished processing”, etc). I have to delete that SY00500 record for her to continue working, only for it to happen again every single time. None of our other users are experiencing this iseus. We haven’t tried adding to a batch a posting the batch because she’s never had to do that. Any suggestions? Security mirrors other in her position.
Thanks in advance.
LikeLike
Jim,
To quickly rule out a security issue, try this:
If this works without the error, then you know it’s a GP permissions issue.
-Victoria
LikeLike
Victoria,
I’m trying to write a query that only returns the current customer balance where the balance has changed since the day before. I was thinking I could use the LSTTRXDT from the RM00103 table. Do you know if the LSTTRXDT includes all transaction types that would affect the CUSTBLNC? Thanks
– Rob
LikeLike
Hi Rob,
I don’t have source code to be able to tell you thins definitively, but my educated guess would be that LSTTRXDT would only include actual transactions posted. So if you had a situation where you applied 2 existing transactions and took a discount or writeoff, which would change the customer’s balance without a new transaction being posted, that would not be captured in the LSTTRXDT.
-Victoria
LikeLike
Hi Victoria,
I’m having challenges on how to generate customer balances trend on GP.
i want to be able to generate a report that will compare each customer balance last month to this month, then look at quarterly, bi-annually and yearly. look at the sample of report below.
Customer Name Balance Jan Balance Feb Balance March
Customer A xxxxxxx XXXX xxxx
Customer B xxxx xxxxx xxxxx
Customer C xxxxxx xxxxx xxxxxxx
The report will help me to check if my collection or Receivable manager is working or not
Rgds
Seafetcher
LikeLike
Hi Seafetcher,
If I understand correctly, what you’re asking for would require a separate monthly historical trial balance for each customer. GP does not store this, so each would have to calculated. While that is certainly possible, it’s pretty complicated and probably not something I would post on the blog. If this is something that you’re interested in having created for you as a consulting project, please let me know.
-Victoria
LikeLike
Hi Victoria,
It’s been a few years, but I’m back again with a few new questions. I’m updating our statement form (RM Statement Long Form) in ReportWriter. We print on a custom form, and the default document numbering for some document types is too wide for the column in which we want the value to display. For instance, our credit memos are of the format: CREDT000000000001. We will never come close to going that high, so we can safely remove a bunch of zeros to create shorter numbers. I know where to change this going forward, but that won’t help for existing documents. Is it safe to update document numbers, and if so, what is the best way to determine the tables that need to be updated? I have some familiarity with the tables, but want to make sure that if we do this, we don’t break anything. Our invoices and returns are okay (they use a prefix and six digit numbering), so we’re looking at doing this for credit memos, debit memos, and payments (the only three document types we use on the Receivables Setup Options screen). Can you tell me which tables would be affected for these three document types? I think RM20101, RM20201 (and their historical counterparts), but are there others? And after making the change, what would be the recommended procedure – could/should we run Check Links?
As usual, thank you for the wonderful information here and the time you give to help us navigate GP!
John
LikeLike
Hi John,
I would strongly encourage you not to do this. These could be in dozens of tables, and in some they won’t be called ‘DOCNUMBR’. It would be very easy to significantly compromise the integrity of your data by not doing this exactly right and in all the tables. I certainly don’t feel comfortable giving advice on how to do this in a blog post, without examining your data and having a good understanding of all the products, modules, customizations, etc. that you have in place.
To get around the issue on your statements, you might instead consider writing some custom code to replace ‘00000000’ with ‘0’ or something like that, on the report only. That way it’s just a report display issue. (Of course, your users would need to know this is happening, so they are not searching for CREDT0000001 when it’s really CREDT000000000001 in the system.) I am not sure how easy it is to achieve something like this in Report Writer, but I would think this should be less work than changing the actual data.
-Victoria
LikeLike
Thank you for your guidance – yes, it sounds like updating the document numbers is a patently bad idea, and modifying the report is a good alternative.
Is it safe to assume that I can change the document format for new documents? So, for instance, for credit memos, the “Next Number” is currently CREDT000000000236 – I would change it to CREDT000236. So the numbers would run CREDT000000000001 to CREDT000000000235, and then CREDT000236+. No new number would have “000000”, so from a reporting end, they would all display the new format, and as you said, internally we would just need to know that below a certain number the real document number has the longer format.
Also, ReportWriter seems to be the default system for creating reports, and since our use of GP is pretty simple, we haven’t done much in the way of reporting. My experience is that it is a cumbersome and unintuitive UI, but there are lots of built-in reports, and report deployment is fairly straightforward. I know Crystal Reports is one alternative (and I think SQL Server’s built-in reporting is another). In your experience, is ReportWriter widely used, or do many customers choose an alternative like Crystal or SQL Server reporting that provides a better environment to create reports? I realize every customer has different needs, I’m just trying to understand the landscape of how companies tend to make GP work for them – especially those on the simpler end. We’re a software company that sells services to a relatively small customer base, so we don’t deal with shipping, back orders, varying tax scenarios – it’s pretty vanilla. But we do have basic needs, like generating invoices and statements, tracking A/R, etc.
Thanks again!
John
LikeLike
Hi John,
There is no problem with changing the ‘next’ numbers in GP. The only issue might be sorting – for example, if you dump all the transactions out into Excel from a SmartList, then sort by doc number, they will not sort how in the ‘right’ order. This is not usually a huge deal, but something to keep in mind. And the sooner you change the next numbers, the less time you have to deal with current transactions having the insane amount of zeros.
As to the reporting tool question – there are pros and cons to all the options and I often see companies using a combination of the tools, not simply pick one. I personally am not a big fan of Report Writer (RW) for just the reasons you list – terrible UI, cumbersome, also some things are just not possible, or possible but with so much effort, it makes you want to strangle someone. My company makes a product called GP Reports Viewer that lets you run any Crystal and SQL Reporting Services (SSRS) reports inside of GP. So our invoices and statements are created in Crystal and look fabulous (at least we think so) and don’t have any of the limitations of the out-of-the-box reports. For statements, we like not having to run paid transaction removal before being able to print them so that paid transactions do not show up there. For invoices, we have a lot of custom logic – like hiding and showing sections based on customer class, or extender and user-defined fields. All of this is a breeze in Crystal (or SQL – most of the time we create SQL views or stored procedures to use in our reports). We also use SSRS reports for some of the reports we run that have a lot more columns, SSRS is better geared towards that than Crystal. Although another option for this would be creating reports directly in Excel – ie, having Excel point to a SQL view or stored procedure directly.
The right approach will typically depend on a combination of your reporting needs, resources, experience and budget. For example, if you have already put a fair amount of time into learning how to use RW and are comfortable with it, and don’t have too many critical needs it cannot meet, then it is hard to justify the time to purchase and/or learn something else.
Hope that helps. If you would like to discuss this in more detail, let me know.
-Victoria
LikeLike
Hi Victoria,
I am trying to create a commission report based upon when invoices were paid. Example: October commissions will be paid on all payments received in October.
I can see payments in receivables transactions, but I need to see which invoices were paid. What table(s) should I use?
Thanks!
LikeLike
Hi David,
You should be able to use my AR Apply Detail view for this.
-Victoria
LikeLike
Thanks very much, it has what we need 🙂
LikeLike
Hi Victoria,
I’m rummaging through the RM tables in search for a buyer name that i can link back to the POP tables. The way the user can view this buyer name is by opening the “Receivings Transaction Inquiry Zoom”, then opening “Receivings User-Defined Fields Inquiry” where the “Buyer Id” is said to be user defined field 3. Do you know where i should be looking?
Thanks Victoria!
LikeLike
Hi Don,
RM tables hold Receivables (AR) information. You’re looking for POP tables. I believe the information you’re looking for is in the POP10306 table.
-Victoria
LikeLike
Thank you Victoria,
You are correct! I figured it out not too long after i sent you the question, but couldn’t figure out how to tell you to disregard it 🙂
Thank you as always!
LikeLike
Hi Victoria,
What is the Series 4 in TX30000 table?
Thanks,
Anvar
LikeLike
Its Purchasing… Got it.. 🙂
LikeLike
Hi Victoria,
I am relatively new to GP and your site has been a great help as I continue to learn. I am trying to clean up and speed up our system and I have determined that my predecessor closed out years, but never processed purchase document reconciliation or moved purchase documents into history. We now have some 7 years of data “open” in the system.
Could you explain in beginner’s terms the purpose of these processes and whether there are potential negative consequences for performing them now?
If it matters, we are using GP 10.0
Thank you in advance for any comments.
Casey
LikeLike
Hi Casey,
You should not have to run reconciliation utilities unless something is wrong. Moving purchase documents to history simply moves completed PO’s to a different set of tables – if you do this, you may want to first confirm that you’re keeping history (GP | Tools | Setup | Purchasing | Purchase Order Processing – there are 3 History checkboxes in the middle). The reason to do this would be to allow searching of open purchase orders only, which should greatly speed things up if you have a lot of closed PO’s. The only negative I can think of is if you have any custom reports that are only looking at the open (non-historical) data, they will now not see all of the data that you might want. It’s not very typical, but I have sen it happen.
Hope that helps.
-Victoria
LikeLike
Hi Victoria,
Have you ever seen a case where the noteindx on RM20101 transactions is 0.00000, even though there is a note attached to the transaction?
LikeLike
Hi Nysa,
There will be many times when the NOTEINDX is 0, often it only gets a valid number when a note is created. What I have never seen is an actual note in the SY03900 table with a NOTEINDX of 0. I would think this is an issue, as you could have many objects (not just transactions, but master records) in your system with a NOTEINDX of 0 and now they will ALL point to this one note.
-Victoria
LikeLike
Victoria, we use the note beside the Document Number in Receivables Transaction Eqnuiry Zoom or the Receipt Number in Cash Receipts Enquiry Zoom.
thanks
LikeLike
Geraldine,
Those are all stored in the SY03900 table. They are linked to the transactions by the NOTEINX.
-Victoria
LikeLike
Victoria, we attach comments to our RM transactions (invoices, payments etc) that help us work out what is going on with the client without using the collections module.
Could you please tell me what table these comments reside in?
LikeLike
Hi Geraldine,
Can you tell me exactly what field on each of the windows you are populating with your ‘comments’? There are a couple of different ones I can think of, so I want to make sure we’re both talking about the same fields.
-Victoria
LikeLike
I’ll try to duplicate the issue in my test environment and keep you posted. Thanks.
LikeLike
Hi:
We have a customer that runs Paid Transaction Removal with the wrong parameters. They know that they can unapply RM from history using the PSTL one by one, but they are having another problem with this issue and Bank Reconciliation. All the voids (Voids and NSF) that are in the History tables are not available in Bank Reconciliation Deposits to be deposited. Is this a typical GP BR behavior or we got something wrong. I look into the CM20300 table and they are there, but I look for them in the Deposits Window and they does not appear.
Thanks for your help.
Jesus
LikeLike
Jesus,
When you NSF a check it does not need a separate ‘deposit’. It will show up in the Checkbook right away as a withdrawal.
For voided checks, I cannot duplicate what you are seeing. And I am not sure how moving the voided payment to history would make it disappear from the Bank Deposits window. Can you duplicate this at will?
-Victoria
LikeLike
Hi Victoria, I am running a RM integration from Oracle to GP 10 via linked server and advanced ODBC data sources. I am using SQL views to the Oracle DB for Customers, RM headers, and RM details with relationships and mappings.
I am selecting only customers NOT IN RM00101 in my view to insure I don’t bring over existing GP customers. I am selecting NOT IN RM10101, RM20101, RM30101 in my view to insure I don’t bring over DOCNUMBRs that already exist in GP. My customers and headers are coming over just fine and I’m mapping my Sales and FRT to the Receivables Transaction Screen. This is automatically creating the distributions just fine. However, I need to manually create distributions for local and sales tax. When I do so, the Sales tax distributions come over fine, but seem to override the distributions for Sales, FRT, etc … Any suggestions on how to keep the distributions created from the RM Transactions entry while also adding the Sales Tax Distributions I create in the detail file. Here is a link to a screen shot that shows the problem that I am having.
Thanks in advance for the direction!
CSH
LikeLike
Hi Chris,
Do you mean that you’re importing directly to SQL tables instead of using a tool like eConnect or Integration Manager? If so, that’s really not something I would recommend. Why not use one those tools and have GP populate/calculate the taxes for you automatically?
-Victoria
LikeLike
I am using IM – RM destination.
LikeLike
Oh great! Then why not have GP calculate the taxes for you?
-Victoria
LikeLike
Hi Victoria, once I set up the necessary tax details and schedules per customer and used those schedules in the RM IM imports, all worked as expected. I’m having some small issues with the rounding of tax. Largely because my Oracle system rounds the total of local and state where GP is rounding local and state individually … producing some occasional annoyances. I’ll work through that one. Thanks for your help on this and the value you provide to the community. I have some additional projects that may require your expertise. Please email me with your contact details when you have a few minutes. Thanks! CSH
LikeLike
Hi Chris,
Yes, GP rounds all the taxes individually, then adds them up. If you were using the SOP module with line items on your invoices, it would be even worse, as taxes for each line would be calculated separately, then added up. One thing to try to mitigate the issue is to see what rounding settings you have on the Tax Details. I believe they usually default to rounding up, whereas I think they should be rounded to the ‘nearest decimal place’.
If that does not help, you could possibly check for this in your code and add a Misc amount (to add) or Discount amount (to subtract) to make sure the total matches your Oracle total. This is not very pretty, but if the goal is to have the total match exactly, this is one way to do it. There may be other alternatives depending on your other requirements/needs.
-Victoria
LikeLike
Hi Victoria,
This is the first time I have posted on your site but have found useful information on here many a time 🙂 Thank you for the site 🙂
I have a query which I wonder if you could help with…is it possible to see SOP transactions that have been deleted – is there a table somewhere obscure that holds this information – I am trying to trace “Missing numbers” for a client going back about 4 years!
Any advice would be gratefully appreciated
Many thanks
Trace
LikeLike
Hi Tracy,
Thank you for your kind words and for frequenting my blog!
Unfortunately, GP does not track anything that is deleted. That’s why many companies don’t allow deleting SOP transactions and only allow voids. 😦 The only alternative to this would be to create a custom SQL trigger and table to store anything that is deleted, but that would only work going forward, can’t go back with something like that.
-Victoria
LikeLike
We need to break out tax into local and state. and Some customers only pay state not local. See query below for how i am detemrining tax from oracle before moving to GP distributions.
/*
TAX Breakdowns
2750 St. Martin parish 3.5 0.466666667
2800 state 4 0.533333333
7.5 1
*/ SELECT IH.INVC_NUMBER AS DocNo, ‘0-000-2750-000’ AS Account,
13 AS DistType, CASE WHEN EXISTS
(SELECT LEFT(RTRIM(LTRIM(c1.company_name)), 15)
FROM [ORACLE11]..[QCTL].[COMPANIES] C1(nolock)
WHERE LEFT(RTRIM(LTRIM(c.company_name)), 15) = LEFT(RTRIM(LTRIM(c1.company_name)), 15)
GROUP BY LEFT(RTRIM(LTRIM(c1.company_name)), 15)
HAVING COUNT(LEFT(RTRIM(LTRIM(c1.company_name)), 15)) > 1) THEN (LEFT(RTRIM(LTRIM(c.company_name)), 10)
+ ” + LEFT(RTRIM(LTRIM(C.[COMPANY_CODE])), 5)) ELSE (LEFT(RTRIM(LTRIM(c.company_name)), 15)) END AS Company_ID, C.COMPANY_NAME AS Customer_Name,
C.COMPANY_CODE AS [Short Name], 0 AS Debit, CASE WHEN c.company_name = ‘CHEM AIR’ OR
c.CMP_AUTO_KEY = ‘6796’ THEN 0.00 WHEN IH.INVC_TYPE = ‘M’ OR
CAST(TOTAL_PRICE AS float) ‘1/1/2013’) AND (IH.post_status IN (2, 3)) AND (C.COMPANY_CODE NOT IN (‘113’)) AND (IH.TOTAL_PRICE 0) AND (LEFT(IH.INVC_NUMBER, 1)
‘I’) AND (C.COMPANY_NAME ‘Helifab’) AND (C.COMPANY_NAME NOT LIKE ‘%GML%’) AND (IH.INVC_TYPE IN (‘I’, ‘D’, ‘M’)) AND
(IH.INVC_NUMBER NOT IN
(SELECT DOCNUMBR
FROM dbo.IM_GetAllDocNums)) AND (IH.TAX_amount 0)
UNION ALL
SELECT IH.INVC_NUMBER AS DocNo, ‘0-000-2800-000’ AS Account, 13 AS DistType, CASE WHEN EXISTS
(SELECT LEFT(RTRIM(LTRIM(c1.company_name)), 15)
FROM [ORACLE11]..[QCTL].[COMPANIES] C1(nolock)
WHERE LEFT(RTRIM(LTRIM(c.company_name)), 15) = LEFT(RTRIM(LTRIM(c1.company_name)), 15)
GROUP BY LEFT(RTRIM(LTRIM(c1.company_name)), 15)
HAVING COUNT(LEFT(RTRIM(LTRIM(c1.company_name)), 15)) > 1) THEN (LEFT(RTRIM(LTRIM(c.company_name)), 10)
+ ” + LEFT(RTRIM(LTRIM(C.[COMPANY_CODE])), 5)) ELSE (LEFT(RTRIM(LTRIM(c.company_name)), 15)) END AS Company_ID, C.COMPANY_NAME AS Customer_Name,
C.COMPANY_CODE AS [Short Name], 0 AS Debit, CASE /*Credit Tax for Chem Air – 100% State*/ WHEN (IH.INVC_TYPE = ‘M’ OR
CAST(TOTAL_PRICE AS float) = 0) AND (c.company_name = ‘CHEM AIR’ OR
c.CMP_AUTO_KEY = ‘6796’) THEN (ISNULL(IH.TAX_AMOUNT, 0)) /*Credit Tax for Others – */ WHEN (IH.INVC_TYPE = ‘M’ OR
CAST(TOTAL_PRICE AS float) < 0) AND (c.company_name ‘CHEM AIR’ AND c.CMP_AUTO_KEY ‘6796’) THEN (ISNULL(IH.TAX_AMOUNT, 0) * 0.533333333)
* – 1 /*Sales/Invoices Tax for others */ WHEN (IH.INVC_TYPE = ‘I’ OR
CAST(TOTAL_PRICE AS float) >= 0) AND c.company_name ‘CHEM AIR’ AND c.CMP_AUTO_KEY ‘6796’ THEN (ISNULL(IH.TAX_AMOUNT, 0) * 0.533333333)
ELSE (ISNULL(IH.TAX_AMOUNT, 0) * 0.533333333) END AS Credit
FROM ORACLE11..QCTL.COMPANIES AS C WITH (nolock) LEFT OUTER JOIN
ORACLE11..QCTL.INVC_HEADER AS IH ON C.CMP_AUTO_KEY = IH.CMP_AUTO_KEY
WHERE (IH.INVOICE_DATE > ‘1/1/2013’) AND (IH.post_status IN (2, 3)) AND (C.COMPANY_CODE NOT IN (‘113’)) AND (IH.TOTAL_PRICE 0) AND (LEFT(IH.INVC_NUMBER, 1)
‘I’) AND (C.COMPANY_NAME ‘Helifab’) AND (C.COMPANY_NAME NOT LIKE ‘%GML%’) AND (IH.INVC_TYPE IN (‘I’, ‘D’, ‘M’)) AND
(IH.INVC_NUMBER NOT IN
(SELECT DOCNUMBR
FROM dbo.IM_GetAllDocNums)) AND (IH.TAX_amount 0)
LikeLike
I thought a bit more about what you are saying …. If my existing customers have the right tax setup and my new customers creating through IM have the right tax setup created during integration, then GP would be able to calculate the correct local and/or state tax per customer during the IM import. Is that correct? If so, would I need to go back and configure tax for each customer and can I provision that in IM for the new customers created daily during the IM import?
LikeLike
Yes, exactly.
-Victoria
LikeLike
Hi Victoria, new to your blog but I’ve found it very helpful so thank you for your hard work to keep this forum going.
My question is about distributions at the cash receipt level. The sample scenario is that a customer has a $500 receivable and a $475 payment was received because our friend, the bank, has taken a nice little $25 off the top.
Would it be possible to create a cash receipt that accounts for the actual cash amount received, the bank fee, and also clears the receivable in its entirety?
As we use a 3rd party bank rec application which determines the book value directly from the GL, I’m less concerned about the processing of a traditional GP cash transaction (so that it properly hits the GP bank rec module), and more interested in the distribution and the proper clearing of the receivable.
I was able to play around with this scenario a little and the only was I was able to do this was to create a cash receipt for the whole receivable amount, and then create two distribution lines with transaction type CASH, one being for the $475 DR Cash, and the other being for $25 DR the bank fee account.
I’m not totally comfortable with the cash receipt amount not matching the actual cash received so do you have any other ideas as to how this can be processed?
Thanks in advance for your insight.
LikeLike
Edmond,
This is a pretty typical scenario when receiving payment via wires, especially internationally. Here is one way to handle this, using the numbers in your example:
This will take care of recording the cash receipt as the amount you actually got, and will work better with the Bank Rec module if you are using it. The only negative I can see to this approach is if you are actually using Writeoffs to track ‘real’ writeoffs. Now these bank fees will be tracked together with real writeoffs and you may see over-inflated writeoff totals for these customers in your reporting. Usually this is not an issue, but just wanted to mention it.
Hope that helps.
-Victoria
LikeLike
Thanks very much Victoria, this helped a lot. My impression from the business is that a high percentage of short pays are in fact due to bank charges so I think we should be OK on our side.
Edmond
LikeLike
Sharing some info on the Credit Limit fields:
Credit Limit Type (CrLmtTyp) – integer: 0 = No Credit, 1 = Unlimited, 2 = Amount
Credit Limit Amount (CrLmtAmt) – currency;
Credit Limit Period (CrLmtPer) – integer
Credit Limit Period Amount (CrLmtPam) – currency
LikeLike
Thanks Curtman! 🙂
I added the credit limit type values to the list.
-Victoria
LikeLike
Good afternoon, Victoria. As always – your blog is my first and foremost stop for researching issues and solving problems with our implementation. One of the ARs accidentally deleted a receivables batch that had already been applied to the wrong invoice. I was able to remove a transactions from rm20201 and from rm10201. Then I ran CheckLinks on the sales module. It told me that RM Distribution Work File: the transaction for this distribution information is missing so the distribution information was removed. And … RM Key File: The transaction for this keys record is missing. The keys record has been removed. My problem is the invoice is still not showing the whole invoice amount and the system is telling me that particular check number has already been applied. (Yes – I’m doing this in a test system first.) Can you, once again, steer me in the right direction? Thanks in advance!
LikeLike
Billie Dee,
I would recommend running Check Links again, until you receive “No Errors” back. When there are records removed, there may be several passes of Check Links needed to remove all additional/related records. Then I would run Reconcile on the “outstanding document amounts”. You can run it just for the one customer involved to speed it up.
-Victoria
LikeLike
Once again you’ve come to our aid. The solution worked perfectly. Thank you very much.
LikeLike
Hi Victoria,
I’m new to GP so this question may be silly, but…
Do the account distribution types (PURCH, SALES, RECV, etc) have any effect on the GL and financial reporting? If not what are they used for?
Thanks in advance.
Casey
LikeLike
Casey,
No, the distribution types do not have any effect on the GL or financial reporting. They are used to make sure that your distributions match up to the totals on your transaction and to facilitate automatically populating the proper distributions. They are also very useful for reconciliations of sublegers to the GL in case that becomes necessary.
Towards the bottom of this blog post I show an example of how the distribution types match up to the transaction totals.
-Victoria
LikeLike
Hi Victoria,
Let me first say your blog is awesome. I’ve used it from time to time as a reference and I am very pleased with the contents. Keep up the good work!
Now for the reason I am posting. I am facing a situation at my workplace. We have a sales invoice which failed to save in the receivables module. The information seems to be correct in the SOP tables but there are no entries in the RM tables, namely, RM20101, RM10601, RM10501, RM10101 and also the MC020102. I have tried to replicate such a case with no success. This is the second time this problem has happened and I would really like to know if you have been in this situation before or if you know what the problem could be. Any help will be appreciated.
LikeLike
Hi Joel,
This is certainly not a known issue and I would not expect you to be able to duplicate it at will. My first step would be to make absolutely sure that the invoice is really not anywhere in RM tables. Can you check to see if it is in the RM00401 table? Also, which SOP tables is the invoice in?
-Victoria
LikeLike
Victoria,
The invoice exists in the RM00401 table. The SOP tables where the invoice is are: SOP30300, SOP30200, SOP10106, SOP10105, SOP10102 and SOP10101.
Joel
LikeLike
Joel,
What is the value in the DCSTATUS field in RM00401?
-Victoria
LikeLike
Victoria,
The value for that field is 2.
Joel
LikeLike
Mi bad, the value for the field is 0.
Joel
LikeLike
Joel,
0 means the number was assigned in RM (which happens during the posting of the SOP invoice), but not actually posted in RM. Did something go wrong during posting? Like someone lost connection to the server, or crashed out of GP, or received an error? Have you run check links on the sales series?
If this invoice is truly not in the RM tables, a work around may be to recreate it manually directly in RM. You would also want to check the GL to see if this transaction made its way in there properly. Past this, you may want to get further help on this from either your GP Partner or Dynamics GP Support, so they can take a look at your data.
-Victoria
LikeLike
Victoria,
Many thanks for your quick responses. The user have not reported having received an error while posting, nor have any of our GP clients crashed in a long time. I have run several check links for the sales series (every sunday) but the records are not repaired.
As I mentioned before this is the second time this happens and I will rebuild the data manually -which worked fine the first time.
Again, thank you very much for taking the time to answer my questions. You are great!
Joel
LikeLike
Joel,
Well, that would seem to indicate that the invoice posted and should be in the RM20101 table. You’re positive it’s not in there? What is the TRXSORCE for this invoice in the RM00401 table? Also, what is the value of the NEGQTYSOPINV field?
-Victoria
LikeLike
Victoria,
I made a mistake with my first query to RM00401. The actual value for the field is 0.
Joel
LikeLike
I love your site!!! I am so glad i found it . I have been able to get some answeres to pending question I was having. So now I have a question for you. I have a stange thing happening with my cash receipts.. When I create a Cash receipt in Sales I will sometimes have a wite off for CC fee’s that I need to enter. When I enter these into the apply screen as a Write off the first thing that happens is the GL account does not pull over from the Customer Card (there is an account in the write off field under accounts for that customer). The second thing that happens if I forget to go in an enter that account in the distributions and post that receipt it will post even though there is an error (account missing). It then will create a batch on the GL side with the account missing and will have to enter it there before posting the GL batch.
LikeLike
Hi Steph,
It doesn’t sound right that the write off account would not pull from the customer properly. I can’t remember ever seeing that before, what version and build of GP are you on? I just tested this to make sure and it’s working properly on my GP 2010 SP 2.
If you have checked the customer setup and are confident it is correct and do not have any customizations or 3rd party products that may be causing GP to behave differently, then you might need to talk to your GP Partner or GP Support about this to see if they can help you determine what is going on.
For the other part of your question, I have seen situations where a subledger transaction will post without all the accounts needed and then it will get stuck in the GL, since that will not allow a posting without all the accounts. However, it has been a while since I have seen that, and on my GP 2010 SP 2 I get an error when I try to post a transaction like that, so it’s possible this has been fixed with a new version or service pack you do not have. One way you can combat this, especially if the write off account is usually the same, would be to enter it globally for the company, under the Posting Accounts. That way, if the customer does not have the account set up, the global posting accounts will be used.
-Victoria
LikeLike
I found the problem to this. Just to clarify.. We are on the latest version of GP all SP up to date.. Posting accounts were in the global setup for the write off account, and on the customer card write off account.
Looks like what happened is a default account was entered on the customer cards, then at some point the Account was deleted(no activity so that was ok) But it left the customer card in limbo thinking that there was an account, but showed an error account not found. Even though I had added a valid account to the one customer I was working with, there were several customers that i hardly used that had this account not found error.
What led me to look into this was while watching the SQL tables when entering a cash receipt it was trying to use a that GL account(which did not exist). It only verfied that there was an account, not that it was valid. This was all on the SQL side on the GP side the account looked blank, so didn’t know it was trying to attach an invalid account. Hence when it got to a Journal Entry it would bomb because at that point it validates the account.
Easy fix was to go through all the classes and roll down a valid account this took all references to the non existant account and now it works fine. Until all references to that account was gone the problem did not go away. All customers with that problem had to be fixed.
LikeLike
Hi Victoria,
I was hoping you could point me in the right direction, i need to build a cash management report for my company, basically see any and all outstanding COD payments, for Debit memos,credit memos and payments for a certain date range, i thought i would need to join the RM20101 table for the RMTDYPAL of (3,7,9) and get COD doc and docdate from the SOP30200 table, then subtract CURTRXAM from ORTRXAMT to see the outstanding amount, Am i wrong in thinking this?
Hope to hear from you soon
LikeLike
Mark,
I am not sure what you mean by ‘outstanding COD payments’. Can you please explain exactly what that means in your usage of GP? Also, typically debit memos are not grouped together with credit memos and payments – you might need to explain more about how you are using these to help answer your questions.
-Victoria
LikeLike
Hi, sorry for the delay and vague question.
In essence , i need to find all COD documents(PYMTRMID = ‘C.O.D’ on SOP30200 table)then find all unapplied doucments/amounts linking to these sop30200 docs, i think im supposed to link sop30200 docs to rm20101 docs.but this doesnt seem to be working.
Belos is a query im using to get all unapplied amounts
select
rtrim(RM1.CUSTNMBR)CUSTNMBR
,rtrim(RM1.CUSTNAME)CUSTNAME
,ORTRXAMT
,CURTRXAM
,DOCDATE
,rtrim(DOCNUMBR)DOCNUMBR
,rtrim(TRXDSCRN)TRXDSCRN
,rtrim(CreditCollectorName)CreditCollectorName
,rtrim(RMDTYPAL)RMDTYPAL
from RM20101 R2
inner join RM00101 RM1 ON RM1.CUSTNMBR =R2.CUSTNMBR
INNER JOIN ACT40300 A4 ON A4.CUSTNMBR = R2.CUSTNMBR
–inner join SOP30200 S32 ON S32.SOPNUMBE = DOCNUMBR
where –RM1.CUSTNMBR in(‘1000032′,’1000042′,’1000009′)
–AND DOCDATE >’2011-09-14’
–AND DOCDATE <'2012-09-23'
–and
CURTRXAM ‘0.00’
and RMDTYPAL = ‘9’ –payments only–>=7
and VOIDSTTS=0
–and ORTRXAMT CURTRXAM
order by CUSTNMBR
now i need to link this to SOP30200 COD doc.
Does this make more sense?
LikeLike
Mark,
Sorry, still not understanding the logic of what you’re trying to do. Your code below looks like it is getting all unapplied payments. But if a payment is unapplied, how can you find invoices it is linked (or applied) to? What logic can you use to link them?
-Victoria
LikeLike
Hi Victoria, i finally came right, had to ask our OPs director for their logic, as i was getting nowhere, i had to look on RM20101 table for any documents with payment methods of COD OR ”, and not just COD, thank you for persevering in trying to assist me, as i dont come from a financila background, so this report killed me.
Thank you for such a great source of information, i normally always troll your site to get information and understandings regarding tables, views, requests, etc
LikeLike
Hello Victoria,
We’ve recently come across a payment that did not fully apply in all the GP windows, it shows as being applied correctly everywhere except the “Sales Payment Inquiry Zoom’ window. For some reason this value is $1000.00 off from the actual payment amount. If we look at the “Sales Transaction Inquiry” window, “Receivable Transaction Inquiry” window, and even at the GL Entry it shows the correct amount.
This is a Historical payment that was made last year and I have been through all of the RM tables and cannot find anywhere that the document is off by $1000.00, would you know where the “Sales Payment Inquiry’ gets its data from so that I can update it? Thanks!
LikeLike
Bill,
I would not feel comfortable advising someone to update data like this in a table without looking at the data in all the related tables. I would recommend that you talk to your GP Partner or GP Support about this so they can take a look at your data together with you and help you fix this.
-Victoria
LikeLike
Thank you for your honesty and concern…
LikeLike
Hi Victoria
I found an odd record in RM00103 with a blank CUSTNMBR. This entry has a customer balance and various amounts in 3 of the aging periods amount columns. I cannot locate any transactions in the system for a blank customer and I believe these amounts only seem to change after a check links is performed monthly. There is also a blank customer record in RM00101 with no created date or any details. Do you have any ideas where this summary data would be coming from? We are running GP 9.0. Thanks for any help you can give.
LikeLike
Chris,
Unfortunately, it is very difficult to give you a definitive answer without looking at the rest of your data and knowing what has happened in the past. It could be that there was a customer record created in error with a blank Customer ID, that would typically cause a record in RM00103 to be created, but I would not expect it to have numbers in the aging columns. It’s also possible that something went wrong during an import or something was changed directly in the database to cause there to be transactions in the RM tables with no corresponding customer record in the RM00101 table. In that case, maybe Check Links is trying to account for this customer’s transactions with the records you are seeing. Have you checked RM and/or SOP transaction tables for records with a blank customer ID?
When I have come across this in the past, we have either left it when it was not causing any problems/errors elsewhere, or deleted all records with a blank Customer ID from all the tables where we found them. If you go this route, unless you find all the tables to delete from, Check Links may bring some of these records back.
-Victoria
LikeLike
Hi Victoria
I had been advised to use you site for GP related stuff and i first of all want to say its awesome 🙂
I do however have a query for one of my clients. They have a debtor that is a USD customer (we are in the UK) who has settled their debt in full however they will not disappear. The originating subtotal is $0.00 but the functional subtotal has $0.01 so you cant post journals or credit/debit notes to get rid of it. Any suggestions as i am not so familiar with the Receivables side of GP?
LikeLike
Hi Laura,
Thanks for the kind words. I have not run into this particular situation before, but have you tried using Write Off Documents (under Sales Routines) for this? If that does not help, I would recommend posting your question on the Dynamics GP Community Forum, where hopefully you will find someone that has run into (and fixed) the same issue.
-Victoria
LikeLike
Hi Victoria,
I am Told to create Customer Account Statement report, Can u please tell me which all are the tables, it must include customer account no. also…
LikeLike
Fazil,
I have a couple of resources on my blog that may help with this:
-Victoria
LikeLike
Hi Victoria,
We have accidentally clicked on NSF button for one of our customer’s cheques in the Receivables Posted Transactions screen. Now the Customer Payment Summary Inquiry screen shows that there is 1 NSF cheque present for this customer. This has now also reversed th original GL entries linked to this payment. Is there any way this can be reversed to not show as an NSF cheque.
LikeLike
Hi Zafar,
Similar to a void, there is no way to ‘undo’ or reverse an NSF. At this point you would need to re-enter the Cash Receipt, which will update your GL accordingly. If you’re using the Bank Rec module, I believe you will also see a negative payment from the NSF waiting to be deposited, so the re-entered Cash Receipt will need to be ‘deposited’ together with the NSF/negative payment to zero that out.
-Victoria
LikeLike
Hi Victoria, your blog is definitely THE most helpful GP site out there!
We have a problem with the direct debit module which I’m hoping you may be able to help with?
At the moment, we have a number of debtors under class ID “DD” and when we build a DD batch, we ensure that select debtor by class ID is from DD to DD, and we also ensure that the automatically apply payments/credit notes/returns boxes are ticked, and once built, the amount shown for the batch on the Select Direct Debits window is exactly the amount we expect it to be.
But, when we hit process, and the batch posts, the total batch amount (and therefore the amounts posted to the GL) changes exactly by the balance of invoices that have had a credit note applied to them automatically as part of the direct debit process, BUT the balance still applies to the payment! Difficult to explain but this might be clearer:
For instance, debtor BEN01, which is in class ID “DD” has the following documents to be picked up on by the direct debit..
Inv1 £50
Inv2 £10
Inv3 £20
CRN1 -£20 (to be applied automatically to Inv1)
Bal to be collected by DD £60
So, on the select direct debits screen, before processing/posting the batch, the amount shown would correctly be £60. BUT, when posted, and we look at the debtor enquiry for BEN01, the CRN1 would be applied to Inv1, correctly leaving a balance of £30 on Inv1, but it would show a DD payment of just £30 (for just Inv2&3) but would have £60 applied to it (i.e. more has been applied to the payment than the amount the payment is actually for!)
If I were to run the same process, but only for this one debtor (not using class ID at all), it would do the whole process 100% correctly, so I can only assume something is wrong when using the class ID, but I cannot see any settings within the class ID screens that would suggest why this is happening!
Hope this all makes sense!
Many many thanks for your help
Ben
LikeLike
Hi Ben,
Thank you for your kind words.
I do not have any direct experience with the Direct Debits module, I do not believe this is something used in US installations which is what I typically work with. However, from what you are describing, I cannot imagine what would cause this process to work differently based on how you are selecting your customers (debtors). And there is typically no process in GP that goes back to the class settings to change functionality – class is only informational at this level, simply a field used to select the appropriate records. The only thing I can think of is that the process has an issue when more that one customer is selected. Are you able to test this by selecting 2 or more customers individually, without using the class ID? Have you tried posting your question on the GP Community Forum to see if others may have some more feedback for you? The next step after that would be to talk to GP Support and see if they can help resolve this.
-Victoria
LikeLike
Hi Victoria,
We’ve got a situation where someone invoiced and posted a large amount of Sales Documents with Sales Territory “A” selected when they should have used Sales Territory “B”. I know there are far too many tables affected to try and do a simple update with SQL and we can’t use PSTL because we don’t want to change EVERY transaction from Territory “A” to Territory “B”. However, it does happen to be ALL the transcations (open and historical) for one specific Customer. Any thoughts on a creative fix that doesn’t require voiding and re-entering the transactions?
LikeLike
Brynn,
If these are posted SOP documents, voiding and re-entering would cause issues with inventory, so that is not recommended. I don’t have a ‘generic’ recommendation for this, as I believe the solution may greatly depend on details in your data as well as a good understanding of everything you’re actually using the sales territories for. I would recommend talking to your GP Partner or GP Support to see what they can come up with.
-Victoria
LikeLike
It appears that the RM00103 table contains VOIDED transaction information. If I create a payment and then VOID it, this payment appears to remain in the LASTPYDT and LPYMTAMT fields. If a payment gets entered on an account incorrectly, I don’t want that data to show as possibly valid. Is there a way to remove VOIDS from the summary information?
LikeLike
Chris,
I am not aware of any way to do this out-of-the-box, you’re talking about a customization. You bring up an interesting point, however not one that any of my customers have ever asked before. I guess because it is not very typical to have payments entered against the wrong customer. Is that something that happens a lot for you? If so, I would maybe look to see what I could do to prevent that, not just because of the reporting issue you bring up, but also because I imagine it creates a lot of work tracking this down and fixing it.
Separately, if you are relying heavily on the ‘summary’ information, consider creating a custom report for it instead that would allow you to incorporate whatever logic you want.
-Victoria
LikeLike
Victoria:
Thanks for the insight. I was hoping to prevent the customization part but it looks like I will need to do that. We have SQL views which are used to display the summary data in external applications so it won’t be too difficult.
We do occasionally have payments put on the incorrect account due to multiple accounts for spouses. The husband got the payment applied when it should have been the wife sort of thing. We also process many ACH payments and have had occasions were a payment is rejected or should not have been recorded (if we mistakenly processed ACH transasctions on a wrong date).
I’ve tried many combinations but the RM00103 table does retain the “bad” payment date and amount even if I void, remove to history or remove completely and run a reconciliation and a check links.
Thanks, Chris
LikeLike
Victoria,
Occasionally I have payments information which go missing in the AR module but are displayed in the banking module. This is strange since only some transactions within the same batch display this anomaly.
We have no network issues, no posting issues, no general GP issues have been reported. How do I go about investigating or even correcting these errors; reconcile or checklinks do not help
LikeLike
Mark,
Without looking at your data, based on what you’re describing, it sounds like maybe some of your customers are not set up to track history. That would make the record not be saved in the AR module, but still flow through to Bank Rec and GL. Often the problem with trying to confirm that this has happened is that there is no record kept of the history settings. So if they have been changed after the ‘missing’ transaction was posted, you will not be able to tell if this is what caused what you are seeing.
-Victoria
LikeLike
Hi Victoria,
Could you tell me which field is used to specify the credit card type (VISA, AMEX or MC) if the Cash Receipt Type is set to ‘Credit Card’ (2) ?
I am using the taRMCashReceiptInsert xml node class for adding customer payment info but cant figure out how to specify the credit card type for the receipt.
Thanks a lot in advance…
LikeLike
Huzefa,
By looking at the tables in our live data, it looks like this is being stored in the Freight Schedule ID field (FRTSCHID) in RM20101 and RM30101.
-Victoria
LikeLike
Hi Victoria,
If you don’t mind me asking, what is the use of the RM table RM10601?
Thank you very much.
Regards.
LikeLike
Neil,
Looking at the table name and our data, I would deduce that this table holds the tax details for any WORK or OPEN receivables transactions. In receivables, WORK means unposted and OPEN means posted but not moved to history (note that this is different from paid, so transactions can still have an OPEN status if they are paid).
-Victoria
LikeLike
Thanks so much for posting this information – I’ve only just begun my Dynamics GP adventure, and I’ve already been to this site numerous times. Being a straight up SQL developer, I have to know where the data resides, how it got there, and where it’s going next. Your resource has provided me with an abundance of information, so, again, many thanks!
LikeLike
Hi Victoria,
It’s been about a year since I’ve ping’d you for help but have been on the site a bunch, so again, thanks very much for a wonderful resource.
I have a few questions this time:
1. Where does GP store the date that payments and credit memos are applied to invoices? I’ve been unable to figure this out. I thought it was RM20201.DATE1, but this seems to be wrong sometimes. I’m looking for the date reported on the “Applied to Debits” window (where you end up when you click the Apply button on the “Cash Receipts Inquiry Zoom” window for a payment, or the “Receivables Transaction Inquiry Zoom” window for a credit memo.)
2. Can returns be used when no money is changing hands? I would prefer that we use returns instead of credit memos when we need to adjust an invoice because returns track the specific items. Our accounting person has told me that returns can only be used if a payment has been made and we are refunding the money – they can’t be used to simply reduce an unpaid invoice. (For instance, a customer orders 3 of item A and wants to return 1 of them.)
3. Can partial payments be associated with (tied to) individual items within an invoice, or only with the invoice itself? It would be helpful if, when a payment is applied, I knew which items were paid for. I am fairly confident the answer is no, but figured I’d tack this on. Also, if you have any suggestions for how to track this, let me know.
Thank you!
John
LikeLike
Hi John,
Hope this helps,
-Victoria
LikeLike
Thanks for the reply. No performance issues; I just happened to notice the duplicates & was wondering if a PTR failed or something & left the original records in the Open table.
Makes writing reports of applied transactions more challenging, though 🙂
LikeLike
Hi, Victoria! It’s been a while since we traded comments over at the old Dynamics GP board 🙂
I have a question….I recently noticed that about 10% of the records (around 55,000) in our RM20201 table are duplicated in the RM30201 table – is that indicative of a problem? I thought the records were moved from RM20201 to RM30201 during a Paid Transaction Removal?
A poster above (on 5/18/2011) hinted that the records could appear in both places if an invoice still has a balance on it, but I checked one of the records and this isn’t true – it’s fully paid.
Any ideas?
LikeLike
Hi Bud, good to hear from you again!
I believe there are a number of factors that determine whether an apply record is in RM20201, RM30201 or both, including the status of the credit transaction, the status of the debit transactions it’s applied to and the cut off dates used during paid transaction removal. There should be no harm as far as data integrity caused by transactions being in both of these tables at the same time. However, it’s possible you can see some performance issues. Is this why you are investigating this? While 10% of the records sounds a bit high to me, it’s entirely possible this is absolutely fine for your specific circumstances.
-Victoria
LikeLike
Firt, I saw a lot of useful information, Thank you for your excelllent job.
I am new to GP, and trying to develop some commission reports now. Each salesperson has pretty unique commission rate which is not also a percentage number, but also involved the products type or product number. So I want to create a commission report for each salesperson. I am searching the RM tables to get the information I need, but get confused about open transaction, open applied transaction, history transaction and history applied transaction. Can you explain a little bit of these tables and where can I find the information for me to do the commission report? Thank you!
LikeLike
Neil,
This gets pretty involved and not something that I feel I can do justice in blog comments. In my experience commissions are pretty unique in most companies and there is no one approach I could recommend that will work for everyone. Without understanding exactly how commissionable transactions are entered in your GP environment and what reporting is required, any answer you get would be partial at best and might be completely off track. I would recommend that you work with your GP partner to get a better understanding of the transaction flows and have them help you analyze your workflow and requirements to design the commissions report.
-Victoria
LikeLike
Is there a recommended process for MASS VOIDING transactions? I have a few hundred transactions that need to be voided. I ‘think’ I can update certain RM20101 fields and then run a reconcile… this appears to work. But is there another approach that is better? If the SQL method is fine, is there documentation on what specifically should be updated to complete the voiding.
LikeLike
Chris,
I am all for using SQL whenever possible to save time, but this is not something I would recommend doing directly in the tables. You will be missing a lot of related transactions, summary tables, status tables, etc., not the least of which are the related General Ledger entries. Since the related transactions and tables may vary widely based on your specific GP setup, by the time you research and test all of this, it will end up being a lot less timely and a great deal safer to manually void all the transactions.
-Victoria
LikeLike
Thanks for the advice. There were more than enough transactions to void to make this a painful process when attempting manually. So we went the Macro route. Best of both worlds. Dynamics did it’s thing, and it was over quickly.
LikeLike
Hi Victoria,
I’ve noticed starting from GP2010, user can enter a negative amount into GP Cash Receipt document. Do you know what is the purpose of having this capability? It seems to only able increase the customer’s balance and doesn’t do much. We cannot associate any document with this negative cash receipt either. Your help is much appreciated.
-Nikki
LikeLike
Nikki,
Here is the description of this new feature from the GP documentation: “You can enter a negative cash receipt for the purpose of decreasing a deposit. The negative amount flows through the system in the same way as a positive cash receipt, but is shown as a deposit with a negative amount.” I cannot recall a single time I needed this functionality in GP, but I guess there were enough requests for this to make the new feature list.
In general I think that negative cash receipts are a bad idea and do not recommend that anyone use them – for the reasons that you stated.
-Victoria
LikeLike
Thank you for the prompt response, Victoria! I really appreciate it.
LikeLike
Thanks Victoria!!!!
LikeLike
Hi Victoria
Can you tell me what table hold the customer notes? Someone here in there infinite wisdom would like to AUDIT them for all 8000 plus customers.
Thanks
Vic
LikeLike
Hi Vic,
All notes for a company are in SY03900.
-Victoria
LikeLike
Victoria,
I have an issue where a payment that was applied to an invoice is twice the amount of that invoice. Do you have any idea how this could’ve happened or how to fix it?
Thanks a bunch for making this awesome site and for your help in advance!
LikeLike
Rob,
Hard to say how it happened, most likely there was a system interruption as the payment was being applied or posted. Have you tried unapplying the payment and then reconciling the customer?
-Victoria
LikeLike
Victoria,
That worked great, I know the whole GP community really appreciates your work.
Thanks again!
-Rob
LikeLike
Hi Victoria,
I am new in GP and learn lot of things from your community. I also have one query, i want to know that where the SHIPMTDH of RM20101 come from, means from where the interface lies.
Looking for your response.
LikeLike
Hi,
Shipping Methods are stored in SY03000 for each GP company.
-Victoria
LikeLike
Thanks for your quick response.
i actually wants to know when SHIPMTHD in RM20101 were assign a value, because i have tried it from the POST of CMB Batch Entry, then POST same in ‘Receivable Batch Entry’ but it does not enter any value in ‘SHIPMTHD’ and i also don’t see any field of which link to me that this is ‘Shipping Method’.
Kindly help me out of this.
Thanks
LikeLike
When you enter a receivables transaction, the Shipping Method will default from the Customer and can be changed on each individual transaction prior to posting. However, not all transactions will have a Shipping Method – for example, there is no Shipping Method for payments (RMDTYPAL = 9).
-Victoria
LikeLike
Thanks Victoria,
you solve my problem and guide me in right direction. I hear your reply frequency and helping habits but you deserve more than that.
I will be there soon if i got some problem.
Thanks Dear
LikeLike
Hi, does anyone know if it’s possible in the case of an NSF payment to determine the original document the payment was applied to? It appears once a payment is marked NSF the record in RM20201/RM30301 table gets deleted.
Thanks!
LikeLike
Unless you customize something to keep track of this, GP does not keep a record of what the payment was applied to previously.
-Victoria
LikeLike
Victoria,
I have to fix an incorrectly entered Invoice date. I have tracked in SOP30200 the following: DOCDATE INVODATE DISCDATE DUEDATE Tax_Date. I am looking through RM tables now. Is there a document on the proper clean up for this?
Thanks,
Allen
LikeLike
Allen,
I strongly recommend against doing this in the tables. Just of the top of my head, I can think of multiple SOP, RM, IV, GL, MC, CM and TX tables that could be involved. And not just transaction tables, but also summary tables. And if you have customizations or 3rd party products, they need to be considered, too. The proper way to fix this is to enter a return on the ‘wrong’ date and re-enter an invoice on the right date. If the issue is just a GL issue, consider a GL adjustment.
-Victoria
LikeLike
Hello Victoria,
I want to get information regarding notes which I entered while creating Credit Memo entries since I want to display those description inside the notes for report printing purpose. Pls advise me from which table i will get proper infformation
LikeLike
Hi Arun,
The notes are stored in the SY03900 table. You can link to them from the transactions by NOTEINDX.
-Victoria
LikeLike
Hello Victoria,
This site is very Use Full having lot of information regarding different Issue in Great Plains. I have learnt lof of stuff about RM regarding how to move OPEN RM documents to HIST if document is not fully applied means have some remaining amount. I have done that using write off process that bring the remaining amount to Zero then use paid transaciton removal. But currently I am stuck into situation where I want to move OPEN documents to HIST but want to keep the customer balance remain same, which was before write off and paid removal. I have many documents types like Sales / Invoices, Debit Memo, Credit Memo and Payments with remaining amounts exist , that I like to move from OPEN to HIST but I can not because it would change customer account balance, I have looked about this but could not find any solution yet, Any help ? I am very thankful and appreciate your help and precious time.
Thanks
Naveed (naveedsadiq@hotmail.com)
LikeLike
Hi Naveed,
You will not be able to do what you are asking in GP, as only fully applied documents can be moved to history. This also may present an accounting issue, as technically a customer’s balance is only made up of OPEN documents. You cannot have historical documents be part of the balance. The only thing I can think of is to create a new document for the difference and keep that open, while moving the documents you want to history.
-Victoria
LikeLike
Hi Victoria,
Many thanks to your response to my earlier question, well I have follow your instructions as well as consult MS GP consultant to close OPEN document, the way we found is to use write off and paid removal first and then create new documents in the year with status OPEN including same amount which was exist in other documents. This is solution works for Sales Invoice, Debit Memo and Payments but not for credit memo following solution provided
The Credit memos that have an outstanding amount will need to be handled differently as there is not a writer-off process. The only option is to create a debit memo for the total amount that needs to be written-off for the customer. You will then need to apply this to the Credit Memos that need to be cleared. The next step will be to create a credit memo in the new year for the amount that was written off.
But it did not work because in Manually Apply sales document window, debit not cannot apply to credit note
Any idea how I can fix this issue for the credit note documents. Really appreciate your response.
Many Thanks
Naveed
LikeLike
Naveed,
Once you post the debit memos you can go to Transactions | Sales | Apply Sales Documents and Apply the Credit Memos to the Debit Memos there.
-Victoria
LikeLike
Victoria,
I’m using your Receivable SQL script for All Posted Transactions. How can I join in the Customer Name from RM00101?
Thanks,
Mark
LikeLike
Mark,
Are you using the one with the friendly names? I just added the customer name to that one for you.
-Victoria
LikeLike
Worked perfectly! I also added the aging bucket to mine. Now I have a quick way to balance AR.
Thanks again,
Mark
LikeLike
Hi Victoria,
What a valuable support you offer, I really thank you so much.
I have a small question if you don’t mind,
I need the table that bring the sales orders status, whether the order transfered or not transfered to sales invoice.
Thank you Again.
Regards,
Sameh Adel
LikeLike
Sameh,
If the SOP order is in SOP30200 (instead of SOP10100), then it has been fully transferred, but it does not necessarily mean it was transferred to an invoice…it could be transferred to a Back Order. Also, because you can have partial transfers, there is not just one status code. You can check the quantity fields on the SOP line items in SOP30300 and SOP10200 to see the details of what has happened. You can also use the ORIGTYPE and ORIGNUMB fields in SOP10100 and SOP30200 to see where transactions were transferred from.
-Victoria
LikeLike
Dear Victoria,
You are a great person, thank you.
Regards,,,
Sameh Adel
LikeLike
Victoria,
Excellent blog and very helpful.
We found that if an invoice has multiple payments applied to it but still has a balance, the applied amounts exist in both the RM20201 & RM30201 tables at the same time. We could clear these out by running the Paid Tran Removal however we would like to keep a month or two of payments in case of NSFs. So in order to have a complete data base of applied payments (used for calculating commissions) We need all the transactions in RM30201 but only the NON-duplicates from RM20201. My script writing is beginner-limited so I’m enlisting your help for a script. Thanks for all your help.
Mark
LikeLike
Hi Mark,
The short answer is that I already have a script posted to show apply information in AR: https://victoriayudin.com/2010/02/15/sql-view-with-ar-apply-detail/. If that’s not what you’re looking for, please write back with more details.
The slightly longer answer is that typically you can avoid duplicates in your results by either using a UNION (instead of UNION ALL) or a DISTINCT, depending on where exactly the duplication happens. UNION vs UNION ALL is a bit tricky with some of the tables in GP, and has to be evaluated carefully for each report.
Hope that helps.
-Victoria
LikeLike
The script worked perfectly. Thanks!
LikeLike
Hi Victoria,
Trust you’re doing well. I have a question about cutomomer’s Last Statement Balance. From GP Customer Payment Summary Inquiry, there’s the last statement balance at the bottom of the window. I could find that balance from the RM00103 table. However, is there a way I can get the precise list for ALL the invoices/documents that adds up to that balance?
Your help is much appreciated.
Best Regards,
Nikki
LikeLike
Nikki,
I do not use these myself, as I have customer statements created in Crystal Reports, however, you might want to take a look at tables RM30701 and RM30702 to see if they will help with what you are looking for.
-Victoria
LikeLike
Thank you so much for your help.
LikeLike
Hi Victoria,
I am trying to get a table in RM which is having the Customer Balance in Foreign currency
LikeLike
I forgot to tell you thanks in advance.
Regards,,,
Sameh Adel
LikeLike
Hi Sameh,
I do not believe customer balances are stored in anything other than the Functional Currency in GP.
-Victoria
LikeLike
Hi Victoria,
And thank you for the reply, but we are recording the transactions using an originating currency which is US$ and then use the exchange rate to convert to kd in the GL. So, my question is is there a table for the originating currency which by i can come to know the outstanding balance of the customers.
Regards,,,
Sameh Adel
LikeLike
Sameh,
That would be the RM00103 table.
-Victoria
LikeLike
Thank you Victoria, I need to know also where I can find the Exchange rate related to each transaction.
Appreciate your continuous support.
Regards,,,
Smahe Adel
LikeLike
Sameh,
I would look in the MC020102 (Multicurrency Receivables Transactions) table for that.
-Victoria
LikeLike
Thank you Victoria,
I tried this table but the problem that it is repeating the balances many time for the same customer.
Is there a way to remove this duplication?
Regards,,,
Sameh Adel
LikeLike
Sameh,
I don’t understand what you mean. The MC020102 table has transaction information – it will not be showing any customer balances.
-Victoria
LikeLike
See, I will tell you exactly what I am doing
I am preparing a reporting technique which links all required data from GP directly thru SQL connection. I am almost done with all what the report may require, except that Receivables part. We have receivables with KD currency (our local Currency) and receivables with $ Currency, some of them pay in advance, so what I need here is to identify the customers’ remaining balances, or in other words, what they have paid and still laying in thier account. I was able to get the data easily from table RM00103, but the problem that it is showing only in Local Currency, while we maintain the balances with $ customers in $, at this point, there would be no problem if and only if the exchange rate dosen’t change. But the exchange rate flactuates and will show different value for balances in KD at the current xchng rate, so we cannot depend on KD balance, other wise we will perform this reporting manually.
This is the picture of the situation, Can you help in this.
Regards,
Sameh Adel
LikeLike
Sameh,
In that case, you cannot use the customer balances table and must instead get the transaction information from RM20101 and MC020102 and sum them up as needed.
-Victoria
LikeLike
Hi Victoria,
Thank you for your site! It is very informative.
I have a question regarding multicurrency cash receipts. When entering a multicurrency cash receipt the Apply button is greyed out. Microsoft states that we should post the invoice and then go to the Apply Sales Documents window to apply it, but this creates additional work for our users. Do you know why the cash application is disabled at the time of entry and are there any workarounds for this issue?
Thank you!
Peter
LikeLike
Hi Peter,
I do not have any special insight as to why this is the way it is and I agree that this is counter-intuitive and creates additional work and have seen other complaints about this in the past. The only thing I can recommend is to submit changing this as a suggestion for future GP versions.
-Victoria
LikeLike
The reason I have been given in the past – it needs to determine the equivalent amount functional currency, and can only do so once the exchange rate is known.
Once the cash receipt is posted the functional amount is ‘locked’ and when it is applied to the appropriate invoice in the originating currency id, there can be a Realized Loss or Gain due to exchange rate differences.
Due to this highlihood of RZLOSS/RZGAIN is why a cash receipt in non functional currency has to be posted before it can be applied.
LikeLike
Robert,
That’s the reason I have been given as well, except all that means to me is that two separate calculations have to be done ‘behind the scenes’. I am not sure why that necessarily translates to the user having to perform two separate steps. 🙂
-Victoria
LikeLike
Hi Victoria
I was wondering what goes into calculating the field RM00104.SMRYSALS. I have a view that subtracts out the returns for a report I am writing , but my CFO wants to still try and tie things out to the GL so I need to know how the values that get updated in that field. So are they like from the sub total on an invoice or the total after shipping?
Thanks
LikeLike
Hi Vic,
The only way to tie to the GL is to start with the GL and determine where each transaction to the sales account(s) came from. Otherwise, you have to make two reports (GL and Sales) and find where the differences are.
To answer your specific question, the ‘sales total’ tracked and summarized for sales transactions is determined by the settings on the Receivables Setup Options window (GP | Tools | Setup | Sales | Receivables | Options). If all the checkboxes at the bottom of that screen are checked, then all of those amounts are being added into the SMRYSALS amount. Since no history is tracked for changes to these checkboxes, if you were to change them today, there is no way to tell what made up the total for transactions posted prior to today. So again, not a good idea to use this to tie to the GL as they may be incomplete, include too much and there is no way to determine what exactly made them up and when.
-Victoria
LikeLike
Hi Victoria,
I have a scenario to apply a sales return against a cash reciept.Exmaple sales return of 1000 and reciept of 500. I know this is a very rare scenario. But my client needs a solution for this. Let me know if you have any idea to do this.
Thanks!
LikeLike
NB,
There is no way to do this in GP. You cannot apply a credit transaction to another credit transaction.
-Victoria
LikeLike
Does this same rule hold for Debit transactions?
For instance, in a conversion we did for a client from MAS to GP10, we were left with what amounts to several cash receipts of negative amounts that were applied to invoices in MAS. It has been suggested that we create Debit Memos in GP for the negative receipts and apply the Debit Memo to the Sales Invoice to increase its balance.
Can a debit memo be applied to a sales invoice?
LikeLike
Yes, the same rule holds. You cannot apply a debit memo to an invoice. You can only apply a credit transaction (payment, return, credit memo) to a debit transaction. No other combination is possible.
Another basic principle is that the current transaction amount cannot be greater than the original transaction amount. So to increase the amount of a transaction you could (a) void it and re-enter it with the new amount or (b) have two or more transactions that make up the total.
-Victoria
LikeLike
Thanks for confirming my fears. 😀
Your blog has been a great reference for those occasions when I venture outside of the Project Accounting module. Thanks for sharing!
LikeLike
Hello Victoria.. love the new look of your site!
We are using Dynamics GP Professional version 9.00.0281 and SQL Server Management Studio version 9.00.3042.00.
My users have been entering third party shipping accounts numbers in User defined field 2 on the Customer Internet Window. I’ve found (with your help) email address table (SY01200) but it doesn’t contain the userdef2 table.
In the RM00101 table there is userdef2 but it’s not the table that has the third party shipping information.
Can you tell me where I can find the information?
Thank you in advance,
Cindy
LikeLike
Hi Cindy,
Glad you like the new look!
Just to make sure we are talking about the same thing…I am not familiar with a GP window called ‘Customer Internet’. There is ‘Customer Maintenance’, ‘Customer Address Maintenance’, and ‘Internet Information’ – are you using one of these, or something else?
-Victoria
LikeLike
Thank you for replying.
To enter the information the steps they take:
Customer Maintenance Window → Address ID → click on i → Internet Information → User Defined 2 field.
LikeLike
Thanks Cindy,
That is the Internet Information window. Since you can change the labels for the fields on this window, it does not necessarily follow that your User Defined 2 will be the same as my User Defined 2. However, if that is the field right above the Additional Information box at the bottom, then it is INET8 in table SY01200.
-Victoria
LikeLike
Thank you Victoria! That is the field with the missing account numbers.
I can see why you keep winning awards!!
Sincerely,
Cindy
LikeLike
Hi Victoria
I got a big problem migrating from GP 10 to 2010 with RMCashWork,
do you know why in GP2010 in RMCashWork window the receipt field (DOCNUMBR) only accepts 17 characters and in GP10 the same field accepts 20 characters?
in both cases table RM10201 is char(21)
Thanks a lot
LikeLike
Hi FAC,
I have not seen this come up as an issue before, I usually recommend smaller numbers, not larger ones. But I do see what you mean, I can manually type 20 characters into the Receipt field in GP 10.0 (and GP 9.0 for that matter), but it stops at 17 in GP 2010.
This is one I would recommend talking to GP support about to see what they say.
-Victoria
LikeLike
Victoria,
Wonderful Blog/Website, I use all these references on a daily basis. Thank you for all the hard work that you put into this for us.
Now on to a question…
We are currently using Open Item Statements for our customers; however we want to move to Balance Forward Statements. I have found that there is no way to do this. Is this true? If not where might I look to find how to change the statement successfully? I have tried changing the balance type field in the RM00101 field (in a test company of course), but with no real change to the statement. Only a balance forward amount and nothing else (no invoices or payments).
Any help is much appreciated!
Thanks!
Topher
LikeLike
Hi Topher,
Thanks for the kind words. I’ve never had someone want to go to Balance Forward…usually it’s the other way around. 🙂 Have you tried running Reconcile after changing the balance type for the customer?
-Victoria
LikeLike
I have not tried that. I will run that in the next day or so and let you know what I find. I guess there is a first for everything!
-Topher
LikeLike
Would you be so kind as to summarize the difference between RM20101 (Open Transactions) and RM20201 (Open Transactions Apply)?
Does “_Apply” simply include more details? My accountant is asking me to write him a query that shows all open deposits (not applied/reconciled) on customers’ records.
LikeLike
Paul,
The RM20101 table holds all posted Receivables transactions that have not been paid or have not been moved to history.
The RM20201 table holds apply information for Receivables transactions that have not been moved to history.
For example, let’s say you have 2 invoices and 1 credit memo:
$400 from the credit memo is applied to invoice 1001 and the remaining $50 is applied to invoice 1040.
RM20101 will have 3 records for these – one for each transaction. RM20201 will have 2 records for these – one for each apply record. While related, completely different sets of data.
To find records that have nothing applied, you simply need to look in the RM20101 table, no need for the RM20201. My Unapplied Transactions view may help with this.
Hope this helps,
-Victoria
LikeLike
Your Unapplied Transactions View worked like a charm; thanks!
LikeLike
Hi Victoria,
Trust you’re doing well and congrats on the 7th MVP Title!! You’re very well deserved it for all your valuable contributions to the Dynamics Community.
I have a question that hope you could help me. We have created Payment Cash Receipt document within Dynamics GP. However, the document cannot be found (seemed to be deleted) from GP. Is there a way to track or trace to see when it was deleted or who deleted the document from any of the tables in GP?
Thank you so much in advanced!
Best Regards,
Nikki
LikeLike
Hi Nikki,
Thanks! If this was a saved transaction that got deleted prior to posting, there will no record of it kept in GP unless you (ahead of time) set up something to specifically track this type of activity. GP does not do this out of the box, but there is an Audit Trails module and ISV products that can help with this.
-Victoria
LikeLike
Oh, I see. Thank you so much!
LikeLike
Hi Victoria,
Thanks so much for a wonderful blog! I regularly visit it for reference.
Quick question….I had located the Customer Pricing Maintenance SQL table at one time, but have since lost it. Do you know where that table is located (customers to price sheet linkage).
Thanks again!
Chad
LikeLike
Hi Chad,
GP does not really have ‘customer specific’ pricing…unless you are creating a new price level for each customer and setting it up that way? If so, my Customer Pricing view will show this.
Hope that helps,
-Victoria
LikeLike
Hi Victoria
Congrats on the new book. I wil lbe sure to pick up a copy of “The Bible” as soon as i can. 😉
I am writing a report for sales that uses the RM00104 table. I need to get the sum of sales based on 5 diffrent date ranges in the same query or view. Like this;
Salesperiod = 10 and Year is 2009 and Salesperiod is 10 and Year is 2010 also
Salesperiod = between 1 and 10 and Year is 2009 and Salesperiod is between 1 and 10 and Year is 2010 also
Salesperiod = between 1- 12 and Year is 2009
I also need the RM00101 table to get sales territory and Salesperson and customer name info.
I have this done in Access (My old favorite!) by combining 5 diffrent querys but i would like to have it in SQL server so I can make an ssrs report out of it.
Do you know of any way this can be done?
thanks
LikeLike
Thanks Vic,
I am guessing what you’ve listed is just an example, since those overlap…so you’re just looking for an example of how to do this in SQL? If so, you can do something like this:
SELECT SLPRSNID Salesperson,
SUM(SMRYSALS) TotalSales
FROM RM00104 S
INNER JOIN RM00101 C
ON S.CUSTNMBR = C.CUSTNMBR
WHERE (PERIODID = 10 and YEAR1 = 2009)
or (PERIODID between 1 and 10 and YEAR1 = 2010)
or (PERIODID between 1 and 10 and YEAR1 = 2011)
and (HISTTYPE = 0)
GROUP BY SLPRSNID
You can just change or add additional period or year restrictions following this example.
-Victoria
LikeLike
Thanks
How can I build this table with a query? I need this type of data but for invoices only (SOPTYPE = 3).
Thanks,
LikeLike
Ricky,
Sorry, not sure what you’re trying to do…build what table? With what data?
-Victoria
LikeLike
Sorry, I would like to build a view that gets the same data as RM00104 but for invoices only (soptype = 3) RM00104 gets credit memos, debit memos and such.
thanks
LikeLike
Ricky,
You can create your own summary from the details. You could start with this view for all AR transactions and add some restrictions or groupings to it, based on what you need.
-Victoria
LikeLike
I’m new to SQL. How can I create a summary by period (month) and year, similar to table RM00104.
If you point me to similar exp I may be able to work it out.
thanks,
LikeLike
Here is something similar: https://victoriayudin.com/2012/01/09/sql-view-for-customer-yearly-totals-in-dynamics-gp/
LikeLike
I think I can figure it out from here.
thanks,
LikeLike
Steven,
I believe you can use this code to get what you need.
-Victoria
LikeLike
Thanks for you help. I was able to get the data I needed.
LikeLike
hi, I doing a report on balances, but I have a question where I can take the beginning balance of a customer which would be the field.
Greetings
LikeLike
Jorge,
Unless I am misunderstanding what you’re looking for, GP does not store a “beginning balance” for customers.
-Victoria
LikeLike
Hello Victoria,
I have a big mystery on my hands and I thought I’d run it by the expert (you!!). I have records in my RM20201 table showing (among other things) Returns that were applied to Invoices. I am assuming that that table gets populated when an end user goes into ‘Apply Sales Documents’ and processes the apply there.
However, the end users have sworn to me that they never go into ‘Apply Sales Docs’. So I’m just wondering how that RM20201 table is getting populated? Is there some other auto-apply utility or tool in GP that I’m missing?
Thanks!!
LikeLike
Hi Kat,
Depending on where/how the return or credit was entered, it’s possible that the apply process was done at the same time, by clicking the Apply button. So it’s not always necessary for users to actively navigate to the Apply Sales Documents window. It’s also possible to apply credits and returns at the same time as entering Cash Receipts. Finally, if you have any 3rd party apps or customizations (or even imports), they may be doing this. If you are on GP 10.0 or higher, there should be a time stamp on the apply record that may help you figure out when and who created it.
-Victoria
LikeLike
Thanks for your response Victoria.
When I go into Sales Order Trans and try to process a test Return, I do not see the ‘Apply’ button anywhere. I know that this exists in AP, but on the AR side of things, it does not seem to exist. Not even when you go to ‘Additional > Create Return’ in SOP. It appears that the only way to apply it is if you go into the ‘Apply Sales Docs’ screen and do it there. Which the end users swear they don’t use. So I’m still a bit stumped. We aren’t using any third party products and of course the RM apply to table doesn’t contain user_who_posted or anything like that. I’ll have to check out the other thing you said, that they may be applying when they enter the cash receipts.
Thanks a lot!!
LikeLike
Hi Kat,
You are right, there is no Apply button in SOP, but in RM there is – so it you are entering a credit memo or a return from the Transaction Entry window (instead of the Sales Transaction Entry window), there will be an Apply button there.
For what it’s worth, my money is on a user going into the Apply Sales Documents window and either not remembering or not admitting to it. 🙂
-Victoria
LikeLike
Hi Vic,
How do i increase the description field size for receivables transaction entry in gp 2010.
Transaction>Sales>Transaction Entry.
regards,
Charles
LikeLike
Charles,
This is not supported and I would strongly recommend not doing this. If you need to store more data for transactions, consider using the notes or an Extender window.
-Victoria
LikeLike
Charles, my previous answer was based on the assumption that you wanted to actually increase the existing field in the database. You also have additional options, as you saw in this post on the GP Customer forum: https://community.dynamics.com/product/gp/f/32/p/40285/72507.aspx#72507.
-Victoria
LikeLike
Hi victoria
any idea where the email address for the salespeople is located?
thanks
Vic
LikeLike
Hi Vic,
All the data on the Internet Information window is in the SY01200 file. For salespeople the Master_Type will be SLP.
-Victoria
LikeLike
Hi Victoria,
I’ve been using your website for 1-2 years now, and it’s been extraordinarily helpful – thank you so much!
I learned today that our accounting person had created a credit memo to offset a transaction which had been paid by EFT, resulting in our having the money, but no payment record. Two questions – first, do you know whether there’s a good alternative to handling payments like this which occur outside of GP? And second, do you know which table/column contains the “Description” field for receivables transactions? (The field I’m talking about is on the Receivables Transaction Inquiry Zoom field – the one displayed when you click on the Document Number link in the Receivables Transaction Inquiry window.) If we have to stick with using credit memos, I can at least pull this field, in which our accounting person records a short note of explanation for each CM.
John
LikeLike
Hi John,
Thanks for the kind words, glad my blog is helpful!
I have seen GP users do this before and it always confuses me, why would getting an EFT be any different from other ways customers send you money? If a customer sends you a check and you go to the bank and deposit it, the end result is the same – your cash is increased and your Accounts Receivables are decreased. The only difference with an EFT (or wire) is that the money is automatically in your account, you do not need to go to the bank to deposit the check.
All that said, I would recommend entering these as Receivables Cash Receipts of type Cash. If you are using the Bank Rec module, this will result in a receipt that will need to be deposited, just like checks received from customers do. Yes, the money is already in the bank, but you just have to tell GP that with the deposit step. If you are not using Bank Rec, nothing else needs to be done, the Cash Receipt will simply debit Cash and credit AR and show a customer payment on the customer account. (And if you are using Bank Rec, I imagine the Credit Memos are throwing it off…not sure if that is why you need the additional reporting?)
As to your last question, descriptions for all posted RM transactions will be in either RM20101 or RM30101, depending on whether the transactions has been move to history or not.
Hope that helps.
-Victoria
LikeLike
Victoria,
Thanks so much for that information – I just went over this with our accounting person, and he agreed that your way is better. He explained that we get so few EFTs that he tends to forget how best to process them. BTW, we do use the Bank Rec module, but he entered a corresponding IAJ, so it didn’t throw off the reconciliation.
Thanks again!
John
LikeLike
Hi Victoria!
One of our users reported an error:
You can’t edit this document because a multicurrency record is missing.
The error occured while the user retrieves a specific document from the Receivables Transaction Entry Window.
What would have caused this error? And how are we going to fix this?
Thanks a lot!
LikeLike
Chris,
I have not come across this error before… If you try the same thing on a different computer, do you get the same error? If so, I would try running Check Links on the sales series and see if that fixes the issue.
-Victoria
LikeLike
Wow! I know this goes way back….but I have this error too. Do you know how you resolved it?
LikeLike
Hi Victoria,
Trust you’re doing well. We ran into an issue with Cash Receipt for a Canadian customer. We created a Cash Receipt for credit card payment; however, once the Cash Receipt is posted, the unapplied amount shows up as $0.00 even though the Cash Receipt has not been applied yet to any invoices. Have you seen this occur before and do you know what might have caused it?
Many Thanks & Best Regards,
Nikki
LikeLike
Hi Nikki,
Doing great, thanks! Hope you are, also. 🙂
Sounds like something may have gone wrong with this transaction. Have you tried running Sales Reconcile and/or Check Links?
-Victoria
LikeLike
Hi Victoria,
Thanks for the kind response. Yes, we’ve tried running Check Links but no lucks and it happens most of the time for Canadian customers. We’re wondering if there’re any settings we might have missed for multicurrency.
Best Regards,
Nikki
LikeLike
Hi Nikki,
Reconcile would actually be my first choice for this, not Check Links. As far as whether something is missing in setup, it’s really impossible to say without checking every single setting. However, I would think that if there was actually setup missing, you would get errors. More likely there is setup that is slightly incorrect, which may actually be more difficult to track down. 😦
-Victoria
LikeLike
hi victoria,
where table can I find the posted Receivables Management batch comment?
LikeLike
Romel,
They are in the RM30502 (Batch History) table.
-Victoria
LikeLike
thanks a lot…that really helps me to finalize my report….
LikeLike
Victoria,
As an avid user of your site for finding solutions for GP challenges, I again am seeking your expertise. I’m looking to determine if there is a way to determine whether invoice is paid or not by using a particular field in one of the tables. What I’m finding is that one payment or check can cover more than one invoice, so there’s not a one to one match. Is there a field that gets populated when a payment is applied indicating that the invoice has been paid?
Regards,
Raul
LikeLike
Raul,
Why not use the Current Transaction Amount? If it is zero, then the invoice is paid.
-Victoria
LikeLike
Victoria,
Thank you! I am pulling from the RM20101 and looks like I’m getting the data I need for my report. Is the CURTRXAM field the same as Amount Remaining while looking at the Receivables Transaction Inquiry window in GP?
Regards,
Raul
LikeLike
Raul,
Yep, CURTRXAM (Current Transaction Amount) is the same as the Amount Remaining field. In SmartList this shows up as Current Trx Amount.
-Victoria
LikeLike
Victoria,
The end user was happy with the data that was provided to them.
Thank you once again for all of your assistance and knowledge sharing.
Raul
LikeLike
Hello Victoria,
Do you know if there is a field in RM30201 that indicates when a transaction was moved to History. I would like to be able to query for new items added, since the previous time I checked. I am not seeing something like CREATEDDT or LastModified or ItWasMovedToHistoryOnDate :-).
Thanks in advance.
— Rick
LikeLike
Rick,
To my knowledge this information is not stored anywhere in GP.
-Victoria
LikeLike
Victoria,
I have reconciled my bank statement in GP9.
My adjusted Bank Balance and
My adjusted Book Balance equal
My GL is low about 258K from the reconciled Balance.
How can I bring my GL in line with my Reconciled bank Balance?
Also, in the Bank inquiry window my current checkbook balance and my current cash accoutn balance are different as well.
Hohw do I correct this?
LikeLike
Paula,
Not sure why this is posted under RM Tables, but in general, if you are satisfied that your Bank Rec balances are correct and your GL does not match this, then your GL is incorrect and you need to enter an adjustment entry in the GL.
My 2 cents would be that 258k is a large enough difference that I would want to understand why it’s there and do some further investigation. Typically there are 3 reasons why the GL would be off from the Bank Rec:
A tool that might help you reconcile your difference is The Closer from Reporting-Central: http://www.reporting-central.com/.
As to your last question – you have already confirmed that your reconciled Bank Rec balance does not match your GL balance – that’s all the inquiry window is showing you, this should not be a surprise, right? 🙂
-Victoria
LikeLike
True. I think I just needed confirmation from someone else. The number just sent me crazy!!! Thank you Victoria.
LikeLike
Victoria,
I am working to clean up unencrypted credit card numbers that have gotten into our GP database. I am running GP9 and have fixed the inputs to where they are now only sending the last 4 instead of the entire card number. I Have identified RM20101, RM30101, SOP10103, SOP30201 that contain the numbers, but I am wondering if there is anywhere else that I may be missing.
Thank you very much for your help.
LikeLike
Hi Todd,
Part of the difficulty in answering this is that it may depend on how exactly credit card numbers were entered into GP. It sounds like you’ve already identified the fields these are entered into, which is most of the effort. To be thorough I would check all the tables that contain those fields. I just published code to find all the tables with a column name: https://victoriayudin.com/2010/04/23/how-to-find-all-sql-tables-with-a-column-name/.
-Victoria
LikeLike
Hi Victoria,
Once again, thank you so much for sharing all of this information and knowledge about GP for the community. These are greatly helpful to us!
I have a quick question regarding SalesPerson & TerritoryID in GP. Is there any relationship between SalesPersonID & TerritoryID? (ie: 1:1, 1:N) For every customer record in GP, it’s assigned with a SalesPersonID and a TerritoryID. Is there any reason whereas CustomerA has SalesPersonA and TerritoryA but CustomerB has SalesPersonA but TerritoryB? Thanks so much in advance.
LikeLike
Hi Nikki,
The only relationship I can think of is that Territory ID is a required field when setting up a Salesperson ID. I am not sure, but I think the only thing this is used for is to possibly to summarize the totals on the Sales Territory Maintenance window. I don’t know any companies who actually use those numbers for reporting. Typically reporting requirements are much more complicated than this allows for.
So…no, there is no reason why you can’t set up Customer A with Salesperson A and Territory A and Customer B with Salesperson A, but Territory B.
-Victoria
LikeLike
Thanks so much, Victoria!!
Best,
Nikki
LikeLike
Victoria,
Thank you worked like a champ! The only piece left that I need is the project number, what table can I join to in order to get this?
Thanks,
Raul
LikeLike
Raul,
Gad that worked. I have not done any reporting on Project tables, so I am not sure where that information is stored, sorry. (And if you’re not using GP’s Project Accounting, then you would need to find out from the vendor of the product you’re using.)
-Victoria
LikeLike
Victoria,
What about linking to an invoice, can that be done and if so what what table and field to join on?
– Raul
LikeLike
Raul,
You mean the invoice the credit memo was applied to? I have published a view for the AR Apply information that should help you with that: https://victoriayudin.com/2010/02/15/sql-view-with-ar-apply-detail/.
-Victoria
LikeLike
Victoria,
I’ve been looking for a way to gather all credit memo transactions in our system. I see RMDTYPAL 7 = Credit Memo and also see DISTTYPE 19 = Credit Memo. Tables RM00101, RM20201 and RM30101 appear to have a lot of the information I need but how can find out what GL account these transactions interface to and what field (if any) can I use to join in order to tie all this information together? Thanks in advance for your knowledge sharing,
Raul
LikeLike
Raul,
Why not start with my published view for GL distributions for AR transactions? https://victoriayudin.com/2010/02/10/sql-view-with-all-gl-distributions-for-ar-transactions/
If you need to, you can add a restriction to only get credit memos back (RMDTYPAL = 7).
-Victoria
LikeLike
Victoria,
Thank you worked like a champ! The only piece left that I need is the project number, what table can I join to in order to get this?
Thanks,
Raul
LikeLike
Victoria,
Have a mystery on my hands, someone or some how 90% of sales customers taxschid was changed. What is fastest way to change back, through GP or update to underlying tables on back end and how many tables would it involve, RM00101, RM00102,….
Thanks,
Raul
LikeLike
Raul,
This can happen with changes to classes that are rolled down to all the customers in the class. If you are using customer classes you can make whatever changes you need to that way and roll them down to customers. Otherwise, I believe RM00101 and RM00102 are the tables that would require changes.
-Victoria
LikeLike