Options for an Extranet browser-based application to allow vendors to update price lists

Date: Archived
Product/Release: LANSA for the Web & LANSA Integrator 
Abstract: Options for an Extranet browser-based application to allow vendors to update price lists.
Submitted By: LANSA Services


We are interested in setting up an Extranet application that would allow our vendors to provide us with their price guarantees for the coming year via a browser. They would prefer to work in some sort of "Excel like" format to enable copying, pasting, etc. Is there some way we can set up standard Excel files in various folders on our IFS, allow a vendor to log into our Extranet, point to the proper IFS folder containing the Excel file and update that file via browser?

When the vendor is finished, we would then grab the Excel file, upload it into a DB/400 data file, and update our vendor quote file accordingly. I don't like the idea of distributing Excel files to the various vendors and having them send (FTP) the files back. In that scenario, we quickly lose control of the format of the spreadsheet, which causes grief when importing them to apply to our database. Any suggestions would be appreciated.

We are currently using LANSA for the Web for three eCommerce Web sites we are hosting. We have a model B configuration with a dedicated front-end iSeries Web-server.


This is a common requirement and vision.

Let's assume that only one user would update a particular .xls at any one time, i.e. one user per vendor. If not, an Excel-based solution would be troublesome.

Let's also assume you already have an Extranet that can authenticate users and identify which vendor they represent.

Option 1: Manually create and store each vendor's .xls file in separate directories on IFS.

The Web-server definition can ensure that you have a distinct realm and *VLDL (validation list) per directory - that way, even if a devious user hand-crafted a URL, s/he could still not browse a .xls of another vendor, only their own.

Then, you upload the .xls data to a flat-file and write RDML functions to process the data and store it in the appropriate DB2 files.

The above approach would work, but is not very scalable. If you have 20 or more vendors, it will quickly start to become unmanageable.

Also, each .xls can easily be deleted / lost / overwritten without the level of control expected from a database application.

Another problem is that the user will not receive real-time feedback if their data is invalid per your Repository business rules, requiring manual communications back to them, after the fact.

Option 2: Generate and parse the .xls dynamically on demand, using LANSA Integrator.

An alternative is to both generate and parse the .xls dynamically on demand, using LANSA Integrator.

  1. When the user authenticates, you check your database to see if they have already begun to enter their new prices. If not, you can use LANSA Integrator to automatically produce an .xls from your iSeries database at runtime, with no intervention. I'm not sure if you'd want to include a row for each product in your DB for that vendor, pricing from last year, and maybe leave 20 blank rows for new products.
  2. The user can then either use their browser to input the information, or for better convenience, they can save the document in proper .xls format and use the full power of Excel to populate their price list, offline, in their own good time.
  3. When finished populating the price list, you could have them upload their .xls to your server, via an intuitive browser interface. They would authenticate to your Extranet, click the link/button to upload a price list, and simply select a document from their hard-drive/network.
  4. When you receive the uploaded .xls, the content can be automatically parsed, validated against your Repository rules (e.g. correct format, price is mandatory, price cannot be negative, etc), and automatically update your iSeries DB2 files - using LANSA Integrator - all in real-time without intervention.
    If you don't trust the upload from day one, you could allow the update to go to a staging file, or to the live DB2 file but with a 'pending' status.
  5. If there are errors in the .xls data, these would be communicated back to the user automatically, in real-time - so they can correct and update their information.

If you feel you need to call vendors to action (i.e. remind them of a deadline to get their prices in by), you could auto-email them with a reminder, and a URL that would take them straight into the .xls (after authentication, of course).

This approach leverages your existing database, protects the user from losing their data, allows you to change .xls format without recalling a bunch of price lists, respects your Repository business rules, is very scalable, and above all requires no manual intervention.

Option 3: Browser-based input capable table.

A second alternative is to forget the use of Excel and provide the user with a browser-based input capable table. These days, with dynamic HTML and JavaScript, tables can quickly be re-sorted, summed, etc - all through the browser, without a server call.

The upside is that you will have the greatest control of the data. The downside would be that the user must stay online when populating their pricing data.