Skip to Content
0

multiple selecions from one tale

Aug 11, 2017 at 03:29 PM

41

avatar image

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?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Dell Stinnett-Christy Aug 11, 2017 at 04:00 PM
0

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: https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/

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.

-Dell

Show 4 Share
10 |10000 characters needed characters left characters exceeded

The info I need is it at the laser and does it need to go to a outside service so I'm guessing #2 is what I should use.

0

If you're not comfortable writing SQL, I can help you write it if you do the following:

1. Let me know what type of database you're connecting to - Commands use the syntax from the database, not the syntax from Crystal.

2. Provide the current SQL that Crystal has created for the report - Go to "Show SQL Query..." on the Database menu. Copy the query and then paste it in your response.

3. Provide the formula from the Select Expert - sometimes not all of the filter gets put in the query.

-Dell

0

we are using sql server database the sql statement is first

SELECT "Job"."Job", "Job"."Customer", "Job"."Part_Number", "Job"."Order_Quantity", "Job"."Unit_Price", "Job"."Customer_PO", "Job"."Shipped_Quantity", "Job"."Priority", "User_Values"."Date1", "User_Values"."Date2", "Job"."Description", "Job"."Order_Date", "Job"."Released_Date", "Delivery"."Promised_Date", "Delivery"."Remaining_Quantity", "Job_Operation"."Operation_Service", "Job_Operation"."Status", "Job"."Note_Text"
FROM ("JBPCI"."dbo"."Delivery" "Delivery" LEFT OUTER JOIN ("JBPCI"."dbo"."Job_Operation" "Job_Operation" LEFT OUTER JOIN "JBPCI"."dbo"."Job" "Job" ON "Job_Operation"."Job"="Job"."Job") ON "Delivery"."Job"="Job"."Top_Lvl_Job") LEFT OUTER JOIN "JBPCI"."dbo"."User_Values" "User_Values" ON "Job"."User_Values"="User_Values"."User_Values"
WHERE NOT ("Job_Operation"."Status"='c' OR "Job_Operation"."Status"='t') AND "Job_Operation"."Operation_Service" LIKE '045' AND "Delivery"."Remaining_Quantity">0
ORDER BY "Job"."Customer"

select Expert has this

not ({Job_Operation.Status} in ["c", "t"]) and
{Job_Operation.Operation_Service} like "045" and
{Delivery.Remaining_Quantity} > 0 and
not {Job_Operation.Inside_Oper}

I know I could do the qry through Access, but my boss wants everything done in crystal possible, so I have to work within the constraints of Crystal.

Not sure if this would help, but I also have a sub report attached with those outside services listed, if it is easier to use that data.

0

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?

Thanks!

-Dell

0