SOP Census data issue

(0) Share

Report

Posted on by Trancefrank

955

HI I have been given a test machine with Great Plains for the purpose of writing some Crytal Reports. In particular, I am taking a currently manual process (which runs some Crystal Reports and Excels, and then someone keys in some data into an Excel...) and creating 1 Crystal Report to do this.

Upon some investigation, I noticed that the query was referencing SOP10200 (& its corresponding header table). This table though needs to reference both the unposted and history table related to this data. That would include SOP30200 (& its corresponding header table). When we changed the table to which it is pointing – we got data for the census and billed amount, but the data regarding expenses or “quoted sales” was empty – this is because it was now looking at the
history table.

So, for the quotes I need the SOP10200. For the invoiced amount (which is used for billed amount)and qty (which we use for census) I need SOP30200.

I wrote a Crystal Report that is based on a SQL Union looking at the SOP10200 and SOP10100, as well as going to historical data SOP302 and SOP303 this is where the Census information is kept.

This report has 30,000 records from the current data and only 9 records from the History data (where we get the census).

The History data is only July of 2007. Does that make sense to you?

*This post is locked for comments

I have the same question (0)

All responses (13)

Answers (0)

callen 4,595 on at

Like (0)

Report

Re: SOP Census data issue

I'm not sure if this is what you are saying so here's what I would do.

Create a union query of two datasets.

Dataset 1: SOP10100 and SOP10200 for the columns and records you need

Dataset 2: SOP30200 and SOP30300 for the same columns and whatever records you need

Build your report on the result of the union query. You could create a SQL view based on the union query and reference the view in your report.

Was this reply helpful? Yes No
Richard Schultz 1,085 on at

Like (0)

Report

Re: SOP Census data issue

Callen is correct - a UNION is what you need. Note that, if you're using Crystal 10 or higher, you can do this with a Crystal Command; if not, you'll need to make a SQL View.
However, I think the better datasets are:
Dataset 1: SOP10100 UNION SOP30200 (Header data)
Dataset 2: SOP10200 UNION SOP30300 (Detail data)
You can then treat the first dataset as your global header, and the second dataset as your global details. Remember to always link on the SOPNUMBE AND SOPTYPE, not just the SOPNUMBE.
Here are a few things to consider:
1) The SOPTYPE field determines whether you're looking at a Quote, Order or Invoice (SOPTYPE = 1, 2, 3 respectively). Returns are 4, Backorders are 5, and 6 is (I believe) fulfilled orders.
2) SOP10100 & 10200 are related, as 10200 is the detail record for 10100. This is a WORK/OPEN table, and can contain any record (not just quotes - you can have Returns & Invoices in here as well). The record moves from the "1's" to the "3's" when the document is complete; for example, if you upgrade an Order to an Invoice, the Order moves to the "3's" and the new invoice records are created in the "1's". For more on this, please check this link: http://www.eggheadcafe.com/conversation.aspx?messageid=33042509&threadid=33042476. Based on that, it does seem odd that you have such a large amount of data in current and so little in history. I noted that you used UNIONS of 10100 & 10200 - how did you do that? The columns are different (one is header info, one is detail); maybe that is the source of the confusion?
3) Longshot idea - If nothing else answers your questions, look into the idea that the history tables may have recently been truncated; either because a DBA figured they're history, so who needs them, or possibly (total guess here) a setting to not keep history somewhere in GP. Again, this is just a WAG, so think about this as a last resort only.

Was this reply helpful? Yes No
Trancefrank 955 on at

Like (0)

Report

Re: Re: SOP Census data issue

Richard, I got the idea of union of the 10100 and 10200 from discussion with the Admin but I will try your concept. One thing I noticed in the data is that the SOP Type is only '1' but that is what the current SQL is returning.

This is the SQL I have been using:

SELECT
IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr,
SOP10200.ITEMNMBR,
SOP10200.SOPNUMBE,
SOP10200.QUANTITY,
SOP10200.OXTNDPRC,
SOP10200.SOPTYPE,
SOP10100.DOCDATE,
'Current' as source

FROM ((((PBS.dbo.SOP10200
SOP10200 INNER JOIN PBS.dbo.IV00101 IV00101 ON
SOP10200.ITEMNMBR=IV00101.ITEMNMBR)

INNER JOIN
PBS.dbo.SOP10100 SOP10100 ON
(SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND
(SOP10200.SOPNUMBE=SOP10100.SOPNUMBE))

INNER JOIN
PBS.dbo.IV40600 IV40600 ON
IV00101.ITMGEDSC=IV40600.USCATVAL)

INNER JOIN
PBS.dbo.IV40600 CATS ON
IV00101.USCATVLS_2=CATS.USCATVAL)

INNER JOIN
PBS.dbo.RM00101 RM00101 ON
SOP10100.CUSTNMBR=RM00101.CUSTNMBR

WHERE
RM00101.CUSTNAME = 'Weekend Fun'

UNION ALL

SELECT
IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr,
SOP30300.ITEMNMBR,
SOP30300.SOPNUMBE,
SOP30300.QUANTITY,
SOP30300.OXTNDPRC,
SOP30300.SOPTYPE,
SOP30200.DOCDATE,
'History' as source

FROM ((((PBS.dbo.SOP30300
SOP30300 INNER JOIN PBS.dbo.IV00101 IV00101 ON
SOP30300.ITEMNMBR=IV00101.ITEMNMBR)

INNER JOIN
PBS.dbo.SOP30200 SOP30200 ON
(SOP30300.SOPTYPE=SOP30200.SOPTYPE) AND
(SOP30300.SOPNUMBE=SOP30200.SOPNUMBE))

INNER JOIN
PBS.dbo.IV40600 IV40600 ON
IV00101.ITMGEDSC=IV40600.USCATVAL)

INNER JOIN
PBS.dbo.IV40600 CATS ON
IV00101.USCATVLS_2=CATS.USCATVAL)

INNER JOIN
PBS.dbo.RM00101 RM00101 ON
SOP30200.CUSTNMBR=RM00101.CUSTNMBR

WHERE
RM00101.CUSTNAME = 'Weekend Fun'

When I put in the changes you suggested I got an error message SQL failed to open rowset on SOP30200.ITEMNMBR,

SELECT
IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr,
SOP30200.ITEMNMBR,
SOP30200.SOPNUMBE,
SOP30200.QUANTITY,
SOP30200.OXTNDPRC,
SOP30200.SOPTYPE,
SOP10100.DOCDATE,
'Current' as source

Was this reply helpful? Yes No
Richard Schultz 1,085 on at

Like (0)

Report

Re: Re: Re: SOP Census data issue

The SQL looks fine. I'm not familiar with the "CATS" table; is that a third-party add on? Only thing I can think of is that your INNER JOIN with CATS might be limiting the result set in the History query.
From your earlier post, I thought the UNION was between 10100 and 10200 (that is, 10100 was on one side of the UNION, and 10200 was on the other). Clearly, that's not the case!
SOPTYPE = 1 is Quote; sounds like you only have "quotes" in your data. That sounds odd as well, since neither Quotes nor Orders can be posted to the GL, and I imagine you have invoices posted to your GL...
I'd start with a LEFT OUTER JOIN on the CATS table - see if that helps you get other data.

Was this reply helpful? Yes No
Trancefrank 955 on at

Like (0)

Report

Re: Re: Re: Re: SOP Census data issue

the CATS table was in the original SQL the client sent me. It looks like the catalogue master list but I don't know what that means.
Running the report in the interface that our Crystal Developer has created yields results that are complete. When I ran the same report in Crystal itself, we saw that the census number and billed amount were 0. Upon some investigation, we noticed that the query was referencing SOP10200 (& its corresponding header table). This table though needs to reference both the unposted and history table related to this data. That would include SOP30200 (& its corresponding header table). When we changed the table to which it is pointing – we got data for the census and billed amount, but the data regarding expenses or “quoted sales” was empty – this is because it was now looking at the history table. Now I am wondering if the SOP 303 is the correct table!

So, for the quotes I need the SOP10200. For the invoiced amount (which we used for billed amount)and qty (which we use for census) I need SOP30200. Now I am wondering if the SOP 303 is the correct table!

Regarding the customers, it is a sub-entity of another customer and is billed as a whole. Therefore, I would like to start billing this customer separately as its own entity. For the meantime, I have save data from another customer. I based my SQL on the exisitng Crystal Reports (unioned for the history and unposted tables).

Was this reply helpful? Yes No
Richard Schultz 1,085 on at

Like (0)

Report

Re: Re: Re: Re: Re: SOP Census data issue

I'm pretty confused now. You say the interface your Crystal Developer made provides complete data, but Crystal doesn't. What is the other interface? Maybe look into its logic to understand the missing piece.
Second, SOP10200 does not have any reference to SOP30200 - 10200 holds OPEN data, and 30200 holds HISTORY data. Joining the two will result in errors. SOP10200 is the detail table for SOP10100; SOP30200 is the header table for SOP30300. Records are either in 10100/10200 OR they're in 30200/30300.
Third, CATS is not a native GP table (at least, as of GP10.0). It may be an add-on, a customization, or third-party piece. Accordingly, it's possible that it relates to the quote/order/invoice data (SOP) in a way that is causing your problems. Again, I'd try a LEFT OUTER JOIN between SOP & CATS to bring in your data.

Was this reply helpful? Yes No
Trancefrank 955 on at

Like (0)

Report

Re: Re: Re: Re: Re: Re: SOP Census data issue

Richard, I am not having success implementing this left join. I do not know enough on how to approach this solution.

Was this reply helpful? Yes No
Richard Schultz 1,085 on at

Like (0)

Report

Re: Re: Re: Re: Re: Re: Re: SOP Census data issue

Oh. Sorry; I thought you wrote the SQL script you posted. My bad.
If you're writing the SQL, you would change:
INNER JOIN
PBS.dbo.IV40600 CATS ON
IV00101.USCATVLS_2=CATS.USCATVAL)
to
LEFT OUTER JOIN
PBS.dbo.IV40600 CATS ON
IV00101.USCATVLS_2=CATS.USCATVAL)
If you're making the change in Crystal, just click on the "links" button, right-click the link between CATS and IV00101, and in the left-most section there are radio buttons. The top one is clicked (INNER or EQUAL JOIN); choose the next one down (LEFT JOIN, I think it's called). Then save your changes & rerun your data.

Was this reply helpful? Yes No
Trancefrank 955 on at

Like (0)

Report

Re: Re: Re: Re: Re: Re: Re: Re: SOP Census data issue

I tried the Left outer change but it looks the same. One suggestion is to use Customer Number as filter instead of Customer Name - as that is the unique index. Crystal does not enforce consistency of names and customer may possibly have different names on different sales transactions.

This did not work. I have to explore how I can locate where the history records are kept.

Any thoughts how to do this?

Was this reply helpful? Yes No
Richard Schultz 1,085 on at

Like (0)

Report

Re: Re: Re: Re: Re: Re: Re: Re: Re: SOP Census data issue

I took a more-thorough look at your initial SQL, and realized a couple of things:
1) CATS is an alias - I'm sorry, I should have realized this. When you call a table more than once in a query (in this case , IV40600), you need to give the table a different name in the query.
2) IV40600 should also be left-outer joined. In your query, I know that the fields you reference for RM00101, IV00101 and the SOP tables you have will be populated, because of the logic internal to GP. However, I'm not so sure about the UserCatLongDescr field; as it's not an 8-digit cryptic field name, it is obviously a newer field, and so may not exist for your historical records - an inner join would result in no record returned. The left outer join gives you, at least, the records for those fields that are populated.
3) The use of parenthesis, (), in your FROM clause, I believe, enforces the priority of the links so, for example, your SOP tables are being linked after the IV40600 data; if there's no IV40600 data, there's no further look for the SOP data.
I've restructured your query, removing the () and putting the order of the tables in a manner that should return the data you want. NOTE: You have the right fields and tables; the problem will be the joins between them.
If you are running this in MS Query Manager (or SQL Server Management Studio) then just paste the code, below, into a query window. If you're working in Crystal, re-arrange the links in the linking window so that the first table of the FROM clause (SOP10100) is the left-most table in the window and, as you go down the FROM clause, those are the tables that should link to the right of SOP10100.
Try this SQL:
SELECT
IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr,
SOP10200.ITEMNMBR,
SOP10200.SOPNUMBE,
SOP10200.QUANTITY,
SOP10200.OXTNDPRC,
SOP10200.SOPTYPE,
SOP10100.DOCDATE,
'Current' as source

FROM
PBS.dbo.SOP10100 SOP10100 ON
(SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND
(SOP10200.SOPNUMBE=SOP10100.SOPNUMBE)

INNER JOIN
PBS.dbo.IV00101 IV00101 ON
SOP10200.ITEMNMBR=IV00101.ITEMNMBR

INNER JOIN
PBS.dbo.RM00101 RM00101 ON
SOP10100.CUSTNMBR=RM00101.CUSTNMBR

LEFT OUTER JOIN
PBS.dbo.IV40600 IV40600 ON
IV00101.ITMGEDSC=IV40600.USCATVAL

LEFT OUTER JOIN
PBS.dbo.IV40600 CATS ON
IV00101.USCATVLS_2=CATS.USCATVAL

UNION
SELECT
IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr,
SOP10200.ITEMNMBR,
SOP10200.SOPNUMBE,
SOP10200.QUANTITY,
SOP10200.OXTNDPRC,
SOP10200.SOPTYPE,
SOP10100.DOCDATE,
'History' as source

FROM
PBS.dbo.SOP30200 SOP30200 ON
(SOP30300.SOPTYPE=SOP30200.SOPTYPE) AND
(SOP30300.SOPNUMBE=SOP30200.SOPNUMBE)

INNER JOIN
PBS.dbo.IV00101 IV00101 ON
SOP30300.ITEMNMBR=IV00101.ITEMNMBR

INNER JOIN
PBS.dbo.RM00101 RM00101 ON
SOP30200.CUSTNMBR=RM00101.CUSTNMBR

LEFT OUTER JOIN
PBS.dbo.IV40600 IV40600 ON
IV00101.ITMGEDSC=IV40600.USCATVAL

LEFT OUTER JOIN
PBS.dbo.IV40600 CATS ON
IV00101.USCATVLS_2=CATS.USCATVAL

Was this reply helpful? Yes No

Community site session details

SOP Census data issue

Helpful resources

Quick Links

Responsible AI policies

Pallavi Phade – Community Spotlight

Congratulations to the September Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Featured topics

Product updates

Community site session details

SOP Census data issue

Helpful resources

Quick Links

Responsible AI policies

Pallavi Phade – Community Spotlight

Congratulations to the September Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Featured topics

Product updates