E9.2 Orchestrator Data Request Previous Value to view and compare like in Report Design Aid

[notchy]

Hi. I have a requirement to read over Ledger table F43199 and then send off a notification only once, based on the last record.
Since F43199 is a ledger table there could be an PO# with Line # 1 but lines and I only need to grab the most current record.
I adjusted the order sequencing in the Data Request to read the Date Updated and time Updated by descending order, so I grab the record I need but then the subsequent records get read and I want to compare the DOCO and DCTO and skip the process and move on to the next DOCO set

Is there a feature where I can store it as a variable and view the previous value?

Just like in RDA reading over a BSVW you have the option to compare BC value against a PC value.
Example: BC F43199.DOCO = PC F43199.DOCO

 

[DaveWagoner]

I assume you don't have advanced SQL query on your version of JDE because then you could do fancy SQL with 1 row per PO

Here's how to do that same thing on my version. Click on Options, then set Maximum Records to 1.


This gets you one record for a given DOCO and DCTO with your ordering. This is the "inner" data query. You'd want an "outer" data query where you pull a list of DOCO's and DCTO's from the file. You send the outer data without the maximum limits, but aggregating on DOCO and DCTO. You send that to a data set, then iterate over that with your 1 record pull.

This way you're not stuck in the muck and mire of comparing current to previous values. Short of writing a great SQL statement it should be pretty efficient.

 

[notchy]

I assume you don't have advanced SQL query on your version of JDE because then you could do fancy SQL with 1 row per PO

Here's how to do that same thing on my version. Click on Options, then set Maximum Records to 1.
View attachment 19712

This gets you one record for a given DOCO and DCTO with your ordering. This is the "inner" data query. You'd want an "outer" data query where you pull a list of DOCO's and DCTO's from the file. You send the outer data without the maximum limits, but aggregating on DOCO and DCTO. You send that to a data set, then iterate over that with your 1 record pull.

This way you're not stuck in the muck and mire of comparing current to previous values. Short of writing a great SQL statement it should be pretty efficient.

I will give this a try and report back.

I just made the change but it truly brings back 1 record. I have to run this based on Date Updated. So if I put in current date, then if there were 3 PO# created and edited, I would need to pick up 1 instance of the PO# based on the lastest date and time updated. Is there anything I can do wth this through Query manager?
This is actually a joined bsvw.

 

[DaveWagoner]

I will give this a try and report back.

You may want/have to wrap the inner DR in an orchestration if you need to send out one notification per PO record found. iterate can be weird otherwise

 

[DaveWagoner]

I will give this a try and report back.

I just made the change but it truly brings back 1 record. I have to run this based on Date Updated. So if I put in current date, then if there were 3 PO# created and edited, I would need to pick up 1 instance of the PO# based on the lastest date and time updated. Is there anything I can do wth this through Query manager?
This is actually a joined bsvw.

You need an outer Data Request that gets you a list of PO #s and types. You do this by aggregating the PO # and type (and do a where clause including date for instance). This gets you a list of "distinct" PO #/type for whatever criteria you have. This is your outer query that you push into a dataset. Then you call an orchestration, iterating over that dataset. Inside the orchestration, you do your single row select based on the key data provided to you by the outer data request, and send your notification.

Hope that makes sense!

 

[notchy]

I assume you don't have advanced SQL query on your version of JDE because then you could do fancy SQL with 1 row per PO

Here's how to do that same thing on my version. Click on Options, then set Maximum Records to 1.
View attachment 19712

This gets you one record for a given DOCO and DCTO with your ordering. This is the "inner" data query. You'd want an "outer" data query where you pull a list of DOCO's and DCTO's from the file. You send the outer data without the maximum limits, but aggregating on DOCO and DCTO. You send that to a data set, then iterate over that with your 1 record pull.

This way you're not stuck in the muck and mire of comparing current to previous values. Short of writing a great SQL statement it should be pretty efficient.

You need an outer Data Request that gets you a list of PO #s and types. You do this by aggregating the PO # and type (and do a where clause including date for instance). This gets you a list of "distinct" PO #/type for whatever criteria you have. This is your outer query that you push into a dataset. Then you call an orchestration, iterating over that dataset. Inside the orchestration, you do your single row select based on the key data provided to you by the outer data request, and send your notification.

Hope that makes sense!

Yes. I was able to use your concept. I was hoping there was an easier way of doing this.
Thanks @DaveWagoner

 

[mad_sagittarian]

Hello @notchy

Would you please mind to explain how the 'Outer Data Request' is designed? @DaveWagoner mentioned , it needs 'Aggregating the PO# and Type and do a where clause'. I am not following this.
I have a similar requirement to fetch UDC rows and based on each row, update P43090 rows (UDC stores Branch, Supplier and Route Code. One UDC row can have corresponding multiple P43090 rows).
I have designed 1st Data Request to fetch UDC rows -> 2nd Data Request is to fetch F43090 rows and -> 3rd Form Request to Update p43090 but this is not working hence curious to understand your solution.

Thanks in advance.

Thanks,
Andy