BAQ - Adding a table with a conditional join


(John Zachow) #1

I have question that somewhat follows this thread
I have an existing BAQ that does quite a bit and I am adding to it and what I need to do is join Job Prod table (existing in BAQ) to a new table (Job Mtl) BUT… I only want to return records that satisfy a condition in the Order Dtl table (is part of the BAQ), SalesCatID= (my thing).

If it does not satisfy this condition, I dont want any job material records

I understand that constructing the BAQ around the Order Details to only get data based on the that condition would work at a glance but remember I have a pretty substantial BAQ that I want to add Job Material info to ONLY if that condition is met (otherwise I get way too much data that I dont want)

This may be a sub query but I don’t understand how to set it up (the ICE documentation I have ready dont help me much on this topic)


(Chris Conn) #2

Look at the subquery criteria


(Tim Shoemaker) #3

One other thing to consider…

  1. Make a calculated field that does something… returning a True/false or “Keep me” or something else you can filter on.
  2. in the Condition, you are not limited to database fields, you can also choose the calculated fields (Click on the table name and you will see CALCULATED as an option. Then you can say "MyCalculatedField equals ‘keep me’)

(Jason Woods) #4

I agree with Tim. often we try to get the BAQ to be “perfect” when the cost of not making it perfect is minimal.
To clarify (if I understand Tim’s meaning), step 1 is in the BAQ, step 2 is in the dashboard filter.


(John Zachow) #5

Jason

Thank you, I missed the boat and was actually just trying it without success (in the BAQ completely)

I think I understand and will give it a try

The BAQ will generate much more data then I need but possibly I can filter out what I don’t want in the DB filter

John Zachow

Vice President, Wire & Cable Systems

P: +1 920.215.6628 | C: +1 860.271.9397

jzachow@davis-standard.com

davis-standard.com

Davis-Standard Logo_4color_Tagline


(Jason Woods) #6

You may be able to do more filtering at the BAQ level too, but without the actual logic, that may be a workable solution.

Jason Woods

Owner | Jason Woods Consulting

jason@jasonwoods.me | Cell: 360.903.4893

www.linkedin.com/in/jasoncwoods

http://jasonwoods.me


(John Zachow) #7

Jason

Great news

You set my in the correct path of understanding Tim’s suggestion

This was the link between the SubQuery1 and SubQuery2 that made it work (not between BAQ and DB)

I dropped it in and it did what I was looking for

Thanks to both of you for your help!!!


(John Zachow) #8

Ok, what is the trick to displaying the fields that I have listed in the subquery2?


(Jason Woods) #9

You will have to add the SubQuery to the TopLevel query and perform a join.


(Tim Shoemaker) #10

You interpreted correctly, but I also was vague for a reason… I have used a calculated field to pass to the Dashboard for further filtering, but also used it in a sub-query and then later filter out the results (not perfect) in an upper query with additional conditions. sometimes its just easier to understand a formula in the subquery and see the results.