Serial Tracking BAQ

Kinetic ERP
1

Hello,

I am creating a BAQ. I have two issues/questions in regards to it.

  1. I am having difficulty locating the following display columns: Ship To Name (not ID) & Assembly Description.

  2. 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?

Thank you,
Michele

2

Good morning,
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.
Good luck!
Nate

1 Like
3

Good morning,

Thank you for all your help so far.

I am looking throughout the Customer table and can’t find a Ship To Name.

4

In the customer table you link to CustNum.

5

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.

Thank you!

6

You will also need to connect the Erp.ShipTo table to get the Ship To Name
image

7

Hello,

Thank you!

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)

8

Let’s start over. What is your business goal with this report?

9

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.

10

try linking it this way

image
image1460Ă—737 34.4 KB

11

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.”

12

We might be able to help if you post the Query Phrase from your BAQ.

13 
select 
	[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)

14

If you remove the start and end date parameters, does the BAQ run?

15

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.

16

There may also be a problem with the JobAsmbl linking on the ShipTo

17

I disconnected the two and moved JobAsmbl to connect with SerialNo.

image
image1468Ă—127 3.57 KB

18

Any luck getting it to run?

19

Still does the same as described above.

20

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.

1 Like