Crystal or BAQ gurus

Yahoo Archive
1
Hello Randy, here's the BAQ.

[for each PartMtl where ( ( PartMtl.PartNum = Part.PartNum OR PartMtl.MtlPartNum = Part.PartNum)) no-lock , each Part where (PartMtl.Company = Part.Company) no-lock .]

*I filtered the query for an existing "partmtl.partnum" in my company to limit the number of records in my test.

I basically got 2 records for every Material in the BOM; both with the same Parent Part (PartMtl.PartNum), the same Material Part (PartMtl.MtlPartNum), but different Part Number (Part.PartNum) and different Part Description (Part.PartDescription)....

Please contact me if I can further assist you.

Regards.

Roel Martinez
ERP Analyst

SABRE TUBULAR STRUCTURES (tm)
www.sabretubularstructures.com<http://www.sabretubularstructures.com/>
8653 E. Hwy 67 | Alvarado, TX 76009
Phone: 817-852-1700 ext. 11858 | Direct Line: 817-852-1858 |Fax: 817-852-1705

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of randyweb
Sent: Friday, September 10, 2010 11:16 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: crystal or BAQ gurus



I tried this and returned zero records. Can you paste the BAQ please?

--- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>, Roel Martinez <rmartinez@...> wrote:
>
> Hello Ken, all.
>
> When you are linking your tables in Phrase Builder, link PartMtl to Part but only through Company; this would bring all the Part table's records if you did not limited in the criteria, right? Well, on the criteria for the Part table, you enter the following 2 items:
>
>
> * PartMtl.PartNum = Part.PartNum
> OR
>
> * PartMtl.MtlPartNum = Part.PartNum
>
> This will only bring 2 records from the Part Table... Hope it helps, because yes...it will create duplicates, but you can address them at Crystal level.
>
> Regards.
>
> Roel Martinez
> ERP Analyst
>
> SABRE TUBULAR STRUCTURES (tm)
> www.sabretubularstructures.com<http://www.sabretubularstructures.com/>
> 8653 E. Hwy 67 | Alvarado, TX 76009
> Phone: 817-852-1700 ext. 11858 | Direct Line: 817-852-1858 |Fax: 817-852-1705
>
>
>
>
>
> [Non-text portions of this message have been removed]
>



[Non-text portions of this message have been removed]
2
Our Part description are about 2-3 lines long sometimes. I can't seem to modify the Data definition to pickup more than 30 characters, Epicor tells me I need the SDK package, so I'm trying to run my report off BAQs. My dilemma is that from the PartMtl I can only get the description of either the parent part or the material part. How can I get the description for both under one BAQ? It's driving me nuts and I'm sure there is an easy answer to this. Thanks for your help.


Ken





[Non-text portions of this message have been removed]
3
Hello Ken, all.

When you are linking your tables in Phrase Builder, link PartMtl to Part but only through Company; this would bring all the Part table's records if you did not limited in the criteria, right? Well, on the criteria for the Part table, you enter the following 2 items:


* PartMtl.PartNum = Part.PartNum
OR

* PartMtl.MtlPartNum = Part.PartNum

This will only bring 2 records from the Part Table... Hope it helps, because yes...it will create duplicates, but you can address them at Crystal level.

Regards.

Roel Martinez
ERP Analyst

SABRE TUBULAR STRUCTURES (tm)
www.sabretubularstructures.com<http://www.sabretubularstructures.com/>
8653 E. Hwy 67 | Alvarado, TX 76009
Phone: 817-852-1700 ext. 11858 | Direct Line: 817-852-1858 |Fax: 817-852-1705





[Non-text portions of this message have been removed]
4
or another perhaps dodgy way would be to get the PartNum description from Part table, and the MtlPartNum description from another table, eg PartDtl. (PartDtl of course isn't ideal, it was just the first table I could find that also had description. It will end up with duplicates also, but there are ways around this in Dashboard etc).

--- In vantage@yahoogroups.com, Roel Martinez <rmartinez@...> wrote:
>
> Hello Ken, all.
>
> When you are linking your tables in Phrase Builder, link PartMtl to Part but only through Company; this would bring all the Part table's records if you did not limited in the criteria, right? Well, on the criteria for the Part table, you enter the following 2 items:
>
>
> * PartMtl.PartNum = Part.PartNum
> OR
>
> * PartMtl.MtlPartNum = Part.PartNum
>
> This will only bring 2 records from the Part Table... Hope it helps, because yes...it will create duplicates, but you can address them at Crystal level.
>
> Regards.
>
> Roel Martinez
> ERP Analyst
>
> SABRE TUBULAR STRUCTURES (tm)
> www.sabretubularstructures.com<http://www.sabretubularstructures.com/>
> 8653 E. Hwy 67 | Alvarado, TX 76009
> Phone: 817-852-1700 ext. 11858 | Direct Line: 817-852-1858 |Fax: 817-852-1705
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
5
Beware, though, if you try and bring over the entire part table you are in
for a long running query and a large XML file.

What about just a sub-report using ODBC?



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
Roel Martinez
Sent: Thursday, April 08, 2010 6:37 PM
To: vantage@yahoogroups.com
Subject: [Vantage] crystal or BAQ gurus





Hello Ken, all.

When you are linking your tables in Phrase Builder, link PartMtl to Part but
only through Company; this would bring all the Part table's records if you
did not limited in the criteria, right? Well, on the criteria for the Part
table, you enter the following 2 items:

* PartMtl.PartNum = Part.PartNum
OR

* PartMtl.MtlPartNum = Part.PartNum

This will only bring 2 records from the Part Table... Hope it helps, because
yes...it will create duplicates, but you can address them at Crystal level.

Regards.

Roel Martinez
ERP Analyst

SABRE TUBULAR STRUCTURES (tm)
www.sabretubularstructures.com<http://www.sabretubularstructures.com/>
8653 E. Hwy 67 | Alvarado, TX 76009
Phone: 817-852-1700 ext. 11858 | Direct Line: 817-852-1858 |Fax:
817-852-1705

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
6
Ken,

The best solution that I could come up with was to recreate this report
in Excel using the ODBC connection.



Tim Dines

Information Technology

Steel Parts Manufacturing, Inc.

801 Berryman Pike

Tipton, IN 46072

Direct: 765-675-5201

Cell: 765-437-1402



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Ken Taylor
Sent: Thursday, April 08, 2010 5:46 PM
To: vantage@yahoogroups.com
Subject: [Vantage] crystal or BAQ gurus





Our Part description are about 2-3 lines long sometimes. I can't seem to
modify the Data definition to pickup more than 30 characters, Epicor
tells me I need the SDK package, so I'm trying to run my report off
BAQs. My dilemma is that from the PartMtl I can only get the description
of either the parent part or the material part. How can I get the
description for both under one BAQ? It's driving me nuts and I'm sure
there is an easy answer to this. Thanks for your help.

Ken

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
7
Tim, with ODBC, would I have to set each individual computer for the connection or could that be done one time on the server for all to use and I was wondering if you had an excel report already written that you can share. Thanks.


Ken




________________________________
From: "Dines, Tim" <tdines@...>
To: vantage@yahoogroups.com
Sent: Fri, April 9, 2010 7:38:12 AM
Subject: RE: [Vantage] crystal or BAQ gurus


Ken,

The best solution that I could come up with was to recreate this report
in Excel using the ODBC connection.

Tim Dines

Information Technology

Steel Parts Manufacturing, Inc.

801 Berryman Pike

Tipton, IN 46072

Direct: 765-675-5201

Cell: 765-437-1402

From: vantage@yahoogroups .com [mailto:vantage@yahoogroups .com] On Behalf
Of Ken Taylor
Sent: Thursday, April 08, 2010 5:46 PM
To: vantage@yahoogroups .com
Subject: [Vantage] crystal or BAQ gurus

Our Part description are about 2-3 lines long sometimes. I can't seem to
modify the Data definition to pickup more than 30 characters, Epicor
tells me I need the SDK package, so I'm trying to run my report off
BAQs. My dilemma is that from the PartMtl I can only get the description
of either the parent part or the material part. How can I get the
description for both under one BAQ? It's driving me nuts and I'm sure
there is an easy answer to this. Thanks for your help.

Ken

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]







[Non-text portions of this message have been removed]
8
Ken,

You will have to install the ODBC driver on each machine. When I write a report like this, I save all of the login information in the data connection properties and set it to refresh each time it is opened. That way the users never have to put in a password if you don’t want them to.



Tim Dines

Information Technology

Steel Parts Manufacturing, Inc.

801 Berryman Pike

Tipton, IN 46072

Direct: 765-675-5201

Cell: 765-437-1402



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Ken Taylor
Sent: Friday, April 09, 2010 11:27 AM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] crystal or BAQ gurus





Tim, with ODBC, would I have to set each individual computer for the connection or could that be done one time on the server for all to use and I was wondering if you had an excel report already written that you can share. Thanks.

Ken

________________________________
From: "Dines, Tim" <tdines@... <mailto:tdines%40steelparts.com> >
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Sent: Fri, April 9, 2010 7:38:12 AM
Subject: RE: [Vantage] crystal or BAQ gurus

Ken,

The best solution that I could come up with was to recreate this report
in Excel using the ODBC connection.

Tim Dines

Information Technology

Steel Parts Manufacturing, Inc.

801 Berryman Pike

Tipton, IN 46072

Direct: 765-675-5201

Cell: 765-437-1402

From: vantage@yahoogroups .com [mailto:vantage@yahoogroups .com] On Behalf
Of Ken Taylor
Sent: Thursday, April 08, 2010 5:46 PM
To: vantage@yahoogroups .com
Subject: [Vantage] crystal or BAQ gurus

Our Part description are about 2-3 lines long sometimes. I can't seem to
modify the Data definition to pickup more than 30 characters, Epicor
tells me I need the SDK package, so I'm trying to run my report off
BAQs. My dilemma is that from the PartMtl I can only get the description
of either the parent part or the material part. How can I get the
description for both under one BAQ? It's driving me nuts and I'm sure
there is an easy answer to this. Thanks for your help.

Ken

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
9
Hello Roel, that could work, but how can I group by the parent part? they are both the same. Pardon my ignorance. Thanks.


ken




________________________________
From: Roel Martinez <rmartinez@...>
To: "vantage@yahoogroups.com" <vantage@yahoogroups.com>
Sent: Thu, April 8, 2010 3:36:54 PM
Subject: [Vantage] crystal or BAQ gurus


Hello Ken, all.

When you are linking your tables in Phrase Builder, link PartMtl to Part but only through Company; this would bring all the Part table's records if you did not limited in the criteria, right? Well, on the criteria for the Part table, you enter the following 2 items:

* PartMtl.PartNum = Part.PartNum
OR

* PartMtl.MtlPartNum = Part.PartNum

This will only bring 2 records from the Part Table... Hope it helps, because yes...it will create duplicates, but you can address them at Crystal level.

Regards.

Roel Martinez
ERP Analyst

SABRE TUBULAR STRUCTURES (tm)
www.sabretubularstr uctures.com<http://www.sabretub ularstructures. com/>
8653 E. Hwy 67 | Alvarado, TX 76009
Phone: 817-852-1700 ext. 11858 | Direct Line: 817-852-1858 |Fax: 817-852-1705

[Non-text portions of this message have been removed]







[Non-text portions of this message have been removed]
10
Good Day Ken:

Link PartMatl to Part table but through the matl part# in PartMatl
not Parent-Part#, then get the description from the Part table?


len.hartka@...

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Ken Taylor
Sent: Thursday, April 08, 2010 5:46 PM
To: vantage@yahoogroups.com
Subject: [Vantage] crystal or BAQ gurus




Our Part description are about 2-3 lines long sometimes. I can't seem to
modify the Data definition to pickup more than 30 characters, Epicor
tells me I need the SDK package, so I'm trying to run my report off
BAQs. My dilemma is that from the PartMtl I can only get the description
of either the parent part or the material part. How can I get the
description for both under one BAQ? It's driving me nuts and I'm sure
there is an easy answer to this. Thanks for your help.

Ken

[Non-text portions of this message have been removed]






This e-mail and any attachments may contain proprietary and/or confidential information. If you are not the intended recipient, please notify the sender immediately by reply e-mail or at 410-472-2900 and then delete the message without using, disseminating, or copying this message or any portion thereof. With e-mail communications you are urged to protect against viruses.


[Non-text portions of this message have been removed]
11
Hi Len, the issue is that I need both descriptions to show on the report. One is the parent part by which I group and below that the matl part along with its description. It's obvious that I'm missing something. Thanks.


Ken




________________________________
From: Len Hartka <len.hartka@...>
To: vantage@yahoogroups.com
Sent: Fri, April 9, 2010 1:06:13 PM
Subject: RE: [Vantage] crystal or BAQ gurus


Good Day Ken:

Link PartMatl to Part table but through the matl part# in PartMatl
not Parent-Part# , then get the description from the Part table?


len.hartka@sunautom ation.com

____________ _________ _________ __

From: vantage@yahoogroups .com [mailto:vantage@yahoogroups .com] On Behalf
Of Ken Taylor
Sent: Thursday, April 08, 2010 5:46 PM
To: vantage@yahoogroups .com
Subject: [Vantage] crystal or BAQ gurus

Our Part description are about 2-3 lines long sometimes. I can't seem to
modify the Data definition to pickup more than 30 characters, Epicor
tells me I need the SDK package, so I'm trying to run my report off
BAQs. My dilemma is that from the PartMtl I can only get the description
of either the parent part or the material part. How can I get the
description for both under one BAQ? It's driving me nuts and I'm sure
there is an easy answer to this. Thanks for your help.

Ken

[Non-text portions of this message have been removed]

This e-mail and any attachments may contain proprietary and/or confidential information. If you are not the intended recipient, please notify the sender immediately by reply e-mail or at 410-472-2900 and then delete the message without using, disseminating, or copying this message or any portion thereof. With e-mail communications you are urged to protect against viruses.

[Non-text portions of this message have been removed]







[Non-text portions of this message have been removed]
12
Good Day Ken:

I am a complete novice in BAQ.
I assumed the parent description was in the PartMatl file.

Based on what I would do in ACCESS:

Insert the Part table twice? - link one to parent_part# and the
other to Component_Part#?
I, of course, have no idea if you can call a table twice in BAQ. in
ACCESS its no problem.


Len

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Ken Taylor
Sent: Friday, April 09, 2010 4:18 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] crystal or BAQ gurus




Hi Len, the issue is that I need both descriptions to show on the
report. One is the parent part by which I group and below that the matl
part along with its description. It's obvious that I'm missing
something. Thanks.

Ken

________________________________
From: Len Hartka <len.hartka@...
<mailto:len.hartka%40sunautomation.com> >
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Sent: Fri, April 9, 2010 1:06:13 PM
Subject: RE: [Vantage] crystal or BAQ gurus

Good Day Ken:

Link PartMatl to Part table but through the matl part# in PartMatl
not Parent-Part# , then get the description from the Part table?

len.hartka@sunautom ation.com

____________ _________ _________ __

From: vantage@yahoogroups .com [mailto:vantage@yahoogroups .com] On
Behalf
Of Ken Taylor
Sent: Thursday, April 08, 2010 5:46 PM
To: vantage@yahoogroups .com
Subject: [Vantage] crystal or BAQ gurus

Our Part description are about 2-3 lines long sometimes. I can't seem to
modify the Data definition to pickup more than 30 characters, Epicor
tells me I need the SDK package, so I'm trying to run my report off
BAQs. My dilemma is that from the PartMtl I can only get the description
of either the parent part or the material part. How can I get the
description for both under one BAQ? It's driving me nuts and I'm sure
there is an easy answer to this. Thanks for your help.

Ken

[Non-text portions of this message have been removed]

This e-mail and any attachments may contain proprietary and/or
confidential information. If you are not the intended recipient, please
notify the sender immediately by reply e-mail or at 410-472-2900 and
then delete the message without using, disseminating, or copying this
message or any portion thereof. With e-mail communications you are urged
to protect against viruses.

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]






This e-mail and any attachments may contain proprietary and/or confidential information. If you are not the intended recipient, please notify the sender immediately by reply e-mail or at 410-472-2900 and then delete the message without using, disseminating, or copying this message or any portion thereof. With e-mail communications you are urged to protect against viruses.


[Non-text portions of this message have been removed]
13
Where are you trying to get longer descriptions? if in a Crystal report, simply right click on the field, select format filed, go to common tab, then select or click in the "can Grow" check box.

--- In vantage@yahoogroups.com, Ken Taylor <ktaylorv@...> wrote:
>
> Our Part description are about 2-3 lines long sometimes. I can't seem to modify the Data definition to pickup more than 30 characters, Epicor tells me I need the SDK package, so I'm trying to run my report off BAQs. My dilemma is that from the PartMtl I can only get the description of either the parent part or the material part. How can I get the description for both under one BAQ? It's driving me nuts and I'm sure there is an easy answer to this. Thanks for your help.
>
>
> Ken
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
14
I tried this and returned zero records. Can you paste the BAQ please?


--- In vantage@yahoogroups.com, Roel Martinez <rmartinez@...> wrote:
>
> Hello Ken, all.
>
> When you are linking your tables in Phrase Builder, link PartMtl to Part but only through Company; this would bring all the Part table's records if you did not limited in the criteria, right? Well, on the criteria for the Part table, you enter the following 2 items:
>
>
> * PartMtl.PartNum = Part.PartNum
> OR
>
> * PartMtl.MtlPartNum = Part.PartNum
>
> This will only bring 2 records from the Part Table... Hope it helps, because yes...it will create duplicates, but you can address them at Crystal level.
>
> Regards.
>
> Roel Martinez
> ERP Analyst
>
> SABRE TUBULAR STRUCTURES (tm)
> www.sabretubularstructures.com<http://www.sabretubularstructures.com/>
> 8653 E. Hwy 67 | Alvarado, TX 76009
> Phone: 817-852-1700 ext. 11858 | Direct Line: 817-852-1858 |Fax: 817-852-1705
>
>
>
>
>
> [Non-text portions of this message have been removed]
>