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.
