Summary Export dependant on value produced in Monarch Summary

Al_21493

I've been very impressed with what a little thinking outside the box can accomplish using Monarch & Monarch Automator.

I ran into what I think is a dead-end today.  I have a PDF input file of an invoice that contains 3 detail lines.  The first two are items that were invoiced; the third is a freight surcharge which is a percentage of the extended price of the first two items.

I want to validate that the surcharge is indeed the amount it's supposed to be.  In Monarch, I created a summary that summed the extended price of the commodity items and also included as a separate field the surcharge.  In the summary output I can see these two values side by side and can visually tell whether they're equal or not.  In another version of the summary, I created an expression as one of the fields to subtract the sum of the commodities from the surcharge, hopefully leaving zero.

On the Automator side, I'd like to conditionally export the surcharge based on whether it matched or didn't match what was expected.  In the second version of the Monarch summary, that would mean export it if the value was zero or skip the export if it wasn't.

Not that it would make any difference in what I'm asking for, but the export I'd want to perform would be an ODBC update.  If there was no discrepancy, I'd want to add a G/L account # to the surcharge detail record (which would trigger our ERP to post it).

I'm not sure what would best accomplish this.  Allow filtering for Summaries?  Allow "If()" functionality in Summary formulas (allowing me to return the G/L # or blank depending on the value)?

Monarch & Automator are very powerful, and I've figured out a way to do most of the things I need.  But this one has me stumped.

Thanks,
Alan Hermann

Baba_Majekodunmi_703

Hi Alan,

Apologies for the delay, just seeing this now. Yes it is possible to do this in Monarch Desktop and Automator.

Monarch Desktop

• If you're using Classic, you can create a formula-based filter for the matching criteria.
• If you're using Data Prep Studio you can create formula column and then filter on that column for the matching criteria.

Monarch Automator - Verify Nodes

With Automator there are two options that come to mind for running the export if the criteria is met, one for classic, and one for Data Prep Studio.

• Classic: Verify Model Node
• Data Prep Studio: Verify Workspace Node

These Verify Nodes essentially have that criteria for checking if the number of rows is at least 1. In short, the capability is there.

Database  Update

Yes, this is a standard out of the box capability for Monarch Server. We can append to, or update existing database tables

 

Al_21493

Hi Baba,

The two values I'm trying to compare are in the result of a Summary.  Based on comparing Column A to Column B (both are sums of Detail fields) and finding they are equal, I want to perform a database update.  If they're not equal, I would skip the update.

As long as I have at least one Detail, there will always be a Summary.  I'm already doing a model verification that stops the process if there are no Details.

I believe what you've shown me below applies to the filtering of Details.

Thanks,
Alan

Baba_Majekodunmi_703

Al_21493 said:

Hi Baba,

The two values I'm trying to compare are in the result of a Summary.  Based on comparing Column A to Column B (both are sums of Detail fields) and finding they are equal, I want to perform a database update.  If they're not equal, I would skip the update.

As long as I have at least one Detail, there will always be a Summary.  I'm already doing a model verification that stops the process if there are no Details.

I believe what you've shown me below applies to the filtering of Details.

Thanks,
Alan

Ah I see. Yes, we can do this.

My recommendation then is that you perform this using data prep studio. In data prep studio, you can use the 'group by' function to create the two summaries, then join them to compare them.

Then in automator you would export based on that join of the summary tables. Here's a screenshot of the idea in data prep studio, and the attached files.

In this screenshot example, I created a 'group by' summary by media, joined by media, and created a formula column which I can then filter upon.

In automator, you can use the verify workspace to export based on this criteria being fulfilled.

 

Al_21493

Al_21493 said:

Hi Baba,

The two values I'm trying to compare are in the result of a Summary.  Based on comparing Column A to Column B (both are sums of Detail fields) and finding they are equal, I want to perform a database update.  If they're not equal, I would skip the update.

As long as I have at least one Detail, there will always be a Summary.  I'm already doing a model verification that stops the process if there are no Details.

I believe what you've shown me below applies to the filtering of Details.

Thanks,
Alan

Thank you, Baba.  I'll look into this and let you know how it works out.

Al_21493

Hi Baba,

Your example looks as if it's using existing summary data as inputs.  In my case, the summary hasn't been exported yet.  My "feature request" is for some way to conditionally export a summary based on a value (or values) in that summary.  I can't filter the details because I need values from all of the lines, so using a filter in Automator isn't an option.

This is what my Automator Visual Process is currently doing:

• Uses a PDF invoice document as input.
• A model defines fields in the invoice as well some added formula and external lookup fields.  The model also contains a Summary used to create Invoice Header records in our ERP.
• Model verification assures there is at least one detail to process (sometimes using a filter).  Otherwise it stops the Automator process.
• Summary data is exported to ERP's Invoice Header table.
• Detail data is exported to ERP's Invoice Detail table.
• PDF input file is moved to an archive folder.

To accommodate the freight surcharge issue, I created a 2nd Summary in the model.  This Summary contains the key information (Supplier # and Invoice #) as well as two summed values:

• Commodity extended price * expected surcharge %.
• Actual surcharge amount invoiced.

These two values should be the same, and if so, I would add a 3rd export to our ERP in the same Automator process that would write information needed by our Accounting Dept.  The problem is, Automator doesn't give me a way to conditionally perform a Summary export based on values in the Summary.  I can't apply a filter to a Summary, and when developing the model I'm very restricted as to what formulas I can use (especially no conditional logic).

I'm thinking the only way I'm going to be able to handle this is to actually export the new Summary "as is", then create another model to use the Summary data as input and apply filtering there.  I'm not sure how I'd chain this to the existing Automator process though.

Baba_Majekodunmi_703

Al_21493 said:

Hi Baba,

Your example looks as if it's using existing summary data as inputs.  In my case, the summary hasn't been exported yet.  My "feature request" is for some way to conditionally export a summary based on a value (or values) in that summary.  I can't filter the details because I need values from all of the lines, so using a filter in Automator isn't an option.

This is what my Automator Visual Process is currently doing:

• Uses a PDF invoice document as input.
• A model defines fields in the invoice as well some added formula and external lookup fields.  The model also contains a Summary used to create Invoice Header records in our ERP.
• Model verification assures there is at least one detail to process (sometimes using a filter).  Otherwise it stops the Automator process.
• Summary data is exported to ERP's Invoice Header table.
• Detail data is exported to ERP's Invoice Detail table.
• PDF input file is moved to an archive folder.

To accommodate the freight surcharge issue, I created a 2nd Summary in the model.  This Summary contains the key information (Supplier # and Invoice #) as well as two summed values:

• Commodity extended price * expected surcharge %.
• Actual surcharge amount invoiced.

These two values should be the same, and if so, I would add a 3rd export to our ERP in the same Automator process that would write information needed by our Accounting Dept.  The problem is, Automator doesn't give me a way to conditionally perform a Summary export based on values in the Summary.  I can't apply a filter to a Summary, and when developing the model I'm very restricted as to what formulas I can use (especially no conditional logic).

I'm thinking the only way I'm going to be able to handle this is to actually export the new Summary "as is", then create another model to use the Summary data as input and apply filtering there.  I'm not sure how I'd chain this to the existing Automator process though.

Hi Al, If someone doesn’t reply before tomorrow I will show you with a few screenshots how to combine processes in Automator. You’re on the right track.

Al_21493

Hi Baba,

I've already combined Automator processes for other reasons.  I'll play around with this, and if I need any help I'll let you know.

But my feature request still stands.  It would be nice to be able to create a conditional formula in a Summary, even if it was very limited in how it could be used.

Baba_Majekodunmi_703

Al_21493 said:

Hi Baba,

I've already combined Automator processes for other reasons.  I'll play around with this, and if I need any help I'll let you know.

But my feature request still stands.  It would be nice to be able to create a conditional formula in a Summary, even if it was very limited in how it could be used.

Hi Al,

Can you provide some screenshots of your existing process right now, and some visual of what you're trying to accomplish? I will pass it along to the product team.

But here's how you can 'chain' processes together, which I'm assuming is different from your interpretation of combining processes. They key is using  the 'hub' node.

Data Prep + Classic

This example demonstrates how you can combine a process in data prep studio with a process in classic. The data prep studio process runs first, exporting an exception based on a negative join between two files. The export is a csv.Then that csv file opened in Monarch classic and a model is applied to it which then exports by filter.

Two Classic Models

The second screenshot shows you how you can chain two classic processes.