I’m very new to the BAQ world and I’m trying to add either a SubQuery or Join. In the TopLevel query I have calculated field (KnockdownTag) that is created from the OrderComment field in OrderDtl table for a specific OrderDtl line. I’m trying to find the PartNum from an associated OrderDtl line (same OrderNum) where the KnockdownTag value is found in the OrderComment field. Example:
OrderComment OrderNum OrderLine PartNum
DOAS-1 (D020) 357307 1 OADG020
DOAS-2 (D012) 357307 2 OADG012
DOAS-1 (D020), DOAS-2 (D012) 357307 6 KCC-OADG-KD
DOAS-4 (N648) 357307 7 KCC-OAN-KD
In this example OrderLine 1 would return the PartNum from OrderLine 6 because the OrderComment from OrderLine1 is contained in OrderLine 6. Order Line 2 would return the PartNum from OrderLine 6 as well.
I can write the query using a sub-select in Sql but being new to BAQs, I can’t figure out how to translate my sub-select into a BAQ Subquery. I’ve tried it with both a SubQuery and just joining the OrderDtl table a 2nd time to the query using the following left join:
left join Kinetic.dbo.OrderDtl od2
On od2.Company = jl.Company
And od2.OrderNum = jl.OrderNum
And CHARINDEX(jl.KnockTag, od2.OrderComment) > 0
And CHARINDEX(‘Knock’, od2.LineDesc) > 0
And od2.PartNum like ‘KCC%’
I can’t figure out how to incorporate the CharIndex function into the TableRelations, TableCriteria or SubQueryCriteria. Any help would be appreciated. Thanks
I have a series of jobs that may or may not have another part associated with them. The way the associated parts are linked to the job is via the order. The order will have and additional order detail line with the associated part number. The way these to are linked together is the order detail line associated with the job has a tag number in it’s OrderComment field. The associated part will also have that same tag in its OrderComment field. Where it get tricky is, the associated part’s Order Comment may contain multiple tag values, thus linking it to more than one order detail line.
ok, so if that’s your raw data, can you mock up what you want the output to be? I’m still not quite understanding what you are trying to show here.
Also, are these orders make direct? Or are you making the jobs to stock? If they are make direct, you might be able to use the JobProd table which houses the link for that. (But I’m not 100% sure that’s what you are trying to do)
And maybe this is the tip you need. In the table relations, the drop down helps you fill in stuff, but it’s a free text field, so you can type in your expressions as needed here.
This BAQ is going to be used in a dashboard that shows job scheduling by production line. The results will show both job that have been scheduled in Kinetic and those jobs that are still waiting to be scheduled.
So I think you should be able to put what you need into the joins then by typing your stuff into the joins in the place I showed in the screen shot above then, no?
If you can’t get there with the joins then you can add blank calculated fields, make the baq updateable and then using C# fill in the data post processing in GetList.
I have a calculated field in the TopLevel query called KnockdownTag that strips the actual tag value from the OrderComment field. So 'Tag(s): DOAS-1 (D020) becomes DOAS-1 (D020). Using the Calculated field I’m not sure how you’d be able to use the CharIndex statements in the BAQ. I can’t use the Table Relations because I can’t see the calculated field and the Table Criteria new button is disabled therefore my only choice is SubQuery Criteria. I can select the calc field from the Table/Field dropdowns but I don’t see what operation to use. Would I use the Contains operation and then have to do a Substring that incorporates the CharIndex to find the necessary portion of the value?
bring in the subqeury as a table on the top level, and do a join there. I don’t know why my button doesn’t actually have the icon, but that’s the button for that.
Oh, this is relevant to my interests! “Standard” options that configurably modify “standard” products ending up on separate order lines sometimes turns out to be a harder habit to solve than to work around.
This is a job for recursion. Treat it like a delimited split operation, iterating on the remainder of the text after each time you extract the first instance. Then you have one instance per row that you can return to tidy up and join on your other data.
I’m like a lost ball in tall weeds here. Here is what I have
I’m only wanting the SubQuery(KnockDownCurb/OrderDtl) to return a PartNum for those rows whose OrderComment contains the calculated field value and whose LineDesc contains the value of Knock in it. That part I get, because is a constant value. What I don’t get is the part of whic I’m guessing is a SubQuery Criteria because it’s the only one of the three that I can even see the Calculated fields in.
