Thank you for your good analysis of the baq.
You bring up a point I didn't think of that the mtl with a part not in the part master will be ignored.
The reason I made the part table as master and summarized the other tables is because I want to have one record per part with total qty required and total qty available instead of having multiple records per part.
I want to build the dashboard to have on top all the parts needed with the parts that have low availability highlighted then the bottom will have the list of quotes needing that part.
Is there another way to reach that result without using the part file as master? Using the summary on the dashboard doesn't do it for me.
Why would you recommend having the job filter instead of a baq filter? Is it faster? Cleaner?
Thanks again for the insight.
Ephraim
Ephraim Feldman
-----Original Message-----
From: "CharlieSmith" <CSmith@...>
Date: Wed, 26 May 2010 17:53:29
To: <vantage@yahoogroups.com>
Subject: RE: [Vantage] Problem with a BAQ
Your BAQ:
for each Part no-lock ,
each QuoteMtl where (Part.Company = QuoteMtl.Company and Part.PartNum =
QuoteMtl.PartNum) no-lock ,
each PartWhse where (Part.Company = PartWhse.Company and Part.PartNum =
PartWhse.PartNum) no-lock ,
each OrderDtl where
( OrderDtl.OpenLine = TRUE) and
(QuoteMtl.Company = OrderDtl.Company and
QuoteMtl.QuoteNum = OrderDtl.QuoteNum and
QuoteMtl.QuoteLine = OrderDtl.QuoteLine) no-lock ,
each JobProd outer-join where
( JobProd.JobNum = '') and
(OrderDtl.Company = JobProd.Company and
.OrderNum = JobProd.OrderNum and
OrderDtl.OrderLine = JobProd.OrderLine) no-lock .
I'm sure there is a reason for the part master but you will not get
materials in a quote that is not in the part master that way.
The only problem I have had with my version of the same query is that
you can't limit the material records on the job prod criteria.
Here is my version:
for each QuoteMtl no-lock ,
each Part outer-join where
[Outer join to get all quote materials]
(QuoteMtl.Company = Part.Company and
QuoteMtl.PartNum = Part.PartNum) no-lock ,
each OrderDtl where
[not an outer join because you don't want materials that are not
associated with an order]
(OrderDtl.OpenLine = TRUE) and
(QuoteMtl.Company = OrderDtl.Company and
QuoteMtl.QuoteNum = OrderDtl.QuoteNum and
QuoteMtl.QuoteLine = OrderDtl.QuoteLine) no-lock ,
each JobProd outer-join where
[Outer Join because you want to examine the job information]
(OrderDtl.Company = JobProd.Company and
OrderDtl.OrderNum = JobProd.OrderNum and
OrderDtl.OrderLine = JobProd.OrderLine) no-lock ,
each PartWhse outer-join where
(Part.Company = PartWhse.Company and
Part.PartNum = PartWhse.PartNum) no-lock .
This will get the information you want except for excluding on the job
ID. When you put this to the dashboard, filter to exclude the job prod
data that has a job ID in it.
If this isn't what you are looking for (I don't know what you are
summarizing or why), you could try some variations but you will still
need to filter at the dashboard, BAQ report or BAQ export.
Charlie Smith
Smith Business Services / 2W Technologies LLC
www.vistaconsultant.com <http://www.vistaconsultant.com/> /
www.2WTech.com
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of efyf_1999
Sent: Wednesday, May 26, 2010 4:02 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Problem with a BAQ
Hi Group,
I have a BAQ that i run to find for each part the BOM qty from the
QuoteMtl but i only want to find the records that have a order created
but no job linked. (find below the BAQ)
The problem is that if i range on up to 1000 quotes it works but if i
dont range on quotes or i range on a couple of thousand than it rund
very long and then i get the error below.
Can someone help me !!!! Wasted alot of time to find that the BAQ works
but not on the whole list of quotes.
This is the BAQ.
Please note that all the tables are summarized beside the Part table.
for each Part no-lock , each QuoteMtl where (Part.Company =
QuoteMtl.Company and Part.PartNum = QuoteMtl.PartNum) no-lock , each
PartWhse where (Part.Company = PartWhse.Company and Part.PartNum =
PartWhse.PartNum) no-lock , each OrderDtl where ( OrderDtl.OpenLine =
TRUE) and (QuoteMtl.Company = OrderDtl.Company and QuoteMtl.QuoteNum =
OrderDtl.QuoteNum and QuoteMtl.QuoteLine = OrderDtl.QuoteLine) no-lock ,
each JobProd outer-join where ( JobProd.JobNum = '') and
(OrderDtl.Company = JobProd.Company and OrderDtl.OrderNum =
JobProd.OrderNum and OrderDtl.OrderLine = JobProd.OrderLine) no-lock .
This is the error im getting
Tried to call a method in an invalid procedure. (7224)
at Progress.Open4GL.DynamicAPI.PersistentProc.validate()
at Progress.Open4GL.DynamicAPI.PersistentProc.runProcedure(String
procedureName, ParameterSet parms, MetaSchema schema)
at Epicor.Mfg.Proxy.GenXDataImpl.StoreData(GenXDataDataSet ds)
at Epicor.Mfg.UI.FrameWork.GenXObject.handleChunkAndSave(Boolean
doCompression, String company, String productID, String typeCode, String
key1, String key2, String key3, String description, String version,
Boolean layerWIP, String data)
at Epicor.Mfg.UI.FrameWork.GenXObject.ChunkNSaveStringByID(String
company, String productID, String typeCode, String key1, String key2,
String key3, String description, String version, Boolean layerWIP,
String data)
at Epicor.Mfg.UI.FrameWork.FormFunctions.ChunkNSaveStringByID(Object
sender, String company, String productID, String typeCode, String key1,
String key2, String key3, String description, String version, Boolean
layerWIP, String data)
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
You bring up a point I didn't think of that the mtl with a part not in the part master will be ignored.
The reason I made the part table as master and summarized the other tables is because I want to have one record per part with total qty required and total qty available instead of having multiple records per part.
I want to build the dashboard to have on top all the parts needed with the parts that have low availability highlighted then the bottom will have the list of quotes needing that part.
Is there another way to reach that result without using the part file as master? Using the summary on the dashboard doesn't do it for me.
Why would you recommend having the job filter instead of a baq filter? Is it faster? Cleaner?
Thanks again for the insight.
Ephraim
Ephraim Feldman
-----Original Message-----
From: "CharlieSmith" <CSmith@...>
Date: Wed, 26 May 2010 17:53:29
To: <vantage@yahoogroups.com>
Subject: RE: [Vantage] Problem with a BAQ
Your BAQ:
for each Part no-lock ,
each QuoteMtl where (Part.Company = QuoteMtl.Company and Part.PartNum =
QuoteMtl.PartNum) no-lock ,
each PartWhse where (Part.Company = PartWhse.Company and Part.PartNum =
PartWhse.PartNum) no-lock ,
each OrderDtl where
( OrderDtl.OpenLine = TRUE) and
(QuoteMtl.Company = OrderDtl.Company and
QuoteMtl.QuoteNum = OrderDtl.QuoteNum and
QuoteMtl.QuoteLine = OrderDtl.QuoteLine) no-lock ,
each JobProd outer-join where
( JobProd.JobNum = '') and
(OrderDtl.Company = JobProd.Company and
.OrderNum = JobProd.OrderNum and
OrderDtl.OrderLine = JobProd.OrderLine) no-lock .
I'm sure there is a reason for the part master but you will not get
materials in a quote that is not in the part master that way.
The only problem I have had with my version of the same query is that
you can't limit the material records on the job prod criteria.
Here is my version:
for each QuoteMtl no-lock ,
each Part outer-join where
[Outer join to get all quote materials]
(QuoteMtl.Company = Part.Company and
QuoteMtl.PartNum = Part.PartNum) no-lock ,
each OrderDtl where
[not an outer join because you don't want materials that are not
associated with an order]
(OrderDtl.OpenLine = TRUE) and
(QuoteMtl.Company = OrderDtl.Company and
QuoteMtl.QuoteNum = OrderDtl.QuoteNum and
QuoteMtl.QuoteLine = OrderDtl.QuoteLine) no-lock ,
each JobProd outer-join where
[Outer Join because you want to examine the job information]
(OrderDtl.Company = JobProd.Company and
OrderDtl.OrderNum = JobProd.OrderNum and
OrderDtl.OrderLine = JobProd.OrderLine) no-lock ,
each PartWhse outer-join where
(Part.Company = PartWhse.Company and
Part.PartNum = PartWhse.PartNum) no-lock .
This will get the information you want except for excluding on the job
ID. When you put this to the dashboard, filter to exclude the job prod
data that has a job ID in it.
If this isn't what you are looking for (I don't know what you are
summarizing or why), you could try some variations but you will still
need to filter at the dashboard, BAQ report or BAQ export.
Charlie Smith
Smith Business Services / 2W Technologies LLC
www.vistaconsultant.com <http://www.vistaconsultant.com/> /
www.2WTech.com
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of efyf_1999
Sent: Wednesday, May 26, 2010 4:02 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Problem with a BAQ
Hi Group,
I have a BAQ that i run to find for each part the BOM qty from the
QuoteMtl but i only want to find the records that have a order created
but no job linked. (find below the BAQ)
The problem is that if i range on up to 1000 quotes it works but if i
dont range on quotes or i range on a couple of thousand than it rund
very long and then i get the error below.
Can someone help me !!!! Wasted alot of time to find that the BAQ works
but not on the whole list of quotes.
This is the BAQ.
Please note that all the tables are summarized beside the Part table.
for each Part no-lock , each QuoteMtl where (Part.Company =
QuoteMtl.Company and Part.PartNum = QuoteMtl.PartNum) no-lock , each
PartWhse where (Part.Company = PartWhse.Company and Part.PartNum =
PartWhse.PartNum) no-lock , each OrderDtl where ( OrderDtl.OpenLine =
TRUE) and (QuoteMtl.Company = OrderDtl.Company and QuoteMtl.QuoteNum =
OrderDtl.QuoteNum and QuoteMtl.QuoteLine = OrderDtl.QuoteLine) no-lock ,
each JobProd outer-join where ( JobProd.JobNum = '') and
(OrderDtl.Company = JobProd.Company and OrderDtl.OrderNum =
JobProd.OrderNum and OrderDtl.OrderLine = JobProd.OrderLine) no-lock .
This is the error im getting
Error Detail
Tried to call a method in an invalid procedure. (7224)
Stack Trace
at Progress.Open4GL.DynamicAPI.PersistentProc.validate()
at Progress.Open4GL.DynamicAPI.PersistentProc.runProcedure(String
procedureName, ParameterSet parms, MetaSchema schema)
at Epicor.Mfg.Proxy.GenXDataImpl.StoreData(GenXDataDataSet ds)
at Epicor.Mfg.UI.FrameWork.GenXObject.handleChunkAndSave(Boolean
doCompression, String company, String productID, String typeCode, String
key1, String key2, String key3, String description, String version,
Boolean layerWIP, String data)
at Epicor.Mfg.UI.FrameWork.GenXObject.ChunkNSaveStringByID(String
company, String productID, String typeCode, String key1, String key2,
String key3, String description, String version, Boolean layerWIP,
String data)
at Epicor.Mfg.UI.FrameWork.FormFunctions.ChunkNSaveStringByID(Object
sender, String company, String productID, String typeCode, String key1,
String key2, String key3, String description, String version, Boolean
layerWIP, String data)
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]