Hello.
I’ve been requested to create a report that shows Model, Serial #, Revision, Company, etc.
I am having trouble with two things:
Any help would be appreciated.
Thank you.
Erp.SerialNo is your base table. It holds SN, Part# and many other fields you can key from. You can get Part Rev by linking JobNum or LotNum (and then PartTran) to the corresponding master tables. I generally don’t bother with revision once something hits inventory, since we don’t track that (and don’t have an ERP version that supports inventory revs). Everything built to a single PartNum should be interchangeable anyway. If not, reroll the part number.
The SerialNo table has three key fields: Company, PartNum and SerialNumber, since you can have a Part A SN#1 and a Part B SN#1. If you’re multicompany, you’ll have to provide all 3 keys to look up a specific serial number.
Hello John,
Thank you.
Do you know where Model would be?
I am looking to do a whole report like of the month with whatever was shipped out during that time, not just one… I am very new to this, I apologize – I don’t know how to create the table criteria. I mean I know how to create one, but not how to word it such that it shows the data I am looking for.
Best regards,
Michele
If you use model number, you have to figure out what the field is. I don’t think model is stored in any common tables. To help you figure this out, go to a form, any form, where you can see a model number. Click Help > Field Help > Technical Details. Click on your model field, and this should show you where that field is stored in the database. This doesn’t always work but is a great place to start.
If you can do this and tell us what field your model number is stored in, then we can help you get closer with your BAQ.
Hello Nate,
After asking around, it looks like we don’t have a Model field. This is fine, I can do without it.
Now I just need help with the criteria wording.
Thank you!
Michele
For your criteria.
What are you trying to report on? Everything in the SerialNo table?
It should be pretty simple. Somethign like this will get you started:
select
[SerialNo].[SerialNumber] as [SerialNo_SerialNumber],
[SerialNo].[SNStatus] as [SerialNo_SNStatus],
[JobAsmbl].[JobNum] as [JobAsmbl_JobNum],
[JobAsmbl].[AssemblySeq] as [JobAsmbl_AssemblySeq],
[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
[JobAsmbl].[Description] as [JobAsmbl_Description],
[JobAsmbl].[RevisionNum] as [JobAsmbl_RevisionNum],
[Part].[TypeCode] as [Part_TypeCode]
from Erp.SerialNo as SerialNo
inner join Erp.JobAsmbl as JobAsmbl on
SerialNo.Company = JobAsmbl.Company
and SerialNo.JobNum = JobAsmbl.JobNum
and SerialNo.AssemblySeq = JobAsmbl.AssemblySeq
inner join Erp.Part as Part on
JobAsmbl.Company = Part.Company
and JobAsmbl.PartNum = Part.PartNum
Here I am starting with the Serial table. I linked it up to Job Asmbl, and Part to get the fields you wanted. Thankfully there were dictionary mappings for these tables, so you don’t have to manually link them. I actually don’t have any serial numbers so I can’t do a lot of testing on my side to get the perfect BAQ for you. As @knash said, What exactly do you want to appear in the report. Setting expectations is half the problem! 
Hello Nate and Knash,
I’d like this to populate with say a month’s worth of information. By that I mean, any that were shipped out this month or any particular month. So these filled out:
Thank you!
Michele
Hard to tell what your query looks like from this.
If you start and just use the SerialNo table what is coming back?
Hi Michele
In order to get the ship Date, you might want to Join the ShipHead Table, then define Parameters to filter the data. Are you familiar with SQL syntax at all ?
Dean
Just to clarify, do you want just serial numbers shipped or all items shipped in a given time frame ?
Hello Dean,
My understanding of SQL is very basic. I would need help setting up the Query Parameters as seen here:
To answer your question, for example I want the serial numbers of all items shipped out in February.
Thank you,
Michele
On another note, I did manage to populate the analyze…
It only works if I don’t have the Manufacturer table., though. I am not sure why.
Here is the Parameter screen
click the new icon ( red Arrow)
fill in items as below
Click new again
Fill as below
Save and close screen
It depends on what join ( table relationship) you set up. Shouldn’t need the Manufacturer table for this example.
SN_Shipped_Param.baq (22.5 KB)
Here is a sample that may help . You would have to import it while in BAQ designer
Dean
