Another query puzzle

Yahoo Archive
1

You could hire a consultant to pound it out. If you find a good VAR and create a partnership they may even be willing to provide training on real-world use cases like this.

 

Joshua Giese
Technology Solutions : CXO

Direct Phone:    920.593.8299
Office Phone:    920.437.6400 x342

http://wcibags.com/email/emailFooter4.jpg

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Thursday, July 7, 2016 12:42
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Another query puzzle

 

 

I don't know how to write my own code so I don't have that option. I'm sure I'll be searching out some training soon.

In the meantime, if anyone else has any suggestion through the programmed wizards, that would be great.

2
This is a training/learning project that may be of use if I can get it to work.

So I want to build a simple BPM that throws an exception with the last operation on an assembly is set to backflush before releasing a job. I ran the trace and am planning on running a pre-processing BPM to the Erp.JobEntry.CheckResourcePlants that fires when releasing a job.

I already have a query and dashboard tab written that finds the qty reporting operation (either the last operation or the operation marked as final assembly as shown in the JobAsm table), and then checks whether they are set to backflush or not. What I was planning to do was use the "Number of rows in the designed query is greater than 0" to throw an exception if the query finds an error.

The problem arises with the fact that the designed query in the BPM does not allow for sub queries, as far as I can tell. My original query has a sub query to find the highest number operation on an assembly (from the jobopr table) or the operation marked as final op (from the JobAsm table, if one is set). This calculated field would then be used to filter the JobOpr table to only show the qty reporting operation and see if that operation is set to backflush.

So for me to get the BPM to work, I need to figure out how to filter the operations down to the reporting operations on the job, and check whether they are backflush or not, all on a single level.


Any ideas?

3

Not sure what version you’re on, etc… but you can do an outer join to the last operation  and also do an outer join to the JobAsm flagged last operation then compare the two and pick the one you want.

 

Patrick Winter

 

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Thursday, July 07, 2016 11:39
To: vantage@yahoogroups.com
Subject: [Vantage] Another query puzzle

 

 

This is a training/learning project that may be of use if I can get it to work.

So I want to build a simple BPM that throws an exception with the last operation on an assembly is set to backflush before releasing a job. I ran the trace and am planning on running a pre-processing BPM to the Erp.JobEntry.CheckResourcePlants that fires when releasing a job.

I already have a query and dashboard tab written that finds the qty reporting operation (either the last operation or the operation marked as final assembly as shown in the JobAsm table), and then checks whether they are set to backflush or not. What I was planning to do was use the "Number of rows in the designed query is greater than 0" to throw an exception if the query finds an error.

The problem arises with the fact that the designed query in the BPM does not allow for sub queries, as far as I can tell. My original query has a sub query to find the highest number operation on an as sembly (from the jobopr table) or the operation marked as final op (from the JobAsm table, if one is set). This calculated field would then be used to filter the JobOpr table to only show the qty reporting operation and see if that operation is set to backflush.

So for me to get the BPM to work, I need to figure out how to filter the operations down to the reporting operations on the job, and check whether they are backflush or not, all on a single level.

 

Any ideas?

This e-mail and any attachments may contain confidential and privileged information. If you are not the intended and/or named recipient or recipients, please notify the sender immediately by return e-mail, delete this e-mail and destroy any copies. Any dissemination or use of this information by a person other than the intended recipient or recipients is unauthorized and may be illegal. Any views or opinions expressed in this email are those of the author and do not necessarily represent those of the Specialty Screw Corporation. Warning: Although precautions have been taken to make sure no viruses are present in this email, Specialty Screw Corporation cannot accept responsibility for any loss or damage that may arise from the use of this email or attachments.
This e-mail and any attachments may contain confidential and privileged information. If you are not the intended and/or named recipient or recipients, please notify the sender immediately by return e-mail, delete this e-mail and destroy any copies. Any dissemination or use of this information by a person other than the intended recipient or recipients is unauthorized and may be illegal. Any views or opinions expressed in this email are those of the author and do not necessarily represent those of the Specialty Screw Corporation. Warning: Although precautions have been taken to make sure no viruses are present in this email, Specialty Screw Corporation cannot accept responsibility for any loss or damage that may arise from the use of this email or attachments.   ­­  
4
I'm on e-10

How do I find the last operation to do the join? Normally I would create a sub query and do Max() function in a calculated field. In the BPM query creator I can't make a sub query and I can't make a calculated field. I can't even choose an expression in the table criteria. I have to join and filter existing tables and fields (without custom code I assume)
5

I think for the level of complexity on that one you might consider just writing the code from scratch it would be much easier than trying to jam it through the wizards.

 

Joshua Giese
Technology Solutions : CXO

Direct Phone:    920.593.8299
Office Phone:    920.437.6400 x342

http://wcibags.com/email/emailFooter4.jpg

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Thursday, July 7, 2016 11:39
To: vantage@yahoogroups.com
Subject: [Vantage] Another query puzzle

 

 

This is a training/learning project that may be of use if I can get it to work.

So I want to build a simple BPM that throws an exception with the last operation on an assembly is set to backflush before releasing a job. I ran the trace and am planning on running a pre-processing BPM to the Erp.JobEntry.CheckResourcePlants that fires when releasing a job.

I already have a query and dashboard tab written that finds the qty reporting operation (either the last operation or the operation marked as final assembly as shown in the JobAsm table), and then checks whether they are set to backflush or not. What I was planning to do was use the "Number of rows in the designed query is greater than 0" to throw an exception if the query finds an error.

The problem arises with the fact that the designed query in the BPM does not allow for sub queries, as far as I can tell. My original query has a sub query to find the highest number operation on an assembly (from the jobopr table) or the operation marked as final op (from the JobAsm table, if one is set). This calculated field would then be used to filter the JobOpr table to only show the qty reporting operation and see if that operation is set to backflush.

So for me to get the BPM to work, I need to figure out how to filter the operations down to the reporting operations on the job, and check whether they are backflush or not, all on a single level.

 

Any ideas?

6
I don't know how to write my own code so I don't have that option. I'm sure I'll be searching out some training soon.

In the meantime, if anyone else has any suggestion through the programmed wizards, that would be great.