Forum

Welcome to the community! Register below to add topics and replies.

Please or Register to create posts and topics.

How to use matrix data

In a previous forum post, How to use external CSV data, we show how you can load spreadsheet data on the fly, and use it to drive a data loop. In this article, we talk show you can instead use "matrix data". This is what we call spreadsheet data where we want to identify and retrieve a row by name. Instead of having to loop over the CSV, we instead use an index column which represent our "key". 

Start by saving your spreadsheet as Comma-Separated Values (CSV), using the reserved column "_key" to signify the lookup key. A good example is using a spreadsheet for test logins and password, for different roles. See below example:

_key, username, password
admin, john, iamjohnspassword
user, anna, iamannaspassword

Make sure that the column heading are in good format, as these will be keys for the imported data. 

Next, copy the file to an accessible server or service such as Dropbox. In my example, I have placed the file on a server running on localhost port 80, and named the file matrixtest.csv. 

http://localhost:8000/data/matrixtest.csv

It's important to note that no restrictive "Access-Control-Allow-Origin" is set when accessing the file. Learn more about this here.

Next, open Boozang, create a test and open the data tab. Create new data -> Request data and input the URL according to below screenshot

Next, create a script action to log the result. Input the following code into the Script window

$test.json=$test.matrixdata.admin;
console.log("One row of data");
console.log($test.json);
console.log("One value");
console.log($test.json.username);

The code can be seen in below screenshot

As you can see, we have extracted the "admin" row of the spreadsheet, and mapped it to the JSON object "$test.json".

Press play, and the console of the application window should show

One row of data
Object
  password: "iamjohnspassword"
  username: "john"
__proto__: Object
One value
john

As you can see, this can be very useful to retrieve external data where you want to be able to choose what data to load. This so-called "cherry-picking", can be used for login and username info, but also for validation data lookups where you don't want to maintain the master data in Boozang. 

Create your account for free!

No credit card required.