AR Statement in Crystal

I am currently working on producing the A/R statement in Crystal 9.0.  We are 
running GP 8.0.  Crystal doesn't seem to be able to pull in all the 
customers.  I am using table RM00101 and pulling custnmbr and custname.  The 
inactive field is set to no and adrscode to 'STATE'.  When I run this query 
using smartlist from within GP, I get a count of 823 customers.  When I run 
this in Crystal, I only get 251 customers returned.  Any ideas?
0
Claman (1)
4/24/2006 7:34:02 PM
greatplains 29623 articles. 6 followers. Follow

7 Replies
552 Views

Similar Articles

[PageSpeed] 26

In Crystal, are you restricting on RM00101.dbo.INACTIVE with the value
"No"?  If so, that should be 0 (0 = Active, 1 = Inactive).

0
michaelj2 (180)
4/24/2006 7:53:53 PM
Yes I have INACTIVE = 0.  (If I try it the other way INACTIVE = 1, I get 253 
records with Smartlist returning 336 records.)

"MichaelJ" wrote:

> In Crystal, are you restricting on RM00101.dbo.INACTIVE with the value
> "No"?  If so, that should be 0 (0 = Active, 1 = Inactive).
> 
> 
0
SueClaman (4)
4/24/2006 8:15:03 PM
Do you have any inner joins to other tables? 
-- 
Charles Allen, MVP



"Sue Claman" wrote:

> Yes I have INACTIVE = 0.  (If I try it the other way INACTIVE = 1, I get 253 
> records with Smartlist returning 336 records.)
> 
> "MichaelJ" wrote:
> 
> > In Crystal, are you restricting on RM00101.dbo.INACTIVE with the value
> > "No"?  If so, that should be 0 (0 = Active, 1 = Inactive).
> > 
> > 
0
callen (2505)
4/25/2006 4:09:01 AM
Sue,
Why adrscode to STATE?
Please post the SQL statement used by Crystal.  That should clear things up 
a bit

HS
-- 
www.DynExtra.com
A resource for the Microsoft Dynamics Community
Featuring FAQs, File Exchange and more
Current member count: 21

--------------------------------------------

Share your knowledge.  Add your favorite questions and answers


Help add questions to this site!  We want Your input.
"Sue Claman" <Sue Claman@discussions.microsoft.com> wrote in message 
news:A2979BAA-B47A-4B78-8763-773AB17FB594@microsoft.com...
>I am currently working on producing the A/R statement in Crystal 9.0.  We 
>are
> running GP 8.0.  Crystal doesn't seem to be able to pull in all the
> customers.  I am using table RM00101 and pulling custnmbr and custname. 
> The
> inactive field is set to no and adrscode to 'STATE'.  When I run this 
> query
> using smartlist from within GP, I get a count of 823 customers.  When I 
> run
> this in Crystal, I only get 251 customers returned.  Any ideas? 


0
HSalim (1270)
4/25/2006 5:16:19 AM
Sue,

Check the joins of the table, you might need a left join from RM00101 to the 
other table

EMS


"HSalim[MVP]" wrote:

> Sue,
> Why adrscode to STATE?
> Please post the SQL statement used by Crystal.  That should clear things up 
> a bit
> 
> HS
> -- 
> www.DynExtra.com
> A resource for the Microsoft Dynamics Community
> Featuring FAQs, File Exchange and more
> Current member count: 21
> 
> --------------------------------------------
> 
> Share your knowledge.  Add your favorite questions and answers
> 
> 
> Help add questions to this site!  We want Your input.
> "Sue Claman" <Sue Claman@discussions.microsoft.com> wrote in message 
> news:A2979BAA-B47A-4B78-8763-773AB17FB594@microsoft.com...
> >I am currently working on producing the A/R statement in Crystal 9.0.  We 
> >are
> > running GP 8.0.  Crystal doesn't seem to be able to pull in all the
> > customers.  I am using table RM00101 and pulling custnmbr and custname. 
> > The
> > inactive field is set to no and adrscode to 'STATE'.  When I run this 
> > query
> > using smartlist from within GP, I get a count of 823 customers.  When I 
> > run
> > this in Crystal, I only get 251 customers returned.  Any ideas? 
> 
> 
> 
0
Ems (26)
4/25/2006 9:28:11 AM
The reason I have the address code equal to state is because we use 'MAIN' 
for the invoice billing address and 'STATE' for the statement address.  I was 
trying to narrow down the problem of why all customers weren't being returned 
and created a new report with just RM00101 and found my problem.  Here is the 
SQL statement from my existing customer statement.
SELECT  RM20101 . DOCNUMBR ,  RM20101 . ORTRXAMT ,  RM00101 . CUSTNAME ,  
RM00201 . CLASDSCR ,  RM20101 . CURTRXAM ,  RM20101 . DOCDATE ,  RM20101 . 
RMDTYPAL ,  RM00101 . CUSTNMBR ,  RM00102 . ADDRESS1 ,  RM00102 . ADDRESS2 ,  
RM00102 . CITY ,  RM00102 . STATE ,  RM00102 . ZIP ,  RM40401 . DOCABREV ,  
RM00102 . ADRSCODE 
 FROM   {oj ((( CDS . dbo . RM00201   RM00201  INNER JOIN  CDS . dbo . 
RM00101   RM00101  ON  RM00201 . CLASSID = RM00101 . CUSTCLAS ) LEFT OUTER 
JOIN  CDS . dbo . RM20101   RM20101  ON  RM00101 . CUSTNMBR = RM20101 . 
CUSTNMBR ) LEFT OUTER JOIN  CDS . dbo . RM00102   RM00102  ON ( RM00101 . 
CUSTNMBR = RM00102 . CUSTNMBR ) AND ( RM00101 . ADRSCODE = RM00102 . ADRSCODE 
)) INNER JOIN  CDS . dbo . RM40401   RM40401  ON  RM20101 . RMDTYPAL = 
RM40401 . RMDTYPAL }
 WHERE   RM20101 . CURTRXAM <>0 AND  RM00102 . ADRSCODE ='STATE'
 ORDER BY  RM00101 . CUSTNMBR ,  RM20101 . DOCDATE ,  RM20101 . DOCNUMBR 

Sue

"ems" wrote:

> Sue,
> 
> Check the joins of the table, you might need a left join from RM00101 to the 
> other table
> 
> EMS
> 
> 
> "HSalim[MVP]" wrote:
> 
> > Sue,
> > Why adrscode to STATE?
> > Please post the SQL statement used by Crystal.  That should clear things up 
> > a bit
> > 
> > HS
> > -- 
> > www.DynExtra.com
> > A resource for the Microsoft Dynamics Community
> > Featuring FAQs, File Exchange and more
> > Current member count: 21
> > 
> > --------------------------------------------
> > 
> > Share your knowledge.  Add your favorite questions and answers
> > 
> > 
> > Help add questions to this site!  We want Your input.
> > "Sue Claman" <Sue Claman@discussions.microsoft.com> wrote in message 
> > news:A2979BAA-B47A-4B78-8763-773AB17FB594@microsoft.com...
> > >I am currently working on producing the A/R statement in Crystal 9.0.  We 
> > >are
> > > running GP 8.0.  Crystal doesn't seem to be able to pull in all the
> > > customers.  I am using table RM00101 and pulling custnmbr and custname. 
> > > The
> > > inactive field is set to no and adrscode to 'STATE'.  When I run this 
> > > query
> > > using smartlist from within GP, I get a count of 823 customers.  When I 
> > > run
> > > this in Crystal, I only get 251 customers returned.  Any ideas? 
> > 
> > 
> > 
0
SueClaman (4)
4/25/2006 1:51:01 PM
Sue,

If you want records returned for customers where the Statement To
Address is equal to State you need to join the RM00101 with the RM00102
on RM00101.STADDRCD = RM00102.ADRSCODE.

0
michaelj2 (180)
4/25/2006 2:56:22 PM
Reply:

Similar Artilces:

Help with incorrect information from statement download
I installed money 05 and when I selected my bank money goes and contacts my bank and successfully gets my accounts information. The balance is correct, what is wrong is that it list deposits on the same side as my withdrawals. If I try to correct this manually it messes up my balances. I know the information from my bank is correct because on different computer I have been using money 03 please advise. Joshua Wickard wrote: > I installed money 05 and when I selected my bank money > goes and contacts my bank and successfully gets my > accounts information. The balance i...

Crystal Reports
Has anyone been able to set up an SQL View in Crystal so that one report can contain data from several companies? I need to create a Crystal Report that will give me the AP Open Invoices for all of our companies. I am on Ver. 8 of GP and Ver. 9 of Crystal. Thanks, -GK Dear, You will need to use something look like the following view, replace Company1 with the first company and Company2 with the second company and so on: SELECT Company1.dbo.GL20000.ACTINDX, SUM(Company1.dbo.GL20000.CRDTAMNT) AS Credit, SUM(Company1.dbo.GL20000.DEBITAMT) AS Debit, SUM(Company1.dbo.GL20000.ORC...

Crystal Reports + CRM
Hello, Does CRM 1.2 support crystal reports 8.5 only for customizing the reports or can I use Crystal reports 9 with the add on that comes with the CRM Pack? Thanks and Regards -- Fakhruddin Zavery Sr. Systems Analyst / Programmer CATS Tanzania Ltd. NIC Life House 4th Floor P. O. Box 2569, Dar es Salaam, Tanzania Tel. +255-22 2137915, 2112631 Fax +255-22 2113033, 2112916 Mob +255 744 277452 website: http://www.catsgroup.com -------------------------------------------------------- The Irony of Life is that, no one gets out of it Alive ---------------------------------------------------...

Reconcile to GL
I used "Reconcile to GL" to reconcile GL & AR during month end, a difference between GL & AR was resulted in the Excel spreadsheet generated. Then, I try to generate and compare the individual GL TB and AR HATB, the amount was tied with no variance. After checked, I found out that the "Total RM Balance" shown in the reconcile report is different with the balance amount shown in the AR aging reports. And the differenciate sum is exactly the same amount shown in Excel spreadsheet. I would like to seek advise here on why there is a difference between t...

GP tables in Crystal 9.0
Hello: In the Set Data Source Location window of Crystal 9.0, you map the GP tables of one ODBC connection to another. In order to see the GP tables of the ODBC connection that you want to map to, you right-click on the connection, click the "Options" button, and then you make sure that only "Tables" and "System Tables" are checked. But, when I conducted these steps yesterday, the GP tables did not appear. Has anyone else faced this issue? If so, what is the remedy? Thanks! childofthe1980s Oh, could the issue be lack of admin privileges on the workst...

Deleting with an IF Statement
Hello Everyone, I am trying to write an IF statement that will delete certain rows i they match the false criteria. Ex. IF cells A7:A12>=500, if true keep those rows, if false delete thos rows. I am looking to delete the entire row if the statement is false. Ca someone please help? Thanks -- Message posted from http://www.ExcelForum.com How about data>filter>autofilter>custom>go from there. Record a macro if desired. -- Don Guillett SalesAid Software donaldb@281.com "garungaha1 >" <<garungaha1.18rtos@excelforum-nospam.com> wrote in message news:g...

Crystal 9.0 and CRM 1.2
Does anyone know if 1.2 supports Crstal Reports 9.0? Yes 1.2 supports crystal 9.0 <anonymous@discussions.microsoft.com> wrote in message news:03e101c3bf57$beeb0880$a101280a@phx.gbl... > Does anyone know if 1.2 supports Crstal Reports 9.0? ...

Ruling out combinations without if statements?
Is there a way to rule out combinations with out having to use an if then block. My form has 5 different text boxes. I want the user to enter the information they wish to search by and have which ever text boxes that are not null have that information passed to an SQL statement to search by. The problem is, if I do an if then block for txtdate txtPIN txtSN txtTicketNumber txtStaff there's 125 possible combination of null vs not null within those 5 text boxes. Is there any way to make an SQL statement that does something like SELECT table.* FROM table WHERE field LIKE txtDate IF t...

IF statement that yields a value based on a formula
Help! If cell B12 >= 0, then I need the answer to be calculated based on the formual "B12 * .40". If cell B12 < 0, then the answer is zero. How do I do this? -- barbara h IF(B12>=0,B12*0.4,IF(B12,0,0)) Click yes if helped -- Greatly appreciated Eva "barbara h" wrote: > Help! If cell B12 >= 0, then I need the answer to be calculated based on the > formual "B12 * .40". If cell B12 < 0, then the answer is zero. > > How do I do this? > -- > barbara h Hi, Try this =MAX(0,B12*0.4) -- Mike ...

How to delete phantom statement?
In M2007 I have a downloaded OFX statement for an investment account that won't go away. It says there are 2 transactions, but if I click on them, there are none. If I try to delete the downloaded statement, there is no error, but the statement does not go away. I've tried repairing the file - no errors, but statement is still there. I removed online services for that FI, restarted Money, and set up online services again but its still there. I also had a few new transactions come in, and the new statement downloaded fine, but after it was processed the old statement is still hangi...

Can't print Monthly Statements
Hello When we try to print current or past monthly statements, we are getting the following error message: "The year you've selected for an open-year column is invalid for Statement - Catering" Help?! ...

Need help with SELECT statement. 04-19-10
Hi, I need some help with the select statement given the business rules and desired result below. Thank you so much for your help in advance. Please see the business rules below. IF OBJECT_ID('Tempdb.dbo.#Office', 'u') IS NOT NULL DROP TABLE #Office CREATE TABLE #Office ( CompanyId INT NULL, OfficeId INT NULL, CorpOffice BIT NULL, DBAName VARCHAR(15) NULL ) go INSERT INTO #Office VALUES (1323, 1302, 0, 'Test1') INSERT INTO #Office VALUES (1323, 1303, 0, 'Test2') INSERT INTO #Office VALUES (1323, 1306, 0, 'Te...

claudios@seincomp.com.ar
claudios@seincomp.com.ar ...

Cannot download statements
Not sure what is causing this: I download statements from several financial institutions. Until recently this worked with very few problems. Over the last week or so, none of them have successfully downloaded. I know this is not much to go on, but any idea? I am currently using Money Plus Deluxe Trial Version (and no it has not expired yet !) My O/S is Windows XP SP3 Thanks In microsoft.public.money, Brian wrote: >Not sure what is causing this: > >I download statements from several financial institutions. Until recently >this worked with very few problems. Over the la...

Sudden Loss of Direct Statements
My citi credit card statement used to synch just pefectly with money 2003 dx on my Win XP machine. Suddenly, it stopped. I've checked with the bank, and the account was locked because of "too many incorrect login" attempts. After unlocking, I changed the password and tried loging in via the web. That works. Using same information in money yields nothing except lockouts. I've verified the login information several times. It just simply doesn't seem to work from money. (I've got 9 other direct synching accounts from this file that still work just fine...) Anyone ev...

Questions on format of Select statement
SELECT tblCalls.ColTime, tblCalls.colProblem, tblCalls.colResolution from tblCalls, in dbCallLogs WHERE tblCalls.colResolution from tblCalls, in dbCallLogs where tblCalls.ColTime >= #12/1/2006# <= #3/2/2007# ORDER BY tblCalls.ColTime DESC; I want to know if this is right? I notice in some queries the table name before the column name is missing? Also when do you have to put in the database name? Is it the same in Access as SQL Server? I want the most recent first. I want those records betweeb 12.1.06 and 3.01.07. thanks, On Wed, 21 Mar 2007 13:30:07 -0700, Janis <Janis...

Not able to verify the database in Crystal
I'm trying to get custom fields in my report but when I try to verify the database I get the error "The database DLL 'crdb_mscrm.dll' could not be loaded". I see that several people have asked this question before but there doesn't seem to be an answer to it. I still am able to log onto the APS and create reports with the standard fields. Any help is appreciated. If you have already installed the Crystal Reports Enhancements for CRM, I would try reinstalling it. It is a quick install and might do the trick. Good luck Matt Wittemann http://icu-mscrm.blogspot...

Crystal Reports 05-12-04
I'm having an issue where every few weeks the default reports need to be republished. I've got the Crystal Reports toolkit and am able to get the reports working again by republishing them. It's just aggrivating! Has anyone else seen this issue? >-----Original Message----- >I'm having an issue where every few weeks the default >reports need to be republished. I've got the Crystal >Reports toolkit and am able to get the reports working >again by republishing them. It's just aggrivating! Has >anyone else seen this issue? >. > It seems th...

Shorcuts or Macro for Creating If Else Statements
I create if else statements often. But I find it tedious to have to type them and redefine them all the time for different workbooks. For example, let's say you want this type of if else condition for calculating price to earnings: a1=price; b1=earnings =if ( iserror( a1/b1),"n/a", if (or( a1/b1<0, a1/b1>100),"nmf", a1/b1)) Is there a way of shortening this? What I mean is, of course I can copy this down the column and calculate a bunch of P/Es for a bunch of companies. I'm trying to see if there is a macro or short cut that will help you create if el...

Statements not downloading, how to get help?
I am using money 2006 and I have not been able to get my banking accounts to download new transactions for several weeks, where can I go for help? Should I call my bank? Is there a money support email or phone number? Does anyone have any suggestions. I should note that other accounts from my brokerage company are downloading correctly, its just my bank accounts that are not updating correctly. Thanks, Sam In microsoft.public.money, Sam wrote: > I am using money 2006 and I have not been able to get my banking >accounts to download new transactions for several weeks, where can I go...

If Statement 01-13-10
Need help with this formula? Need to calculate a formula for CELL A based upon the following IF CELL B = STOCK and CELL C >= 1.0, <25.0 THAN CELL D * 4 tss wrote: > Need help with this formula? > Need to calculate a formula for CELL A based upon the following > IF CELL B = STOCK and CELL C >= 1.0, <25.0 THAN CELL D * 4 =IF(AND(B1="STOCK",C1>=1,C1<25),D1*4,"**No idea what you want here**") IF( AND( logical1, logical2, logical3 ), value_if_true, **value_if_false** ) Put this in A1: =3DIF(AND(B1=3D"Stock",C1>=3D1,C1<...

Account Statement
Do you know of any way to add the journaled receipt to the Account Statement ideally in a smaller version and say four to a page? Is this even possible? ...

(Follow on from previous post) Using IF statements
I have an alternating set of three columns, labelled (in row 5) Beg, Mid and End. They represent the beginning, middle and end of each month. Row 24 contains values for these periods. I would like to calculate the average for all Beg columns, for all Mid columns and for all End columns. In a previous post I got as far as putting following formulae in three different cells: =AVERAGE(IF('Cash flow'!5:5="Beg",'Cash flow'!24:24,"")) =AVERAGE(IF('Cash flow'!5:5="Mid",'Cash flow'!24:24,"")) =AVERAGE(IF('Cash flow'!5:5=&...

Stop Downloading Statements in Money 2004
I don't want to download statements in Money 2004. I've tried to find somewhere to stop this, but everytime I stop the statement downloads, I stop online bill pay. Is it possible to seperate the two? ...

CRM / Crystal Install error
Just wondering if anyone is having the same install problem I am and if there is a solution. I am installing CRM on it's own server and using an existing SQL server. CRM installs to the default instance of SQL which is good but half-way through the install when it tries to setup Crystal Reports I get the error "Failed to install CRM Server Unknown error 80040e14" When I check the SQL logs I find that the Crystal APS service is trying to log into the SQL server on a Anonymous account? SQL log reports "Logon for NT Authority/Anonymous, access denied". Everything ...