mas90 and crystal 8.5 report problem - missing data 1

[micheleg]

I have a mas90 crystal report, i created, not canned, the problem I am having is I am lossing data when I select certain criteria.
This is a sales report and there are certain inventory item numbers which appear as line items on my invoices that I must exclude, which is fine I am able to exclude these items with no problem. But, I also have misc. item codes associated with my invoices, which also disappear. When I delete formula to exclude those item numbers, my misc. items reappear.
Why is it doing this. Misc. item codes are not a part of my formula, so why do they disappear when I exclude inventory items.
I have tried, selections with product line, but the same thing occurrs. I know there is an answer... Please help me

 

[Turkbear]

Hi,
More than 1 table involved?
How are they joined?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

Share HOW you are "making them disappear" and " I am able to exclude these items with no problem". That isn't a technical description, please try to use real technical terms and demonstrate what you are doing.

Successful posts tend to include:

crystal verison
database/connectivity used
Example data (show the recordset)
Expected output (show what is output from the recordset)

-k

 

[micheleg]

Tables involved are:
ARO_History
ARN_Header
AR1_Cust.Master
Linked in that above order order with equal joins......
Selection formula giving me the problem is: ARO-SOItem <> "items" result returns the items excluded which is correct, except as I previously stated, SOMISCITEMCODEs in ARN_Header also are excluded without any formula instruction.

 

[lbass]

I think the problem is that not all invoices have a history. When you add a restriction on history items, you are engaging the history table, probably for the first time. With an equal join, referencing anything in that table will result in the join becoming active and eliminating records where there is no history. You did not describe your tables or what they contain in any depth, so I have no idea what the tables contain, so this is just a guess. Try a left join FROM the header table TO the history table, and maybe an equal join from the header table to the Customer table.

For more help, please show the fields in each table that you are using. If the names are not descriptive, use aliases so we can follow.

-LB

 

[micheleg]

I have accessed the history files before so I don't think that is what this is.
I am currently using MAS90 3.5 with Crystal 8.5. The data files I am using are:
ARO-History
Invoice Date
Cust. No.
ARN-Detail
SO Item Num
SOExtChargeAmt
ARN-CustomerMaster
Customer Name
Sales Rep
Report is grouped by:
1. Sales Rep
2. Customer Num
Within the detail I have approx. 30 formulas to calculate the invoice amts by date, ie if month and year = ?? then ??. No big deal the formulas work fine.
SO Item Num are inventory item numbers with product lines, the SO Misc. item codes are created in sales order set up and are used as non inventory billing items, ie. labor or warranty (non inventory items).

 

[micheleg]

by the way, i have tried your first suggestion with the joins, the report is running now, the report takes about 45 minutes to run...... yeh one problem at a time. I'll just be thankful to get this report to return accurate information.

 

[lbass]

You didn't show the header table or where the SO miscellaneous item "codes" (which seem to be different from the item numbers) are coming from. From your earlier post, I thought the miscellaneous items were from the history table, but that seems unlikely.

Are these three tables (or four, with the header table) the only ones in your report?

-LB

 

[micheleg]

i am sorry, I see what your asking, and I made a mistake on my first post, the files are
ARN_Inv.HistoryHeader and ARO_Inv.HistoryDetail, the somisc item code and soitem number come from the Inv.HistoryDetail file, the only files I have pulling from the Inv.HistoryHeader is Invoice Date and Customer number.
soitem and somisc item code are housed in ARO_InvHistoryDetail file.

 

[lbass]

Sorry, I just can't follow this. Now it looks like you're saying that when you exclude certain items from the Detail table that certain codes from the same table disappear. If those codes appear in the same records, then certainly this would happen.

I think you should provide sample detail level data before and after you add the selection formula that makes somisc item codes disappear, and that you should also share the formula that is causing this. Be sure to identify what fields the data is drawn from.

-LB

 

[micheleg]

This is how my data appears prior to using my formula:

PL SOITEM DESCRIPTION SO MISC.ITEM EXT.AMT
SERVICE SERVICE 50.00
REPAIR LABOR LABOR 100.00
65 41951 KEY PAD STSMAN 47.00
34 1621638 3 ON 3 UP 99.00
10 AN80373 LOTS OF SLOTS 450.00
90 WCSTICK WC STICKER 5.00
MILLE BINGO MACH MILLENNIUM 500.00
GAMETECH BINGO M GAMETECH 475.00

This is my formula{ARO_InvHistoryDetail.SOItemNumber} in ["ACSTICKER", "SERVSUPP", "T11", "T12", "T13", "T14", "WCSTICKER", "WCSTICKERS"])

This is my data after I run report with above formula:
PL SOITEM DESCRIPTION SO MISC.ITEM EXT.AMT
65 41951 KEY PAD STSMAN 47.00
34 1621638 3 ON 3 UP 99.00
10 AN80373 LOTS OF SLOTS 450.00

You are correct the soitem and somisc.item are coming from the same data file.
I must tell you I appreciate your patience and help.

micheleg

 

[lbass]

I think the problem is that you are not accounting for nulls. I don't know where you are using the formula you supplied, and I'm guessing there is a leading "not" that you omitted, since otherwise it doesn't relate to the sample data. If this is a record selection formula, change it to:

isnull({ARO_InvHistoryDetail.SOItemNumber}) or
not({ARO_InvHistoryDetail.SOItemNumber} in ["ACSTICKER", "SERVSUPP", "T11", "T12", "T13", "T14", "WCSTICKER", "WCSTICKERS"])

If this is a conditonal formula, you would use:

if isnull({ARO_InvHistoryDetail.SOItemNumber}) or
not({ARO_InvHistoryDetail.SOItemNumber} in ["ACSTICKER", "SERVSUPP", "T11", "T12", "T13", "T14", "WCSTICKER", "WCSTICKERS"]) then
{table.amount} //or whatever

-LB

 

[micheleg]

LB,
I will try this in the am...
The only other formula is on invoice data which is also in ARO_InvHistoryDetail and there are not any conditinal formulas.
Ill let you know how this works out
I appreciate it

 

[micheleg]

LB

Thank you ;-)
micheleg