Welcome to the OPA Hub!


Category Archives: Interview

Importing Data into an Interview : Excel Example

Importing Data into an Interview : Excel Example

Readers will remember a while ago I explained briefly how to use Microsoft Excel to act as a Connection Datasource – in this overview article, followed by this one in a little more detail. Now we look at another challenge : Importing Data into an Interview.

Well, here comes another example of the ubiquitous nature of Microsoft Excel. The customer requirement was as follows:

Using a simple mechanism, let the user upload an existing Excel spreadsheet into the Interview. Parse the spreadsheet, read the data in it, create corresponding rows in an Entity. Let the user review the data but do not require any new data entry. There may be up to 250 rows of data to import. So how do you go about about Importing Data into an Interview?

So how can we face up to a challenge like that? We need:

  • An upload that isn’t a standard File Upload Group
  • A parsing mechanism to read Excel and extract the data in a given tab, or wherever
  • A custom Entity Collect to handle the data import / create the rows in a Screen

The shopping list above isn’t that long.

The File Upload is essentially an HTML 5 component to let the user select a file on their computer. We cannot access an arbitrary local path from JavaScript, so we need the user to point to the file they want to upload.

There are a number of JavaScript-based Excel parsers, including the excellent SheetJS js-xlsx which we used. It is capable of converting to and from Excel, which is no easy task when you consider that an Excel file is basically a Zip file with a bunch of complicated stuff inside it. The library can convert to HTML, CSV and magically (for our requirement) JSON. Awesome!

Plus, in a previous post we’ve also looked at the (large amount of) work required to build a Custom Entity Collect Extension. In fact when I was writing that article I was thinking, for goodness sake Richard, when do you think you will actually need to go to the trouble of building that Entity Collect Extension? Well, I’ve finally found a use for it – Importing Data into an Interview!

We need an Entity Collect Extension since we need some way of getting the Excel data into the Entity Collect, which ultimately means we need to do some work behind the scenes between the import of the data and the display of the Entity Collect. We need to rewire the Entity Collect temporarily so that it sucks our Excel data up, before we show it to the user so they can examine the results.

For the purposes of a raw demo, I unplugged all the other functionality (delete buttons, add buttons, etc.) and just concentrated on getting the data into the Entity Collect. There are how ever a few caveats. Once you get into the larger imports, at least in the Debugger, you can expect to see “concurrent record editing” errors. I’m trying to find out what the limit is exactly. But up to a few hundred I think it’s OK.

So let’s look at the items in turn.

File Upload and Data Load

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
{
					//console.log("Starting customInput Mount");
					var div = document.createElement("input");
					div.id = "myFile";
					div.type = "File";
					//div.value = control.getValue();
					el.appendChild(div);
 
					function handleFile(e) {
						var files = e.target.files,
						f = files[0];
						var reader = new FileReader();
						reader.onload = function (e) {
							var data = new Uint8Array(e.target.result);
							//console.log("In Change");
							var workbook = XLSX.read(data, {
									type: 'array'
								});
 
							worksheet = workbook.Sheets["YOURWORKSHEET"];
							jsonoutput = XLSX.utils.sheet_to_json(worksheet, {
						raw: true, header : 1
							});
						//console.log("Read " + jsonoutput );
						};
 
						reader.readAsArrayBuffer(f);	
						var completepath = $(':file').val();
							//console.log(completepath);
						interview.setInputValue("rest_filenameandpath", completepath);
					}
 
 
				var filedialog = document.getElementById("myFile");
					filedialog.addEventListener('change', handleFile, false);
 
					var completepath = $(':file').val();
						control.setValue(completepath);
						//console.log("Hello " + completepath);
						//console.log("Ending customInput Mount");
				}

Assuming you have a custom Input framework as your starting point, the above code will be in the mount. This will build an HTML5 file upload control, and attach an event handler. The code regarding Excel depends upon xlsx.full.min.js being in the resources directory. But that’s it. You’ve loaded the Excel file into a JSON object.

Entity Collect

The next step is to include a Custom Entity Collect in your project, and use the jsonoutput object (which you just created from the imported file above) in the mount of the Entity Collect to load the JSON into the Entity Collect. The following is an extract from the mount code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
var numEntities = Object.size(jsonoutput);
					//console.log(numEntities);
					// Remove header row if the file has one
					jsonoutput.shift();
					// load records into the Entity Collect
 
					if (control.getRows() == 0) {
						for (j = 0; j < numEntities - 1; j++) {
							control.addNewRow();
							var mycurrentrecords = control.getRows();
							mycurrentrecords[j][0].setValue(jsonoutput[j][0]);
							mycurrentrecords[j][1].setValue(jsonoutput[j][1]);
							mycurrentrecords[j][2].setValue(jsonoutput[j][2]);
							mycurrentrecords[j][3].setValue(jsonoutput[j][3])
drawrows();

The end result is something like this:

Importing Data into an Interview

The File is loaded into the Entity Collect, and the contents displayed to the user. In my case I unhooked all the code related to modification (the onchange stuff from the original idea) and removed the add / delete buttons, since it was designed to just allow the user to see the loaded result, not modify it.

Importing Data into an Interview

If you want to have a look at the project, just download the very basic example here.

Back to Basics : Seeding from a URL Parameter

Back to Basics : Seeding from a URL Parameter

This topic comes up regularly and it often seems to get mixed up with just plain old “starting the interview from a URL”. So it’s time for a little refresher about Seeding from a URL Parameter. Time for another Back to Basics topic (catch the previous one here) !

First of all : What do we mean by seeding from a URL parameter?

Sometimes you want to start an Interview with certain attributes already populated. But this is not a situation where you are using a Connection, for example, to perform an inbound mapping loaded at start. Perhaps you are embedding Oracle Policy Automation Interviews on a Web site, and the Web site is going to pass some information into the Interview when it starts.

In pseudo-world, you want to do something like this:

https://myinterview.com/startsession/Interview/?myattribute=X

It’s really quite simple and it is becoming more frequent, since lots of Oracle Policy Automation is appearing in modern interfaces driven by JavaScript and other frameworks that support using JavaScript.

But if you try to do something like this with Oracle Policy Automation, you get a nasty surprise. It doesn’t work at all.

Then you read the documentation, and discover two or three important things that must be in place.

  1. You must authorize pre-seeding for the attribute(s) you are interested in

Seeding from a URL Parameter 1

2. You must use the seedData= tag on your URL to introduce the information to the Interview.

Armed with this you come back to your Interview and you try something like this, with impressive results:

Seeding from a URL Parameter 2

What the heck is happening here? It all seemed so simple. Well it is, but you forgot the most important point. Whatever your seeding from a URL requirements, they must be URL encoded. So the “=” character for example, is not going to get through the defences of Oracle Policy Automation. You have to encode it all first. So let’s imagine you have an attribute, whose name is my_seeded_value, and you want to populate that attribute with Richard Napier.

You need to encode part of what you are sending. You do NOT need to encode “seedData=” in fact if you do, there will be yet another error. Your URL needs to look like this :

http://xxx.com/web-determinations/startsession/URL_Seeding?seedData=

And the rest of the URL needs to be something like my_seeded_value=Richard Napier. But that part needs to be encoded. As a simple example, take it to https://www.urlencoder.org/ and put it through the encoder. It probably comes out like my_seeded_value%3DRichard%20Napier. Excellent. Now you can create the complete URL:

http://xxx.com/web-determinations/startsession/URL_Seeding?seedData=my_seeded_value%3DRichard%20Napier

And it comes out just fine:

Seeding from a URL Parameter 3

So there has definitely been an important lesson here : don’t encode seedData=, but do encode the information after it.

There is a lot more to learn about URL-based seeding, and we will continue this after the break. If you want the full online help, it is to be found here.

Have a good Holiday and see you in 2019!

Entity Collect Extension

Entity Collect Extension

Some time ago I wrote about Entity Collect Extension and the challenges of writing a demonstration piece of code. The challenges of Entity Collect Extension are pretty well known, but here is a run down of the most important one:

  • You must build the container and the contents,  and manage them

That means, that if you build an Entity Collect Extension, you cannot use other control extensions inside it :- you must code the entire contents yourself, so you would be looking at

  • Handling all different data types of attribute
  • Handling all the basic events of these attributes
  • Managing instance creation and deletion
  • Handling styling options and look and feel

So when you look at this shopping list, it can be quite daunting. Even so, I said to myself, if Oracle provides such a functionality, then somebody somewhere is going to use it. And so the idea came to create a demonstration Entity Collection Extension. When I started working on it I really did not have much time to look at it, so I wrote a couple of articles about my experiences and left it at that.

Recently I have been revisiting the concept again, and have found  time to work on it a little bit more. I added it to the OPA Hub Shop (on the strict understanding, as usual, that it is completely for educational and amusement purposes only).

That statement is particularly important in this case. I only decided to do this because I am fascinated by other people’s JavaScript frameworks. I find it useful to know what goes on underneath the glossy Oracle Policy Modeling HTML output. And my example is basic to the point of being little more than a sandbox (oh, and by the way, it still has a lot of issues – especially when you are running it in the Debugger rather than the Browser).

Entity Collect Extension in JavaScript

This version tries to cover off the 4 shopping list items shown above, and adds a couple of interesting ideas

  • Dynamic Delete Button (remove the button based on an attribute value). This is one request I have seen many times – the ability to forbid deleting in certain circumstances.
  • Handling Date Time and Value Lists for display and data entry.

I still have to find time to investigate :

  • Handling Inferred attributes that need to be refreshed as you are entering instance attributes.

I’ve just about had enough of this now so I am going to leave it for a while.  It has started to make my head spin.  Nonetheless it is an interesting exercise and helped make clear how cool the out of the box JavaScript really is.

The silent movie below shows where I’m at. All that work, for this rather dull looking thing. Sometimes there is not much to show for efforts. Of course, if I was a real programmer, I would be doing all of this in a much more efficient way (avoiding complete page refreshes at every change and so on) but I found this exercise very interesting. Certainly it gives me a better understanding of the scale of such a task.

Have a nice day!

Interviews, Languages and URL Arguments

Hi There!


This content is accessible only to logged in users of the OPA Hub Website.

To Register takes only 10 seconds and uses LinkedIn for authentication.

Once registered you can change your OPA Hub password and manage it independently of LinkedIn.

We recommend you keep different passwords for all your sites.

To register, click the Log in link in the menu at the top of your page.

Thanks, the OPA Hub Website.

Whats New in Oracle Policy Automation 18A #1

Whats New in Oracle Policy Automation 18A #1

And so the latest and greatest version of Oracle Policy Automation has just hit the shelves, I’m of course talking about version 18A which became available for download from the Oracle Policy Automation Downloads page on the Oracle Technology Network pages. The list of what’s new is quite short this time, although we can imagine that lots of things have been going on behind the scenes given some of the early access info that has been filtering out on the official Oracle Policy Automation Blog these last few weeks. So, on with Whats New in Oracle Policy Automation 18A #1!

Whats New in Oracle Policy Automation 18A #1

Automatically Retrieve List Values from Rules

The biggest addition to the Oracle Policy Modelling experience in this new version is the concept of dynamically (or almost) retrieval of lists of values from rules. I’ll give you an example that you might be familiar with. You have written rules that deal with an attribute, of type Text, and you have referenced it several times with different values, perhaps as a condition to some conclusion. And now you want to add it to an interview. At some point you will find yourself copying and pasting the values in your Word document into a Value List or a plain old List of values in the Interview Screen.

Well, not any more. The video in the next paragraph shows off the new functionality, whereby the modeler can retrieve the values at the click of a button and add them to a screen, or of course use the existing functionality to turn them into a Value List. The video goes on to demonstrate what happens when you go back and edit the rules in the Word document, and what happens when you change the Control type – for example from Drop Down to Fixed List and so on.

The example uses a fictitious restaurant deciding the correct level of spices to be added to their sauce, depending on the customer selection.

video

That concludes our post Whats New in Oracle Policy Automation 18A #1, in the next post we will look at some improvements to the REST API (which we looked at in earlier posts) to enhance user lifecycle management.

Custom JavaScript Extension Places and Custom Options List

Hi There!


This content is accessible only to logged in users of the OPA Hub Website.

To Register takes only 10 seconds and uses LinkedIn for authentication.

Once registered you can change your OPA Hub password and manage it independently of LinkedIn.

We recommend you keep different passwords for all your sites.

To register, click the Log in link in the menu at the top of your page.

Thanks, the OPA Hub Website.

JavaScript Custom Extension Google Maps for Addresses and Reverse Geocoding

Hi There!


This content is accessible only to logged in users of the OPA Hub Website.

To Register takes only 10 seconds and uses LinkedIn for authentication.

Once registered you can change your OPA Hub password and manage it independently of LinkedIn.

We recommend you keep different passwords for all your sites.

To register, click the Log in link in the menu at the top of your page.

Thanks, the OPA Hub Website.

JavaScript Extension Custom Header as a Timer in Oracle Policy Modelling

JavaScript Extension Custom Header as a Timer in Oracle Policy Modelling

Sometimes I get distracted. I was supposed to be preparing for a workshop last night on an unrelated, Siebel-centric topic. I happened to be reading (again) through some material on our sister site, https://siebelhub.com, and I came across an example – which I have always been impressed by – from Duncan Ford, one of the Siebel Open UI and JavaScript gurus who contributes to the Siebel content on our blog. And it got me thinking about and Oracle Policy Automation JavaScript Extension Custom Header as a Timer.

The basic tenet of the script was building a set of statistics for timing performance : how long did such and such a page take to load and be ready, in a sense. In Siebel-world, this is a constant worry and ongoing process. So I got distracted by this and thought about Oracle Policy Automation and how we might use some of the ideas in our own script. I settled on the basic principle that I would want to know about how long it took people to finish a given Screen (ultimately you could extend it to the Stage concept as well). So not directly about performance, more about user time.

To do this, I figure we already have a bucket-load of exciting charts from the Hub :

JavaScript Extension Custom Header as a Timer

You know the sort of things I mean, available for each project : you can grab a set of information about a Project, change the type of chart, decide how to split the axes, decide which version to look at, filter on Service Cloud criteria and number of days and so forth.

These are great  tools. Of course, you also might have the mindbogglingly powerful In-Memory Policy Analysis with TimesTen and so on.

But you might not have any of that to hand, and you might want to work out how much time, each person spends on each Screen. Of course the navigation paradigm in Oracle Policy Automation is different to a Customer Relationship Management application like Siebel – you can go back and forth between the Screens quite a lot. All I really want to do in this case – because it was useful to me – was to identify the cumulative time spent on each Screen, and display it in the Browser at the end of the Interview:

JavaScript Extension Custom Header as a Timer

It sounds really easy but there are a couple of things that will be interesting to talk about along the way.

JavaScript Extension Custom Header as a Timer in Oracle Policy Modelling  – Data

I decided to choose the customHeader as my starting point. The documentation states that this renders the header for an interview. So by logical extension it probably has the things we might need :

  • Access to the Interview Object
  • Access to the Stages and Screens in the Stage
  • Adding code that does not implement any UI probably won’t cause a problem, since the UI does not have to display a Header (it’s an option in the Styles dialog)

For the second part of the requirement, the display of the results, I decided to use the customLabel approach with jsGrid, similar to the example back in December of last year.

So I knew I would need 2 files, one for the custom Header and one for the custom Label. Since the results would only display on the last page (“Interview Complete”), I wanted to be able to pass the results from the Header to the Label easily. So how did the JavaScript Extension Custom Header as a Timer in Oracle Policy Modelling work out? Well, as usual I was just experimenting so it is rough, ready and not very robust. But it is interesting enough to provide some talking points. Let’s look at the code for the Header first.

First the opening salvo of the customHeader:

JavaScript Extension Custom Header as a Timer

  1. We are going to store results in an array of JavaScript objects. This is similar to how the actual Screens are accessed in the Interview, and also it allows us to plug the data straight into the jsGrid component.
  2. So here is the Array
  3. I’m loading the set of Screens here. Be aware, that the Screens you load will only be those in the current Stage. So our myScreens array will be used to store (as you navigate) all the screens in all the stages, to have only one big array when you have finished.
  4. I’m checking in my Array of Objects to see if the Screen we are on has ever been visited : is it in my Screens array?
  5. If it is in my Array, this is not the first time you have been on the Screen in question. We update the information about the Screen in the Object, calculating the elapsed time using a very rough and ready technique.

JavaScript Extension Custom Header as a Timer

Here is the rest of the script, which I have split out into a second part for readability.

  1. If the Screen is not in myScreens, then it is a new Screen. We add it and set up the basic information.
  2. We hook our myScreens array to the Interview object.

JavaScript Extension Custom Header as a Timer in Oracle Policy Modelling – Label

Over in the Label code, the process is very familiar:

JavaScript Extension Custom Header as a Timer 5

  1. Create a custom DIV on the final Screen using the Custom Property “name” to make sure we only execute on the label we are interested in. Get a reference to our myScreens array.
  2. Spawn a jsGrid from the DIV (which implies of course that jQuery and jsGrid files are in the /resources folder of your Project).
  3. Build the field object, noting that you can format the fields, or add / remove fields as you see fit from your array of Objects.

So if all goes according to plan you might get something like this. Note that the whole thing only works if you navigate normally – that is to say you navigate using the navigation Buttons or stage Buttons. Using the Debugger tree on the left does not have the same effect at the array will be empty. The array will also be empty if you do not reset between each debugging session. Once deployed, there should be none of those issues of course, or you could Ctrl+F5 to debug in Chrome or your favorite browser.

OPA 12 - JavaScript Extension Custom Header as a Timer in Oracle Policy Modelling 6

This being a bit of an experiment, I didn’t go much beyond 5 screens in 4 different stages, nor did I add any events to time the Stages, or to time Controls  – such as “on Focus” and “on Blur” which I thought about doing to be able to get the times in and out of individual controls and which I might do at some point.

JavaScript Extension Custom Header as a Timer – Conclusion

Anyway I hope the ideas, if not the implementation, have given you some pause for thought – namely how to get the Screens and Stages and load them into your own Array, and pass them to other Controls. The experimental code is on the OPA Hub Shop, search for customHeader.

Have a nice day!

Custom Entity Container JavaScript Extension Example #3

Custom Entity Container JavaScript Extension Example #3

Custom Entity Container JavaScript Extension Example #3

By now most of you will have realised that as far as JavaScript goes, I am a bit of a generalist. As one wise person once put it “the kind of person who can code themselves into a mess but not out of one”. I learn by exploring and doing. I’ve been “doing” for a long time (I started with a ZX 81 and pretty much kept mucking about from there). My first ever big IT project was replacing IBM 5520 word processors with Windows 3, Word and a custom application written using Visual Basic.  I get kind of obsessive about understanding the “big picture” without necessarily wanting to have every detail. And so it is with Custom Entity Container JavaScript Extension Example #3.

All that to explain what I have mentioned before : everything in relation to JavaScript which I offer to you here is purely for entertainment or educational purposes : many times I have to sketch out a need or specification, and what you see on this blog relating to Oracle Policy Automation JavaScript extensions is just the results of some head-scratching or an idea that I have needed to show to someone more technical than myself to be “tidied up” (a.k.a professionalized).

The reason I mention it is because I often work in Siebel CRM (visit www.siebelhub.com the other site in this family) and JavaScript is a big part of moving forward with Siebel too. So I get to mix and match. Today I found myself looking at the two previous incarnations of the Custom Entity Container JavaScript Extension Example #3 (#1, #2) and thinking “this looks like a List Applet in Siebel”. So I decided to place the layout of the custom entity list in the hands of jQuery and jsGrid. Siebel CRM actually uses jqGrid but I needed something very lightweight for this example requirement, so jsGrid was a better choice. Of course I needed minified jQuery as well.

My goal is to make this a fairly simple routine to present instances of an inferred entity. So the code for the jsGrid looks very minimalist. It basically takes the for..each loop and extracts the two attributes from the array created in Custom Entity Container JavaScript Extension Example #2, and then passes that to jsGrid as a flattened dataset without the attribute child nodes. I also set up the grid to only have two columns, and no editing (since this is an inferred entity). I set the page size and the size of the actual space taken up by the entire list:

Custom Entity Container JavaScript Extension Example #3

  1. The loop creates a flattened data structure just right for jsGrid
  2. The data is passed to the jsGrid
  3. The column headers are sized appropriately

The end result is rather nice, smooth scrolling (not in the Debugger, only in Ctrl+F5 debugging in a modern browser).

Custom Entity Container JavaScript Extension Example #3

 

Now that’s what I call a list! Using the jsGrid library gives me (and the reader) the added bonus of scrolling or jumping using the paging links at the bottom of the list. Cool!

As ever this is available from the OPA Hub Shop, look out for Custom Entity Container JavaScript Extension Example #3 in the product list.

Custom Entity Container JavaScript Extension Example #1

Hi There!


This content is accessible only to logged in users of the OPA Hub Website.

To Register takes only 10 seconds and uses LinkedIn for authentication.

Once registered you can change your OPA Hub password and manage it independently of LinkedIn.

We recommend you keep different passwords for all your sites.

To register, click the Log in link in the menu at the top of your page.

Thanks, the OPA Hub Website.