Skip to Content

multiple selecions from one tale

I have a table that lists out what operations that any given part needs to go through including outside vendors. What I am looking to do, is create a report where a particular operation (laser) is open and another operation must go to an outside vendor. So I wrote this, this works to determine if the job is at the laser.

{Job_Operation.Operation_Service} like "045" and
not ({Job_Operation.Status} in ["c", "t"])

When I add in the other part: and not {Job_Operation.Inside_Oper}, I get nothing on the report. I think it is looking at the laser operation and trying to see if any of those are outside services, which they are not.

How can I get it to look at the other operations to see if they are outside services?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Aug 11, 2017 at 04:00 PM

    There is no way to do this while just joining tables in Crystal because you need to look at multiple records records to get your data. The only way I know of to do this would be to use a Command (SQL Select Statement). See this blog for good information about how to work with commands:

    If you just need the information about the laser operation, your query might look something like this:

    Select <all the fields for your report>
    from Job_Operation jo
    where jo.Operation_Service = '045'
    and jo.Status = 'open'
    and exists (Select "X"
    from Job_Operation jo1
    where jo1.Status in ('c', 't'))

    If you need the information from the laser operation AND any external operations, it might look like this:

    Select <all fields for your report>
    from Job_Operation jobLaser
    inner join Job_Operation jobOutside
    on jobLaser.job_number = jobOutside.job_number
    and jobOutside.Status in ('c','t')
    where jobLaser.Operation_Service '045'
    and jobLaser.Status = 'open'

    Depending on what type of database you're using, there may be some more efficient ways of querying this data as well.


    Add comment
    10|10000 characters needed characters exceeded

    • I can work with this, but I have a couple of questions

      1. Do you need all of the records from Delivery regardless of whether they have an associated Job? This is how the query is currently working.

      2. You're using "like '045'", but there are no wildcards on this so it should only pull records with the exact value "045". Is that correct?