Versions Compared

Old Version 6

changes.mady.by.user Eliot Muir

Saved on

compared with

New Version Current

changes.mady.by.user Eliot Muir

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Expand
titleHow does the excel side of things work?

  1. Code Language:

    • The code that handles communication with Excel is written in Visual Basic for Applications (VBA).

  2. Communication Process:

    • Data is sent and received using HTTP requests.

    • The adapter parses incoming data and formats outgoing data to work with Excel.

  3. Configuration Table:

    • A configuration table in Excel determines which web services to query and where to store the data. It contains the following key columns:

      • Enabled - true if the call is executed when pressing the Get Data from Iguana button

      • Table: Specifies where the data will be stored.

        • The adapter will create or update an Excel table object with this name in a tab of the same name.

        • You can change this on the fly simply by editing the name in the Table column. The adapter will create a new tab and a new table when you click on the Get Data from Iguana button.

      • URL: Provides the URL of the Iguana-powered web service to fetch the data.

      • ParamName and ParamValue: Optional parameters that can be used for more advanced customization when querying the web service.

By leveraging Excel table objects, the adapter ensures that data is dynamically updated and formatted properly. This setup provides flexibility for storing and customizing data retrieval and storage processes.

Similarly for sending data we have the following configuration table:

...

Expand
titleHow would I modify the source of the data being served up?

Look at APP/APPpull.lua to see how the data needs to be formatted.

In a real application you would most likely be querying the data from a SQL database and then formatting it in the same way as the application.

If you want to add additional web services then edit this code from main:

Code Block
local APPaction={}
APPaction["/"] = APProot
APPaction["/ExcelExport.xlsm"] = APPspreadSheet
APPaction["/push"]             = APPpush
APPaction["/pull"]             = APPpull

And define additional functions.

You can specify which table to populate in excel by editing the Table column entry in the Query table. That can be done purely in excel without altering the back end code.

Image Added
Expand
titleWhy does the code use a non standard escaping format for CSV for special characters?

Writing code in Visual Basic for Applications is challenging, so I chose a format that’s simple to parse and generate in VB, avoiding the need for a complex parser.

Since we control both ends of the ‘pipe’ in this case it makes sense to do it this way.

Expand
titleHow does user authentication work?

Currently, the code uses Iguana's session API for login, relying on Iguana's own login credentials. However, it doesn't maintain a session and simply authenticates via HTTP basic authentication.

For simplicity, the example code doesn't restrict data access per user, but this could be implemented using #hash_tags if needed.

Advice about Reporting

Expand
titleWhy build reports from first principles instead of using built in reporting tools in applications?

Built-in reporting tools because, while they’re quick to set up, they often hit limits and can’t combine data from different systems, like customer management and accounting.

By extracting data from the source systems and store it in separate 'shadow' tables for easy querying gives more power and flexibility.

It’s a first-principles method to give more flexibility and power when building reports.

...