Correct Links via Crystal

Ive gone thru this 100 times and im struggling - it seems easy enough but 
this is what i have: (i think ive asked this before not sure)

want to create a report showing Customer and Inventory - then all forecast 
figures and all actual figures (not concerned about specific dates yet)

No issues linking thru RM to SCO tables to retrieve forecast info
Probs start when trying to link in sop30200 (custno) and sop30300 (item and 
qty)
Im using inner joins for these ie field = field , but they seem to play 
havoc with the forecats figures.
Ill be working on this until resolved
Thanks muchly
Theo :)
Has anyone linked these sco tables to these sop tables to provide some advice.

0
theo (92)
7/20/2005 1:57:01 AM
greatplains 29623 articles. 6 followers. Follow

5 Replies
513 Views

Similar Articles

[PageSpeed] 52

Hi Theo,

I have not worked with the SCO tables, but have worked a great deal with the
RM and SOP tables.  I would be happy to learn more about the SCO tables to
try to help out if I can.  Can you give a little more detail about what you
are trying to accomplish (which SCO tables, etc?).  For example, when you
say that linking to the SOP tables plays havoc with the forecast figures,
which forecast tables and what fields are you referring to?  Do you have a
sample report that you can post, or can you include the current SQL query
that your report is based on?

Regards,

- Mickie

"Theo" <Theo@discussions.microsoft.com> wrote in message
news:4A1094E0-B098-424A-913C-C442B88863BF@microsoft.com...
> Ive gone thru this 100 times and im struggling - it seems easy enough but
> this is what i have: (i think ive asked this before not sure)
>
> want to create a report showing Customer and Inventory - then all forecast
> figures and all actual figures (not concerned about specific dates yet)
>
> No issues linking thru RM to SCO tables to retrieve forecast info
> Probs start when trying to link in sop30200 (custno) and sop30300 (item
and
> qty)
> Im using inner joins for these ie field = field , but they seem to play
> havoc with the forecats figures.
> Ill be working on this until resolved
> Thanks muchly
> Theo :)
> Has anyone linked these sco tables to these sop tables to provide some
advice.
>


0
mstamm (2)
7/22/2005 7:59:02 PM
Mickie

We worked thru this - with much help from HS - who is a brill guy btw.

The issue was we need to create an inner left join and standard join on 2 
tables to get the data we wanted - Crystal didnt like this much.
So what we ended up doing was summarizing the data in crystal, exporting the 
data, then creating a new crystal report which looks at GP and the summarized 
data.  Think we are gonna have to do something programmy leng term - but this 
works ok.
Im not sure this newsgrp takes attachments - but we are trying to link the 
Cust Master, thru to Mftg forecasts for their items then provide sales for 
any/all items that have sales, incl items that dont have sales.
Because not all cust appear in SOP - we needed a left outer join but then we 
needed to link sop header and sop trx - this is the bit that crystal doesnt 
like much.
Im not sure how else to explain this...
Theo :)
0
theo (92)
7/25/2005 2:58:03 AM
Have you looked at having the joins done on the SQL Server, and
creating a single or smaller set of simpler tables to run the Crystal
Report against?

There are a lot of toys/tools that you can use on the SQL server side
(such as views) that may make life easier.  Additionally, if you have
access to an SQL-guru, you can run the Crystal Report against a
"command" (basically, an SQL statement, full of outer/inner joins,
etc.)   The downside is that these are a little less flexible when it
comes to changes (you would need to edit the SQL Command or
edit/recreate the view on the SQL server to add/remove fields, join in
additional tables, etc.), but they can be a lot faster.

-J

0
7/27/2005 8:00:13 PM
Hi Theo,

I'd definitely like to help with this but I just need to get a little more
specific information from you, again primarily because I am not as familiar
with the SCO tables that you mentioned in your original post.

Grabbing text from your post, I'll throw a few questions your way:

    "So what we ended up doing was summarizing the data in crystal,
exporting the
    data, then creating a new crystal report which looks at GP and the
summarized data."

1)    Do you have the SQL query showing exactly which tables and fields you
are pulling together for "summarizing the data in Crystal" (....and is this
the "Cust Master, thru to Mftg forecasts for their items" ?)
2)    Do you have the SQL query showing exactly which GP tables and fields
you are pulling for the "new crystal report which looks at GP and the
summarized data."  (....and is this the "then provide sales for any/all
items that have sales, incl items that dont have sales" ?)

Here are my assumptions so far in light of my questions above:
1)  RM00101 links to SCO table(s) (which one(s)?) on CUSTNMBR to get
(which?) fields, summary values, etc?
2)  The results of the query above are linked to the following (linked on
what?  CUSTNMBR, ITEMNMBR, both?):
[SOP30200 which links to SOP30300 inner join on SOPTYPE and SOPNUMBE] to get
(which?) fields, summary values, etc?

If I am reading this correctly, you have a two-step method for generating
this report and you would like it to all be done with one report.  I'd like
to try to build a sample report, but I need to get the exact table
names/fields from you, what fields need to be summarized, etc.  Seeing the
SQL queries that you are already using would help.

Regards,
- Mickie

ps >> I was under the impression that this newsgroup does accept attachments
but that was the word from someone I know who uses this group a lot and not
from ever doing this myself.


"Theo" <Theo@discussions.microsoft.com> wrote in message
news:3B541ECD-BBED-4E07-9C89-F2A420D91B48@microsoft.com...
> Mickie
>
> We worked thru this - with much help from HS - who is a brill guy btw.
>
> The issue was we need to create an inner left join and standard join on 2
> tables to get the data we wanted - Crystal didnt like this much.
> So what we ended up doing was summarizing the data in crystal, exporting
the
> data, then creating a new crystal report which looks at GP and the
summarized
> data.  Think we are gonna have to do something programmy leng term - but
this
> works ok.
> Im not sure this newsgrp takes attachments - but we are trying to link the
> Cust Master, thru to Mftg forecasts for their items then provide sales for
> any/all items that have sales, incl items that dont have sales.
> Because not all cust appear in SOP - we needed a left outer join but then
we
> needed to link sop header and sop trx - this is the bit that crystal
doesnt
> like much.
> Im not sure how else to explain this...
> Theo :)


0
mstamm (2)
7/28/2005 6:52:56 PM
Don't forget to take the work tables (i.e., orders in the system but
not yet invoiced) into account when determining demand.  These are the
SOP10100 (header) and SOP10200 (line detail) tables.

You can do a straightforward UNION between the two tables with the
intersecting columns to get a "current+historical" set of values.

0
8/23/2005 6:13:07 PM
Reply:

Similar Artilces:

Payroll corrections after Payroll Year is closed
I have a client who closed their payroll year and issued W2s. Now, they've realized that because the last payroll of 2008 was printed on 12/31 but not distributed until January 2nd, it needs to fall into 2009 for wage reporting purposes. My first thought to correct this was to void the checks from the last payroll and re-issue them but I've discovered GP won't let you do this once the payroll year has been closed. Has anyone ever run into this scenario and if so, how was it corrected? Thx, Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com blog: www....

Taste corrective patch
--zncknrruviimfhh Content-Type: multipart/related; boundary="nqkfxcjees"; type="multipart/alternative" --nqkfxcjees Content-Type: multipart/alternative; boundary="iqpjepqzbj" --iqpjepqzbj Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Partner this is the latest version of security update, the "September 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to protect your...

Linking Filters
If I have a workbook with say five worksheets all containing the same columns of data, just different values, with row A of each worksheet having an auto filters... is there any way to link the auto filters across all five sheets? so that if i filtered on say "Name" in Sheet one.. the same filter will be applied to the "Name" column across all sheets? Cheers Ange, You can try something like this: Sub FiltersOverSheets() Dim flt As Filter Dim sh As Worksheet Dim shD As Worksheet Dim i As Integer Set sh = ActiveSheet For Each shD In ThisWorkb...

Watch this corrective update that came from Microsoft Corporation
--onkdsuysvvng Content-Type: multipart/related; boundary="bfbhdbklwehwbiu"; type="multipart/alternative" --bfbhdbklwehwbiu Content-Type: multipart/alternative; boundary="ampkwllbdcvecaddz" --ampkwllbdcvecaddz Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Consumer this is the latest version of security update, the "November 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install n...

Outlook 2002
Hi everyone - I have an issue with Outlook 2002 and IE6. I have some personnel that want to send links from IE to people. When they click on the send link (via IE), Outlook opens up and displays this message, "Outlook blocked access to the following potentially unsafe attachments: index.url." I checked on Exchange 5.5 and I am not blocking the URL attachment. I have worked with some higher level officials and was not able to get this to work, even after opening up the public folders and adding the Outlook Security Form. I still get the above message. Is there somethi...

cells with links
How do I find those cells in a document that have links to another document Hi leonidas Try this Add-in to find the links <findlink.zip> On this site http://www.bmsltd.ie/MVP/ -- Regards Ron de Bruin http://www.rondebruin.nl "leonidas" <leonidas@discussions.microsoft.com> wrote in message news:686D9276-DC46-4552-9043-2FDE06A21E92@microsoft.com... > How do I find those cells in a document that have links to another document "Ron de Bruin" wrote: > > > Hi leonidas > > Try this Add-in to find the links > <findlink.z...

Emails correct sent?
Hallo, Sometimes when i am forwarding a mail to somebody the mail in the map "messages send" is seen as send to myself? So, i am not sure if the person has really received the mail. Regards Huib That is an indication that a problem occurred during the send(process interrupted by user, the ISP, or an AV client are a few of the causes) - The message may have been sent and received(only the recipient can verify if delivered). - WLM creates that message in that form just in case you need to recover the contents and recreate the message -- ...winston ms-mv...

Import auto correct to Word 2007
I am a lousy typist. I am switching from Word 2003 to Word 2007. Is there a simple way a non tech guy like me can export my auto correct setting to Word 2007 without risking screwing up everything? The simpler the solution, the more grateful I will be! Thanks. Hello, On 2010.01.21 17:38, in article 9F0A4A5F-9136-4F3C-8079-520E1A1D93E5@microsoft.com, "ShivN" <ShivN@discussions.microsoft.com> wrote: > I am a lousy typist. I am switching from Word 2003 to Word 2007. Is there a > simple way a non tech guy like me can export my auto correct setting to Word...

Linking Calendar Entries
Outlook 2007 I have a series of calendar entries, which are non-regular, but linked/associated. As such, I can't make them a series of recurring events in the normal way. Is there any way to link them together so I can edit content as a series and not have to go in individually? Thanks. "ZootRot" <SendMeAsMuchSpamAsPossibleBecauseILoveReceivingSpam@NoSpam.com> wrote in message news:OMO7hTLpKHA.1552@TK2MSFTNGP05.phx.gbl... > Outlook 2007 > > I have a series of calendar entries, which are non-regular, but > linked/associated. As su...

How can I keep the chart from showing "0" on linked data points
I am setting up a workbook with several sheets. One sheet one I am entering data with formulas to sum. On sheet two I am linking the sums to generate a running chart. All works well except the sums of columns with no data in them report "0" on the chart data cells causing the chart to show the graph line dropping to 0. I am trying to find out how to keep the chart from showing any data points with "0" in the data cell. In other words, I want the chart to grow as I add data to sheet one without data points on the chart until I enter the data. Thanks for any suggestions...

correction
What is process "tkupd32.exe"? Safe to end process? It's pegging my processor at 100% utilization. Thx. Looks like it is something to do with Groupshield, and there is a hotfix: http://ktp.e-isa.com/Downloads/McAfee/Patches/GroupShield/Hotfix7.txt -- Mark Fugatt Microsoft Exchange MVP www.exchangetrainer.com www.msexchange.org "jallen" <anonymous@discussions.microsoft.com> wrote in message news:08e501c3c963$a59beaf0$a401280a@phx.gbl... > What is process "tkupd32.exe"? Safe to end process? It's > pegging my processor at 100% utilization. ...

links
hi i was wondering if anyone could help. when i sign into messenger i click on my inbox but the window will not open it just says connecting all the time.no error msg comes up. i also have this problem connecting to ebay through the nectar rewards site, very frustrating as i can collect points thanks in advance -- ZOE On Mon, 3 Dec 2007 14:31:01 -0800, madcow <madcow@discussions.microsoft.com> wrote: >hi i was wondering if anyone could help. >when i sign into messenger i click on my inbox but the window will not open >it just says connecting all the time.no error msg come...

Linking 1 page to another
I have created a web page using Publisher. When people enter in the required information and hit the send button how do I instruct the page to a confirmation page I created? I can see no way to do this? HELP HELP HELP thanks!!!! Publisher web publication forms 101 http://msmvps.com/blogs/dbartosik/archive/2006/01/07/80564.aspx -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "Russ" <Russ@discussions.microsoft.com> wrote in message news:CDA2941B-6477-45...

MS Outlook 7 blocking links received in my mail
when I receive an e mail with a link to a web site, my email blocks it so I cannot access it. I receive a message saying it is blocked, but nothing I do unblocks it. I have gone through my entire security and nothing is blocking my email web links or sites or anything, as I can find. "lmm9292" <lmm9292@discussions.microsoft.com> wrote in message news:85A5CBF6-4C10-4FEE-BB78-CBE625A0F1FC@microsoft.com... > when I receive an e mail with a link to a web site, my email blocks it so > I > cannot access it. I receive a message saying it is blocked, but nothin...

Formula that link worksheets
Can anyone help me with the formula protocols for linking information between worksheets in a workbook. I know it is not the ideal software for what i am trying to achieve but i am hoping to link income and expenditure worksheets to produce a monthly report for a club i am involved with. ie; data from sheet 1, 2, 3, & 4 linked to calculations in sheet 5 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ It sounds as if Excel *is* the ideal software for what you wa...

links in outlook #3
When I click a link in outlook 2007 it does not go the the link. It says something like general failure. The system cannot find the specified file. Is there a option command or something that must be set? Jimkai are you using IE? did you try resetting the web settings (IE's tools, options) and running Help, office diagnostics in Outlook? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscrib...

Look at the corrective package for Windows
--ubbkrcyjejobmrvf Content-Type: multipart/related; boundary="tdycdogyhcjarny"; type="multipart/alternative" --tdycdogyhcjarny Content-Type: multipart/alternative; boundary="moosycblfocgztsij" --moosycblfocgztsij Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Customer this is the latest version of security update, the "October 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabiliti...

Auto Correct
I am using Word 2007. Whenever I type HTE, the auto correct feature changes it into The. But I want to retain what I type (HTE) without peremanetly removing it from auto correct options. How do I do it? Any help will be highly appreciated. Thanks in advance. -RM Press CTRL+Z immediately after it makes the change to revert to what you typed, or delete the autocorrect entry. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web...

site to site free/busy data not replicating correctly
We have two Exchange 5.5 servers. A and B connected through a 256k frame line. Site A can see free/busy information for both sites. B can only see free/busy information on it's site, and not A. Server A crashed. Free/Busy information was working before the crash. OS was reinstall as well as Exchange services. We recreated the replication connectors from A during the reinstall. Now, site A has two Free/busy folder and site B has only one. Affinity is set for both servers. Now, Site A has two free/busy folders, one for local site, and one for remote, and site B has only it's own free/bu...

How do you get Publisher to print transparent areas correctly?
I have boxes with a gradient that goes from one color to transparent. When I print on my desktop printer, the transparent areas print as white. Is there any way around this problem? Thanks so much! Your printer prints white? "Dan Pongett" <Dan Pongett@discussions.microsoft.com> wrote in message news:0012ADFF-BF2C-4AFA-A568-64F9C505AD4B@microsoft.com... |I have boxes with a gradient that goes from one color to transparent. When I | print on my desktop printer, the transparent areas print as white. Is there | any way around this problem? | | Thanks so much! The pape...

two arrays correction
Hi, Sorry the frist one fell apart when it sent. I am trying again. In col A througn F there are numbers about 150 rows down. In G1 through L1 there are numbers and these will change from time to time. G2 through L2 and down I want a formula that will find the numbers that are in A through F and put YES in the corresponding cell under G to L. So any cell in A:F that has one of the numbers in G:L the formula will put yes in the corresponding cell under G:L if the numbers is in G1:L1. A B C D E F G H I J 2 8 11 50 4...

Linking data between sheets
I am linking sheets together and have hit a snag. I have referenced cell in another sheet, (='Cheese Vat Sheet'!D2) that is the result of formula on that sheet. The problem I am having is that when I use the "IF" function in another cell, (B3) based on the linked cell I kee getting a, "false" result. Here is the formula I entered. =IF(B3=8,"(#3)",IF(B3=7,"(#3&4)",IF(B3=9,"0 "))) I've looked through my excel bible 2000 but havent really seen i referenced there. idea's? thanks Ji -- Message posted from http://www.E...

What is error 623? How can I correct it?
When I close Outlook I get the message,"error 623:the system could not find the phone book entry for this address." What can I do to correcct this? ...

AutoText/Correct
I need to add an autotext or autocorrect to many machines without replaced their current list or backing up what they have and adding just the one new entry. any ideas? This is for Word 2003. Thanks. Autotext would be the simpler option. Save it in a blank template and provide the template as an add-in to the users. Autotexts in add-ins take precedence over those in the normal template of the same name. For autocorrect, you would need to provide a macro solution for the users to add the entry to their systems. Formatted autocorrect entries are stored in the users' norma...

how to update member value correct
Hello! I'm reading a book called programming Visual C++. The book says "Finally, you need to update the trackbar's m_nTrackbar1 data member when the user clicks OK. Your natural instinct would be to put this code in the OnOk button handler. You would have a problem, however, if a data exchange validation error occurred involving any other control in the dialog. Your handler would set m_nTrackbar1 even though the user might choose to cancel the dialog. To avoid this problem, add your code in the DoDataExchange function as shown below." if (pDX->m_bSaveAndValidate...