Help with BAQ Query

RC2020 · February 3, 2021, 6:44pm

Hello Everyone,

I need assistance in creating a BAQ that will pull the job information shown on the purchase order screen. I see the job and the table used when I select the Purchase Header and Job Header there doesn’t seem to have any relation so the query just bombs. (See below) information trying to capture with Purchase order information.

If someone can explain or show me how to make the relationship. I would greatly appreciate it.

Thanks in Advance

Miguel

ckrusen · February 3, 2021, 6:47pm

There won;t be any Job info in the PO Header. It will be in the PODtl or PORel.

Vinaykamboj · February 3, 2021, 8:02pm

PODtl is linked to SORel

Vinay Kamboj

Joseph_Martin · February 3, 2021, 8:19pm

The links your are looking for are POHeader -> PORel -> JobMtl or JobOper -> Job Head.
Subcontract operation POs link to a JobOper OprSeq and Job Material POs link to JobMtl AssemblySeq and MtlSeq.

RC2020 · February 4, 2021, 3:27pm

Thanks I got it working now. How do I remove repeating information? I am getting duplicate entries for every line.

Thanks In Advance.

Miguel

ckrusen · February 4, 2021, 3:52pm

Can you post the BAQ as you currently have it? Show the designer sheet that shows the tables graphically linked. And then click the links to show the relationships.

RC2020 · February 4, 2021, 5:32pm

Hello Calvin,

Below is my designer sheet

Thank you.

Miguel

RC2020 · February 4, 2021, 6:36pm

Hello Calvin,

You can see my BAQ (shown below) is duplicating every field except the OpDesc which is weird since the operation and OpDesc are pulling from the same table. In addition, I would expect the description would be the same.

I would appreciate any assistance you can provide to stop the duplication.

Thank you.

Miguel

Joseph_Martin · February 4, 2021, 6:55pm

My guess is your duplication is probably due to the JobOpDtl join. If you do not need that table then join PORel.JobNum, AssemblySeq, JobSeq to JobOper.JobNum, AssemblySeq, OprSeq

ckrusen · February 4, 2021, 8:00pm

I’d make sure AssySeq is included in the joins

RC2020 · February 5, 2021, 9:42pm

Hello Calvan,

I have the information and I added the AssemblySeq, but still getting the duplicates of 4 each.
See below.

My selection criteria is for 1 purchase order with 2 lines on the PO for operations 100 and 130.

I would appreciate any assistance.

Thank you.

Miguel

gpayne · February 5, 2021, 11:07pm

@RC2020 I think in 9 picking first from the which item drop down would fix this.

RC2020 · February 5, 2021, 11:27pm

Hello Greg,

I made the change shown below.

Below is my output, it is still replicating the same entry 3 more times and the second line is now not appearing since I say only show the first record.
.

Thank you for your assistance.

Regards,

Miguel

gpayne · February 5, 2021, 11:31pm

@RC2020 Try first on JobOpDtl

RC2020 · February 5, 2021, 11:36pm

Hello Greg,

Now the BAQ displays nothing. (See below)

Thanks for your assistance.

Miguel

gpayne · February 7, 2021, 3:16am

@RC2020 Make sure each is on PODetail. Join PORel to JobOper. Join JobOper to JobHead and do First on JobOprDtl.

ckrusen · February 7, 2021, 1:31pm

@RC2020 - back when just had two rows when you expected one, the last field was showing up different

You need to figure what table relationship you’re missing that is pulling records from two different operations