BAQ Help with Summarized table - 8.03.407

Yahoo Archive
1
Hi Keith,

>
>How do a run Conversion program 6430?
>

On your server is a link called "Admin Tools". You log in as manager and then select Run Conversion Programs. The scroll bar is wacked. You can't just grab the square and pull it down to see all of the programs - you have to click on the down arrow in the scroll-bar. (At least, that's been my experience) Find 6430 and run it. You can run from the menu or right-mouse click it. . All it does is read the bin records and updates the totals in the PartWhse (and PartDtl?) records. Epicor recommends running it while there's no activity and depending how it's coded, that might be a good idea.

Mark W.
2
I am trying to write a BAQ that shows open SO releases, and total quantity on hand. Here is my query:

for each OrderHed no-lock , each OrderRel where ( OrderRel.OpenRelease = true) and (OrderHed.Company = OrderRel.Company and OrderHed.OrderNum = OrderRel.OrderNum) no-lock , each Customer where (OrderHed.Company = Customer.Company and OrderHed.CustNum = Customer.CustNum) no-lock , each PartBin where ( PartBin.WarehouseCode = '120' OR PartBin.WarehouseCode = '320') and (OrderRel.Company = PartBin.Company and OrderRel.PartNum = PartBin.PartNum) no-lock by OrderRel.ReqDate by OrderRel.PartNum.

To get the total quantity on hand, I am summarizing the PartBin table, then using a formula (calc field) to display data. Here is my formula:

Total(PartBin.OnhandQty)


My issue is in the BAQ's "summarizing". for every SO release that has the same part, it adds to the Total(PartBin.OnhandQty) result.

For instance if I have two seperate orders for part ABC, and I have a total of 10 parts on hand, the query will tell me 20 on hand... if three order/releases I get 30 parts on hand.

Does anyone know a work-around for this? Should I structure my query differently? Is there a better place to get total QOH where I do not have to summarize?

Thanks,
Keith Walter
3
Keith,

I don't believe there is a better join type you could use at the BAQ to get the results you're looking for. Summarized tables can be a little goofy.

As you likely are already aware, Vantage supports three types of Crystal-based custom reports; BAQ Reports, Dashboard Reports and Custom Reports. Since you are starting with a BAQ I assume you are planning to use either a Dashboard Report or BAQ report (or maybe just a Dashboard).

What you need is a sub-report within your Crystal Report (BAQ Report or Dashboard Report) or a 'subscribing' query within your Dashboard.

A Dashboard report may be your best option if you don't want to use a Custom Report Link in Vantage. The Dashboard report can utilize multiple Queries from the Dashboard in the report allowing you to use your Sales Order Release Query as the top level report and use a secondary BAQ for PartBin from the same Dashboard as a Sub Report.

If you have questions about how to do any of this, feel free to contact me directly.

Nathan Bonner
bonner.n@
totalplastics.com
269.553.5838

--- In vantage@yahoogroups.com, "Keith" <keithfwalter@...> wrote:
>
> I am trying to write a BAQ that shows open SO releases, and total quantity on hand. Here is my query:
>
> for each OrderHed no-lock , each OrderRel where ( OrderRel.OpenRelease = true) and (OrderHed.Company = OrderRel.Company and OrderHed.OrderNum = OrderRel.OrderNum) no-lock , each Customer where (OrderHed.Company = Customer.Company and OrderHed.CustNum = Customer.CustNum) no-lock , each PartBin where ( PartBin.WarehouseCode = '120' OR PartBin.WarehouseCode = '320') and (OrderRel.Company = PartBin.Company and OrderRel.PartNum = PartBin.PartNum) no-lock by OrderRel.ReqDate by OrderRel.PartNum.
>
> To get the total quantity on hand, I am summarizing the PartBin table, then using a formula (calc field) to display data. Here is my formula:
>
> Total(PartBin.OnhandQty)
>
>
> My issue is in the BAQ's "summarizing". for every SO release that has the same part, it adds to the Total(PartBin.OnhandQty) result.
>
> For instance if I have two seperate orders for part ABC, and I have a total of 10 parts on hand, the query will tell me 20 on hand... if three order/releases I get 30 parts on hand.
>
> Does anyone know a work-around for this? Should I structure my query differently? Is there a better place to get total QOH where I do not have to summarize?
>
> Thanks,
> Keith Walter
>
4
If you are going with Crystal, you can use "Running Totals" to sum at any level you want (Release, Bin, Part, etc.), too.

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

[cid:image001.jpg@01CAB13C.D4235580]

Sabre Industries, Inc. ("Sabre") E-MAIL NOTICE - This transmission and any attachments may be confidential or subject to a legally recognized privilege and is intended only to be received and read by the proper party having a reason to send communications to or from Sabre. If you do not believe that you are the proper recipient of this message because of its address, contents and/or otherwise believe that this message was sent to you in error, you should not disclose, print, copy or disseminate the information contained in this message. If you have received this in error, please reply and notify the sender (only) and delete the message. Unauthorized interception of this e-mail is a violation of federal criminal law and state laws.
The text in this communication does not reflect an intention by Sabre to conduct a transaction or make any agreement by electronic means. Only a document which bears a handwritten signature of an authorized signatory of Sabre shall be binding upon Sabre and such documents may be attached indicating a desire to conduct a transaction or make any agreement. Nothing contained in the text of this message shall satisfy the requirements for a writing, and nothing contained herein shall constitute a contract or electronic signature under the Electronic Signatures in Global and National Commerce Act, any version of the Uniform Electronic Transactions Act or any other statute governing electronic transactions or any statutory adoptions or equivalents of the aforementioned acts and convention, and any other laws or regulations of any state or country related to electronic contracts, electronic signatures, or electronic records shall not apply to Sabre or the recipient.




[Non-text portions of this message have been removed]
5
thanks guys. I am using this on a Dashboard, and I already have a bunch of publish/supbsribes. I want to get the QOH into this query, so that I can do a row-rule and highlite the QOH feild if it is less then the open SO qty. Otherwise you would have to click on each line to see the total in another grid right?

Is there another table that summarizes netable QOH that I can grab?

thanks again,
Keith
6
Keith,

PartWhse has OnHandQty but, as it is an accumulated field with base code that is a little squirrely at best (Epicor has a conversion program that we run every few days to keep PartWhse in sync with PartBin), I would be reluctant to make any business-critical decisions based on it.

NB

--- In vantage@yahoogroups.com, "Keith" <keithfwalter@...> wrote:
>
> thanks guys. I am using this on a Dashboard, and I already have a bunch of publish/supbsribes. I want to get the QOH into this query, so that I can do a row-rule and highlite the QOH feild if it is less then the open SO qty. Otherwise you would have to click on each line to see the total in another grid right?
>
> Is there another table that summarizes netable QOH that I can grab?
>
> thanks again,
> Keith
>
7
Hi Keith,

>thanks guys. I am using this on a Dashboard, and I already have a bunch of publish/supbsribes.
> I want to get the QOH into this query, so that I can do a row-rule and highlite the QOH feild if it
> is less then the open SO qty. Otherwise you would have to click on each line to see the total
> in another grid right?
>
>Is there another table that summarizes netable QOH that I can grab?

Check PartDtl or PartWarehouse. Warning: this can get out of sync at times and you may want to run Conversion program 6430 to make sure the values are good,

Mark W.
8
How do a run Conversion program 6430?

Thanks, Keith

--- In vantage@yahoogroups.com, Mark Wonsil <mark_wonsil@...> wrote:
>
>
> Hi Keith,
>
> >thanks guys. I am using this on a Dashboard, and I already have a bunch of publish/supbsribes.
> > I want to get the QOH into this query, so that I can do a row-rule and highlite the QOH feild if it
> > is less then the open SO qty. Otherwise you would have to click on each line to see the total
> > in another grid right?
> >
> >Is there another table that summarizes netable QOH that I can grab?
>
> Check PartDtl or PartWarehouse. Warning: this can get out of sync at times and you may want to run Conversion program 6430 to make sure the values are good,
>
> Mark W.
>