I am mapping out a plan to implement a solution where a customer wants a PO number from an Excel sheet. The requirements are to look at three columns to get the value you're looking for. Any of the values you are considering can be found in many rows but all three values must exist in one row in order to have a match. In other words, you may have a name that can repeat across multiple rows but you won't have a name, an edition and a version that repeats as that's all on one row.
I have not yet created a RPA solution using Excel. I am sure I can learn it. However, I can also see how doing this could be fast with a custom software program that Pega may run at the right time and find out what row the values exist on from that program. I could make a Java program to do this which Pega runs as part of the larger automation. Before I go down one path or another and based on the requirements I list, can I please get some advice about what would be the smiplest way to search for the values I'm looking for (write a custom program run by Pega or use the Pega RPA tools)?
I suppose the answer is relative to your experience with your preferred tool. In terms of automating Excel, you'll be a doing all of your interaction with a component rather than through the actual Excel interface, so your training in terms on interrogation won't apply. For me as an experienced user, this would be quick to write in Pega Robotics. I am not sure how well Java interacts with Excel, but I am certain you could probably use it as a Datasource and query it like a database. If that is more comfortable for you to write your own code and have Pega Robotics invoke it, then that would work just the same. The advantage to keeping it in Pega Robotics though is your code is all in one place. Using the Excel component is relatively straight-forward. You open the Excel document, you can call GetCellValue (it takes in A1, B43, ZZ654, etc...) and construct a means of iteration (I use a forLoop) and then evaluate. I would use the Excel Connector's ExportData method. This will produce a Datatable object whose rows you can iterate through to evaluate and return whatever column you need.
Thank you for this. To do it quick, I made a program that opens the excel doc, iterates through the cells and then when it finds all the matches, breaks the iteration and returns the value needed. I find that the low code feature of Pega are great but I tend to be faster with things like that in an IDE writing software and then using Pega for flow control and automation of external systems. However, I don't know Pega as well as you do so it takes me longer than just writing simple class files sometimes. That said, it's really cool that you can tie all these together and use Pega as the hub no matter what strategy you invoke.