I am creating a BAQ. I have two issues/questions in regards to it.
I am having difficulty locating the following display columns: Ship To Name (not ID) & Assembly Description.
I am also need to set it up such that it is searchable by date, which I know how to do, but prior to date search is there a way to search by Part Number first?
The Ship To Name is in the Customer table. You will have to join it on ShipToNum (Which is customer num). Assembly description is in the JobAsmbl table. You will have to join it on Job number. If you want to search by part number and date. just enter them both as criteria in the BAQ. If you need the part to search first, you may have to use subqueries.
Thank you for all your help so far.
I am looking throughout the Customer table and can’t find a Ship To Name.
In the customer table you link to CustNum.
I have that set up, but going through the display columns in Customer I can’t find Ship To Name. There’s Customer Name and Ship To Number, just no Ship To Name. Unless I am missing something.
You will also need to connect the Erp.ShipTo table to get the Ship To Name
Now I have a new problem:
When I ask it to Analyze, it says the Syntax is okay. But when I go to test/run, even setting rows to return at 10, it doesn’t load. I have to cancel the query, then it loads. It says “Severity: Error, Text: Bad SQL statement.” I have a feeling my joins are wonky.
SerialTracker.baq (33.3 KB)
Let’s start over. What is your business goal with this report?
A user of mine has requested to see the following: Part Number, Part Description, Serial Number, Serial Number Creation Date, Serial Number Modified Date, Serial Number’s Job, Job Assembly Description, Sales Order associated with the serial number, Customer Name, Customer Ship To Name, and Requested Ship Date of the Sales Order.
I found the proper link to generate Ship To Name, thank you.
Now it’s just a matter of this error: “Severity: Error, Text: Bad SQL statement.”
We might be able to help if you post the Query Phrase from your BAQ.
[Part].[PartNum] as [Part_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[SerialNo].[CreateDate] as [SerialNo_CreateDate],
[SerialNo].[ModifiedDate] as [SerialNo_ModifiedDate],
[SerialNo].[SerialNumber] as [SerialNo_SerialNumber],
[SerialNo].[OrderNum] as [SerialNo_OrderNum],
[Customer].[Name] as [Customer_Name],
[OrderDtl].[RequestDate] as [OrderDtl_RequestDate],
[SerialNo].[JobNum] as [SerialNo_JobNum],
[JobAsmbl].[Description] as [JobAsmbl_Description],
[ShipTo].[Name] as [ShipTo_Name]
from Erp.SerialNo as SerialNo
inner join Erp.Part as Part on
SerialNo.Company = Part.Company
and SerialNo.PartNum = Part.PartNum
inner join Erp.OrderDtl as OrderDtl on
Part.Company = OrderDtl.Company
and Part.PartNum = OrderDtl.PartNum
inner join Erp.Customer as Customer on
OrderDtl.Company = Customer.Company
and OrderDtl.CustNum = Customer.CustNum
inner join Erp.ShipDtl as ShipDtl on
Customer.Company = ShipDtl.Company
and Customer.CustNum = ShipDtl.CustNum
inner join Erp.ShipTo as ShipTo on
ShipDtl.Company = ShipTo.Company
and ShipDtl.CustNum = ShipTo.CustNum
inner join Erp.JobAsmbl as JobAsmbl on
ShipTo.Company = JobAsmbl.Company
where (SerialNo.CreateDate >= @pStartDate and SerialNo.CreateDate <= @pEndDate)
SerialTracker1.baq (34.0 KB)
If you remove the start and end date parameters, does the BAQ run?
Yes. Well… Sort of. It runs but never fills out, I have to hit the X to cancel it after a minute or so, then it populates. Even if I set it to a mere 10 rows. Does the same thing with or without the date parameters.
Analyze says syntax is ok.
There may also be a problem with the JobAsmbl linking on the ShipTo
I disconnected the two and moved JobAsmbl to connect with SerialNo.
Any luck getting it to run?
Still does the same as described above.
Here’s what I would do to get started.
I’d remove all the tables you connected and set a parameter for one particular part number or one serial number. Add fields from the SerialNo table and run the query to see what you get. Once you’re getting data then add another table, add fields from that table and run the query and see what you get. Continue adding tables and fields until you add a table and fields and it breaks. Once you have all the fields you need, then put your date parameter in place and see how it goes.
Also, when you add the OrderDtl table, link that to the SerialNum order number and order line
Link the ShipDtl table to the SerialNum packnum and pack line
Link the JobAsmbl to the SerialNum Job fields.