Link Tables in Report Writer

Hi everyone,
Im working with Report Writer in GP10 I read David articles and they are 
interesting but my question is:
for exemple I want to add the "expiration date" to the "POP Receivings 
Posting journal" report let's assume I found the table containing the 
"expiration date" 
how do I get to it when creating table relationships Im new with GP and Im 
having difficulties what tables to link to get the final table.
I really tried everything to figure it out but I didn't succeed I hope I was 
clear explaning this.
thank  you guys in advance

0
Saad (30)
4/19/2009 5:53:01 AM
greatplains 29623 articles. 6 followers. Follow

3 Replies
967 Views

Similar Articles

[PageSpeed] 52

It sounds like you need a bit of database theory.  Information contained in 
tables that can be related must have a common field.  For example, the PO 
order header table contains a vendor number field that can be used to link 
the table to the vendor table.  Tables are related by these link fields.  (a 
link field must be identical in type, for example, you cannot link a number 
to a string field)

When you attempt to relate a new table to a report, you must link the table 
to an existing table in the report and link them by common fields.  Careful, 
you can get into trouble if multiple records in the new table match the 
original table.  For example, if you link the PO history header table to a 
new PO by the vendor number and you have made multiple purchases from that 
vendor, you will result in many matches, possibly not what you want.

If this helps, you might want to look up an old book called Database Theory 
by CW Date.  He is the father of databases and relational theory.  It helps a 
lot in understanding the linking of tables.

Everything else is mechanics.  The papers you described talk about the 
Report Writer mechanicals very well.
-- 
Richard L. Whaley
Author / Consultant / MVP 2006-2008
Documentation for Software Users

Get our Free Tips and Tricks Newsletter and check out our books at 
http://www.AccoladePublications.com



"Saad" wrote:

> Hi everyone,
> Im working with Report Writer in GP10 I read David articles and they are 
> interesting but my question is:
> for exemple I want to add the "expiration date" to the "POP Receivings 
> Posting journal" report let's assume I found the table containing the 
> "expiration date" 
> how do I get to it when creating table relationships Im new with GP and Im 
> having difficulties what tables to link to get the final table.
> I really tried everything to figure it out but I didn't succeed I hope I was 
> clear explaning this.
> thank  you guys in advance
> 
0
info4071 (3004)
4/19/2009 12:49:01 PM
Hi Richard,
First of all I want to thank you for the reply 
you are right about key fields but that's not what Im looking for
take this example:
I want to add "Item number" to "GL Distribution Breakdown Register" Report
in the report table relationships window I double click on the first report 
table relations which is "IV Distribution Temp" there you will find 2 related 
tables and if you check them they both have common fields with the first 
table 
so how you decide which one to choose on how you base your choice that's 
what im trying to understand.
im sorry for the long email 
and thank you very match


"Richard Whaley -- MVP 2006-2008" wrote:

> It sounds like you need a bit of database theory.  Information contained in 
> tables that can be related must have a common field.  For example, the PO 
> order header table contains a vendor number field that can be used to link 
> the table to the vendor table.  Tables are related by these link fields.  (a 
> link field must be identical in type, for example, you cannot link a number 
> to a string field)
> 
> When you attempt to relate a new table to a report, you must link the table 
> to an existing table in the report and link them by common fields.  Careful, 
> you can get into trouble if multiple records in the new table match the 
> original table.  For example, if you link the PO history header table to a 
> new PO by the vendor number and you have made multiple purchases from that 
> vendor, you will result in many matches, possibly not what you want.
> 
> If this helps, you might want to look up an old book called Database Theory 
> by CW Date.  He is the father of databases and relational theory.  It helps a 
> lot in understanding the linking of tables.
> 
> Everything else is mechanics.  The papers you described talk about the 
> Report Writer mechanicals very well.
> -- 
> Richard L. Whaley
> Author / Consultant / MVP 2006-2008
> Documentation for Software Users
> 
> Get our Free Tips and Tricks Newsletter and check out our books at 
> http://www.AccoladePublications.com
> 
> 
> 
> "Saad" wrote:
> 
> > Hi everyone,
> > Im working with Report Writer in GP10 I read David articles and they are 
> > interesting but my question is:
> > for exemple I want to add the "expiration date" to the "POP Receivings 
> > Posting journal" report let's assume I found the table containing the 
> > "expiration date" 
> > how do I get to it when creating table relationships Im new with GP and Im 
> > having difficulties what tables to link to get the final table.
> > I really tried everything to figure it out but I didn't succeed I hope I was 
> > clear explaning this.
> > thank  you guys in advance
> > 
0
Saad (30)
4/19/2009 3:06:02 PM
Please Richard a little help here
I appreciate it

"Saad" wrote:

> Hi Richard,
> First of all I want to thank you for the reply 
> you are right about key fields but that's not what Im looking for
> take this example:
> I want to add "Item number" to "GL Distribution Breakdown Register" Report
> in the report table relationships window I double click on the first report 
> table relations which is "IV Distribution Temp" there you will find 2 related 
> tables and if you check them they both have common fields with the first 
> table 
> so how you decide which one to choose on how you base your choice that's 
> what im trying to understand.
> im sorry for the long email 
> and thank you very match
> 
> 
> "Richard Whaley -- MVP 2006-2008" wrote:
> 
> > It sounds like you need a bit of database theory.  Information contained in 
> > tables that can be related must have a common field.  For example, the PO 
> > order header table contains a vendor number field that can be used to link 
> > the table to the vendor table.  Tables are related by these link fields.  (a 
> > link field must be identical in type, for example, you cannot link a number 
> > to a string field)
> > 
> > When you attempt to relate a new table to a report, you must link the table 
> > to an existing table in the report and link them by common fields.  Careful, 
> > you can get into trouble if multiple records in the new table match the 
> > original table.  For example, if you link the PO history header table to a 
> > new PO by the vendor number and you have made multiple purchases from that 
> > vendor, you will result in many matches, possibly not what you want.
> > 
> > If this helps, you might want to look up an old book called Database Theory 
> > by CW Date.  He is the father of databases and relational theory.  It helps a 
> > lot in understanding the linking of tables.
> > 
> > Everything else is mechanics.  The papers you described talk about the 
> > Report Writer mechanicals very well.
> > -- 
> > Richard L. Whaley
> > Author / Consultant / MVP 2006-2008
> > Documentation for Software Users
> > 
> > Get our Free Tips and Tricks Newsletter and check out our books at 
> > http://www.AccoladePublications.com
> > 
> > 
> > 
> > "Saad" wrote:
> > 
> > > Hi everyone,
> > > Im working with Report Writer in GP10 I read David articles and they are 
> > > interesting but my question is:
> > > for exemple I want to add the "expiration date" to the "POP Receivings 
> > > Posting journal" report let's assume I found the table containing the 
> > > "expiration date" 
> > > how do I get to it when creating table relationships Im new with GP and Im 
> > > having difficulties what tables to link to get the final table.
> > > I really tried everything to figure it out but I didn't succeed I hope I was 
> > > clear explaning this.
> > > thank  you guys in advance
> > > 
0
Saad (30)
4/20/2009 1:37:02 PM
Reply:

Similar Artilces:

Changing text in X and Z reports
Hi, I want to change a text in X and Z reports. The original text is "Paid on Account." I want it to say "On Account." How can I do the change? I already found the XML report template but don't know where to go from there. Thank you in advance. Win open file in Frontpage or text editor. Do "find" and type in "paid on account" to get you to the section quickly. or, just roll down until you find this section =========================== Section: Grand Total Out =========================== --> <!--BOOKMARK--> <ROW...

Mails are being send without colors and table structure destroyed
Dear All, I am using Microsoft Outlook 2003. My other office products are of Office 2000. Word 2003 is not there on my PC and therefore the option to use it is disabled. Now how to use Microsoft Word 2000 as the mail editor? I am facing some strange problems with Outlook 2003. I composed a outage notice in Word 200 with table structure and colouring. I pasted the same in mail and send it. The mail send was in a format where table structure completely destroyed, all the words are not well aligned and the e-mail is not in colors. I able to see it structured in the send folder but it is not gett...

OT: demoralized tables
Kirk Kuykendall has a good overview on Common Table Expressions http://www.sqlservercentral.com/articles/CTE/62291/ but his conclusion contains a "new db term" (at least I had never seen it before)... "As you can see CTEs can be very useful. I have found them particularly handy when cleaning up demoralized tables. Like imported spread sheets." I assume it was on purpose and couldn't resist passing it on. We've all been there... gary I'm wondering if demoralized tables might be an undiscovered cause of file corruption. I would think that one table wit...

disable reports daterange dialog to show up twice
Hi, I created a report with daterange parameters. Everythings works fine but when I want to print some report, the report asks the user again for the date parameters. Having installed the CRM update CRM1.2-KB834913-Server.exe which should repair this bug, I noticed no change. The reports always asks twice for the parameters. Is there any secret for this mystery... Greetings Thomas Ott are all these parameters in a single report or are you using sub reports as well? -- John O'Donnell Microsoft CRM MVP http://www.microsoft.com/BusinessSolutions/Community/CRMFaqLanding.aspx "Th...

9.0 Table Changes
Will be updating to V9, am looking for a list of the tables that changed and the documentation says it is on V9 cd 2, under the SDK But I don't see anything on the CD so do I have to install v9, and the SDK before I can get a list of the table names? -- Doug You need to install the SDK "Doug" wrote: > Will be updating to V9, am looking for a list of the tables that changed and > the documentation says it is on V9 cd 2, under the SDK > > But I don't see anything on the CD so do I have to install v9, and the SDK > before I can get a list of the tabl...

How to print Z report twice?
Last night I run the Z report but the paper got stuck. When I tried to run it again it shows 0 sales. My trainer told me that the Z report can only be run once. Is it true? If not how can i retrieve the information again and run the report for the 2nd time? You can find it in the journal viewer just like a receipt and reprint it from there. <oez4u@yahoo.com> wrote in message news:1154589409.592371.315400@i42g2000cwa.googlegroups.com... > Last night I run the Z report but the paper got stuck. When I tried to > run it again it shows 0 sales. My trainer told me that the Z report...

Looking for Easiest Way to Create Report
We need to create a report from our accounting program which contains about 200 .db files. I have determined that the required information is contained in only 3 of those files. Out of those 3 files I need about 30 fields of data. The end result is that those 30 fields of data have to be placed into an existing excel spreadsheet in specific columns which has 97 total columns. For example one db file contains data which has to be placed into column c of the excel spreadsheet. another db file has data which has to be placed into column z of that same spreadsheet and so on Th...

Third Party Dictionary Reports
Hi All, We are creating a custom table that has a one to one relationship with Sales Transaction Amounts Work, so it has the required SOPTYPE, SOPNUMBE, LNITMSEQ and CMPTSEQ columns to create the join. I need to add this table to the Blank Invoice, so that I can print the new custom fields. What should we do to ensure that these fields are available? Thanks in advance for your assistance Regards HS You have to use Dexterity to accomplish this goal. A separate dictionary has to be created. Based on working with a developer, it's a difficult task. I think using Crystal is a much bett...

Custom Reports Using MS Access?
Ran across a thread where Glen Adams suggested using MS Access to create custom reports instead of Crystal Reports. But Glenn never said where to point Access to obtain the needed information to create the reports. Glenn, if you are watching can you give us some more info on how to use Access to create a report? I'm not Glen... I think you would use ODBC... to connect to the RMS database. Then use Access to connect via ODBC... Marc Wagner www.gmroii.net "Dan" <anonymous@discussions.microsoft.com> wrote in message news:0df801c46ea7$d79b00b0$a601280a@phx.gbl... &g...

Linking text within Excel-- help!
I have a mailing list that I will be importing into Excel, and am trying to link the names on the list to invoices for those people on the list. Can I do this in Excel by using links? The Excel help feature only seems to describe links with figures (numbers), not text. e.g. Mr. Bill Jones, 123 Main Street, Middletown, OK 40404 (each word in its own cell in the mailing list worksheet) ....would link to a worksheet that had Bill Jones' name and address but also indicted that he purchased a $100 product on June 15 and his bill has been paid in full. My questions: 1. Each of the 300 or...

Viewing toolbar while in report
I've pretty much got this danged database ready for users, but am having a problem with the custom print toolbar I created not showing up in reports. I designated the custom print toolbar in each report property box (those that use it), but the toolbar doesn't appear. Am I doing something wrong? Linda ...

Crystal Reports XI password storage
In earlier versions of Crystal Reports, the user id and password to access the GP tables were able to be stored in the connection properties so that they did not have to be entered when refreshing a report. I can store the used id, but I have not been able to find a way to store the password in Crystal Reports XI. Does anyone know if this is still possible and how it can be done? Thanks. How Early? I have been using Crystal since V4 and don't remember that. However, You can set up a trusted group and use that trusted relationship when creating your reports. In this manner, y...

Links won't open internet sites
Links sent to me in email show up as blue and the curser turns into the hand when over it but clicking on the link does nothing, it won't open a site on the internet. What do I have to adjust? Have turned off firewall and problem continues. Help! Thanks... Using Outlook Express 6 in Windows XP Home - Internet Secuity installed but all turned off (other than antivirus) ...

Saving data in Access table with VB ?
I have a form with a button and if you click the button, a list of invoices are generated and saved in the table 'Invoice'. Problem is, the data isnt saved :( Here's my code: Private Sub Knop0_Click() Dim Invoicenr As Long Dim Invoicedate As Date stdocname = "Invoice" DoCmd.OpenTable stdocname, acViewNormal, acAd Invoicenr = 111111 Invoicedate = Now DoCmd.Save acTable, stdocname End Sub What am I doing wrong ? Answered in microsoft.public.access "Bauhaus" <niemandhier@pandora.be> wrote in message news:7Exii.2418$yf6.1885@biebel.telenet-ops.be...

Save Link Problem
Hi I run Windows 2000 & Excell 2000 with service pack 3. I have an autoshape button that I have added a hyperlink to which opens a html file on a server. When I add the hyperlink and press the autoshape it takes me to the file ok. I can do this as many times as I like and it works ok. The problem arises after I save the spreadsheet. As soon as I save the spreadsheet the hyperlink no longer works. I get the error message "The address of this site is not valid, Check the address and try again". To add some more mystery to this, I emailed the file to a friend and he inserte...

Filter Report by Form
I'm using the following code from the wonderful Allen Browne which works perfectly in an either or scenario (either by Start/End Date(s) OR filtering by client name; but I don't know what syntax to use to filter by Start/End date(s) AND anything typed in the client name field. I'm sure it's easy but I can't get my head around it. I appreciate any and all assistance!! Private Sub cmdPreview_Click() 'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it working. 'Purpose: Filter a report to a date r...

Sub Report Repeating
With On My Main Report I have a Sup Report, The Main Reports Shows The Project Number. Project Title and Enquiry: Enquiry Could Be Anything From "Supply Only Ductwork" The Sub-Report Shows All The Drawings That Are Needed For That Enquiry, The Sub-Report Is Set to a Contineuos Type, The Problem Arise's If Ive Seletced The Say 2 or more Companys For that Enquiry, What Is Happening The Sub Report Is Duplicating The Drawing List For How Many Companys Ive Set The Enquiry To. Is The Away I Can Just Print the One Set of Drawings for that Enquiry Records, and Not Repeat the Whole S...

Custom Report 03-19-10
There is no way for me to run a report on Buydown Pricing, is there anyone who has a report for this option? If not, can someone write a custom report for it, and how much might something like this run me? I would like it to do something like buydown quantity greater/less/equal and same for buydown price... please contact me at nagarra @ yahoo . com or respond here with your contact info! -- Thank You Vince :) ...

Button on Form to Run Report based on Query with Parameters
Hi Guys. I am busy working on a stock control database (and job control). When I have a Form Open, it shows several fields, including JobID (Which is the PK for that job) (frm_Invoicing) I also have a Query that when Run, it prompts for the JobIDNo. (qry_CustJobsInvStock) I have a report based on that query. (rpt_CustJobsInvStock) I have added a button onto the form (onclick -> DoCmd.OpenReport) to open and automatically print the report, But I just cannot figure out how to get it to get my JobID from the form, and to Automatically add it as a Parameter for the que...

entryfield linked to selectionlist
Hallo, I created a form with a selectionlist of all belgian cities. Next to this field I created an entryfield where the zip-codes should appear What I want is this entryfield to show the zipcode when the up and downarrow of the selectionfield is clicked In the "after update" propertie of the selectionlist I wrote: Dim db As Database Dim tb As Recordset Dim f As Form Set db = CurrentDb() Set tb = db.OpenRecordset("postnummers") Set f = Forms![leerkrachten] tb.MoveFirst Do Until tb.EOF If tb!gte = f!gem_lk Then f!PN_lk = tb!PN Exit Do End If tb.MoveNext Loop tb!gte citi...

Crystal Reports 9.2.3 and CRM
Hi, I'd like to know if CRM does work fine with the Crystal Reports Pro Version 9.2.3. I know the recommanded version is 9.2.2 but was just wondering if somebody successfully worked with 9.2.3/CRM Thanks Eric I have used a version line 9.2.4 so I would assume yes it will work. Only way to be sure is to try it. -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "Eric" <NOSPAMerich742001@yahoo.fr> wrote in message news:%23GB4wV9SFHA.1040@TK2MSFTNGP10.phx.gbl... > Hi, > > I'd like to know if CRM does work fine with the Crystal Reports Pro Ve...

Moving CRM
Hi All. I am in the process of relocating CRM 1.2 to a new SBS 2003 Server. If I have not created any custom reports with Crytal Reports, is it really necessary for me to back that up and then restore it on the new server? Since I have only been using the default reports, shouldn't everything be fine??? THanks! Jake, You shouldn't need to migrate the reports. But, are you using the redeployment tool for this? If so, then there may be some reasons that this introduces that make it a requirement. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------...

Send Report to Hard Drive
Hi, I need your help. I create a report with picture and send it to HTML. I open the report like "report1.html" but I got the detailed information without a picture in it. I'm wondering .... Is there a possible way to send a report to become "report1.jpg"? I know if I send the report using Access Snapviewer and it works. Your help would be much appreciated. Thanks Everyone please note that Aaron Kem.pf is attempting to impersonate one of our regular posters again. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBui...

Scrolling Tables list
When I use the scroll bar to move up and down the list of tables or queries etc, the list continues to vacillate after I stop sliding the bar; making it difficult to find the item I want to select. This seems to be worse with my new computer. Are there settings I should adjust. "Crop scout" <Crop scout@discussions.microsoft.com> kirjoitti viestiss´┐Ż:771C65DD-5D6E-47EF-B987-C797A680FB59@microsoft.com... > When I use the scroll bar to move up and down the list of tables or > queries > etc, the list continues to vacillate after I stop sliding the bar; makin...

Denial of report access
I would like to deny access to specific reports thoughout CRM for a group of users. I have already created Security Group-Domain Local at the Active Directory and assigned some users. Afterwards I opened SQL Server Reporting Services, selected the report I didn't the group to access and assigned the new group with a new role (only task:View folders). At the end I deleted all the other groups except BUILTIN\Administrators and tried to enter as the acess denied user. Unfortunatelly it didn't work. What else should I need to do? Thank you in advance! ...