How to structure this data in PEGA
I'm trying to work out how this works in PEGA because no one seems to know. I currently have a spreadsheet which has lets say 3 important columns:
Customer Number, Account Number, Address Prefix
Each customer will have multiple accounts spread over multiple records. I'll need to flag all of that customers records as processed or errored as the robot works through them.
Because PEGA does not have the capability to work with excel by querying it as far as I can tell I've decided it'll be easier to manage this data in a database using queries to load, flag, move this data as I damn well please.
What I want to do is load a distinct list of CIS numbers via a query into an object in pega which can then be read through by a listloop to then feed this CIS number into a string variable. I then want to use this variable as a parameter in two subsequent queries to draw in: A list of accounts for that customer and the prefix for that customer.
My issue is what container do I use and how do I load the results of my query into it? I've tried using lookuptables but this doesn't seem to want to load anything, you can't seem to loop through a datatable.
I'm trying to work out how this works in PEGA because no one seems to know. I currently have a spreadsheet which has lets say 3 important columns:
Customer Number, Account Number, Address Prefix
Each customer will have multiple accounts spread over multiple records. I'll need to flag all of that customers records as processed or errored as the robot works through them.
Because PEGA does not have the capability to work with excel by querying it as far as I can tell I've decided it'll be easier to manage this data in a database using queries to load, flag, move this data as I damn well please.
What I want to do is load a distinct list of CIS numbers via a query into an object in pega which can then be read through by a listloop to then feed this CIS number into a string variable. I then want to use this variable as a parameter in two subsequent queries to draw in: A list of accounts for that customer and the prefix for that customer.
My issue is what container do I use and how do I load the results of my query into it? I've tried using lookuptables but this doesn't seem to want to load anything, you can't seem to loop through a datatable.
Ideally what I'm looking for is the equivalent of creating a container for this in a class library so you'd have say "CustCase" as a class and "Custcase.CustNum" as a string, "Custcase.Accounts" as a list, and "Custcase.Prefix" as a string, to then be fed into an application to perform my automation. Once complete using the CIS again to flag back to the database that case is to complete and to ignore it next time.
This just seems absolutely impossible in pega whilst in a .NET application it would be really simple to code, could someone possibly point me into the direction of what components and methods would allow me to do this? I basically want to load my queries into these data items to allow me to loop through 1. The Customer numbers to define which case the process is to load and work on and 2. The Customers Accounts to be selected as part of the process
Pegasystems Inc.
US
This would be pretty tricky to do using the LookupTable component and the Excel connector. The lookupTable could hold the data, and you could then write it back to Excel, but since you are versed in .Net it would be much easier there. You could probably perform that all in a C# script and using the Script component to host that code, or you could create your own .Net Component dll and use that in your solution.
You can use the query component to query and Excel file, although I do not know how it would handle updates and inserts as that's really a .Net question. The query component is really just a wrapper for the .Net component, so it should have the same capabilities.
My suggestion; since you already see this as easy in .Net is to write it there and use a script or create your own component. The script is better as it would include everything in the source code of the solution, whereas as component would be developed separately and the resulting dll would be referenced. For the component (for developers only), you'd need to place the dll in your installation directory. You could then add it to your toolbox and use it in an automation. The dll would get added as a reference to the deployment package and shipped there.