Augmenting Datasources via “CreateContent.lua”

Manipulation of incoming data is possible through the execution of a script right after EasyCatalog retrieves data from a data source, but before any subsequent processing takes place. The script, named ‘createcontent.lua‘, when placed in the data source’s scripts folder, is triggered as the last stage of the synchronise operation. Pressing the info button on a data panel, and then the script Icon will automatically create this file. It receives a ‘records’ variable, which is a RECORDSET object representing the unprocessed data obtained from the data source.

This script has the ability to add new records, remove fields, modify field names, and assign field content. This functionality allows for the integration of data from diverse sources into a unified dataset, as well as the retrieval of language-specific fields based on options provided by the data source.

For example:

simple example.xls

field 1	field 2	field 3
1	A	A
2	B	B
3	C	C
4	D	D
5	E	E

When loaded into EasyCatalog, with field 1 as a key, it looks like:

Using the following ‘createcontent.lua’ script:

-- this can be used to access data source options
-- this_local =  DATASOURCE.get():getoption("locale", "US");

-- Rename an existing file
records:renamefield("Field 2", "f2");

-- Create a new field and add content
records:addfield("Regular Field");

for i = 1,records:size() do
   r = records:getrecord(i);
   r:field("Regular Field"):setcontent(i);
end

-- create a new tabular and add content
records:addfield("Tabular Field");
t = TABLE.new();
t:cell(1,1):setcontent("1");
t:cell(2,1):setcontent("2");
t:cell(3,1):setcontent("3");
t:cell(4,1):setcontent("4");

for i = 1,records:size() do
   r = records:getrecord(i);
   r:field("Tabular Field"):setcontent(t);
end
-- Remove a field
records:removefield("Field 3");

-- Duplicate each record and create a valid key
 for i = 1,records:size() do
   nr = records:duplicaterecord(i);
   nr:field("Field 1"):setcontent( nr:field("Field 1"):content() .. "-1");
end

-- this can be used to access data source options

-- this_local = DATASOURCE.get():getoption("locale", "US");

-- Rename an existing file

records:renamefield("Field 2", "f2");

-- Create a new field and add content

records:addfield("Regular Field");

for i = 1,records:size() do

r = records:getrecord(i);

r:field("Regular Field"):setcontent(i);

end

-- create a new tabular and add content

records:addfield("Tabular Field");

t = TABLE.new();

t:cell(1,1):setcontent("1");

t:cell(2,1):setcontent("2");

t:cell(3,1):setcontent("3");

t:cell(4,1):setcontent("4");

for i = 1,records:size() do

r = records:getrecord(i);

r:field("Tabular Field"):setcontent(t);

end

-- Remove a field

records:removefield("Field 3");

-- Duplicate each record and create a valid key

for i = 1,records:size() do

nr = records:duplicaterecord(i);

nr:field("Field 1"):setcontent( nr:field("Field 1"):content() .. "-1");

end

The result after synchronizing is:

Make sure the field type of “Tabular Field” is set to be a tabular field, with “Command Script” as the source.

Notice the indicators on the fields which didn’t originate from the original data. Fields created in this way are treated as regular fields, with the exception of tabular fields which are stored in binary format within the snapshot file.

The benefit of employing this technique instead of using ‘createformattedcontent.lua’ is that data manipulation occurs prior to loading, resulting in actual changes to the content within a field. Furthermore, it has no impact on the rebuilding of formatted content, as only takes place during data synchronization. Additionally, this approach allows for the indication of content changes, including updates and deletions.

Examples:

Duplicating records using a delimited category field

This particular sample is suitable for situations where a single field determines the categories in which a record should appear. In order to effectively group the panel, EasyCatalog necessitates having one record per category. Hence, this script duplicates a record for each value found in its ‘Category’ field, where values are separated by a semi-colon. Since each record requires a unique identifier (referred to as the key), this script appends ‘-1’, ‘-2’, ‘-3’, and so on to the end of the key field for each duplicated record. In this example, the key field is named ‘Product number,’ and the field containing the list of categories is ‘Category.’

for i = 1,records:size() do
    -- Get record i
	record = records:getrecord(i);

    -- Get the contents of the Category field
	categoryField = record:field(“Category"):content();

    -- Define a duplicates created counted
	n = 0;

    -- For each delimited value
	for k, v in string.gmatch(categoryField , "([^;]+)") do
		if (n == 0) then
	    -- Remember the first value
			rememberMe = k;
		else
		    -- For anything other than the first, duplicate the record
			newRecord = records:duplicaterecord(i);

		    -- Define a unique key
			newRecord:field("Product number"):setcontent( newRecord:field("Product number"):content() .. "-" .. n);

		    -- Set the new Category content
			newRecord:field("Category"):setcontent(k);
		end
		n = n + 1;
	end
	if n > 1 then
	    -- If more than one duplicate was created, set the first value
		record:field("Category"):setcontent(rememberMe);
	end
end

for i = 1,records:size() do

-- Get record i

record = records:getrecord(i);

-- Get the contents of the Category field

categoryField = record:field(“Category"):content();

-- Define a duplicates created counted

n = 0;

-- For each delimited value

for k, v in string.gmatch(categoryField , "([^;]+)") do

if (n == 0) then

-- Remember the first value

rememberMe = k;

else

-- For anything other than the first, duplicate the record

newRecord = records:duplicaterecord(i);

-- Define a unique key

newRecord:field("Product number"):setcontent( newRecord:field("Product number"):content() .. "-" .. n);

-- Set the new Category content

newRecord:field("Category"):setcontent(k);

end

n = n + 1;

end

if n > 1 then

-- If more than one duplicate was created, set the first value

record:field("Category"):setcontent(rememberMe);

end

Transforming XML field content using XSL

This script is designed to transform XML data using XSLT stylesheets and store the transformed results in a field named “Tranformed” for each record. It requires a version of EasyCatalog 2024 or the beta version to work correctly.

-- Add a field to store the transformed XML
records:addfield("Tranformed");

-- Define the stylesheet
local xmlContent = [[
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:template match="/">
..
..
    </xsl:template>
</xsl:stylesheet>
]]

-- Walk down each "XML" field, transform it and store the result in "Transformed"
for i = 1,records:size() do
   r = records:getrecord(i);
     local xml_content = r:field("XML"):content(i);
     local xml = XML.new(xml_content);
     local t = xml:applyxslt(xmlContent);
    r:field("Tranformed"):setcontent(t);
end

-- Add a field to store the transformed XML

records:addfield("Tranformed");

-- Define the stylesheet

local xmlContent = [[

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">

</xsl:template>

</xsl:stylesheet>

]]

-- Walk down each "XML" field, transform it and store the result in "Transformed"

for i = 1,records:size() do

r = records:getrecord(i);

local xml_content = r:field("XML"):content(i);

local xml = XML.new(xml_content);

local t = xml:applyxslt(xmlContent);

r:field("Tranformed"):setcontent(t);

end

Creating a configurable option to select data

This example introduces a custom ‘locale’ parameter for the data source. This parameter enables the extraction of data from JSON and assigns it to a field based on the selected locale. You can modify the locale by accessing the Information Dialog in the data panel and clicking the ‘Update’ button. The JSON data is processed using JMESPath

-- Create a new field for the translated content
local_specific_field_name = "locale content";
records:addfield(local_specific_field_name)

-- Add a new option. If it exists, does nothing
DATASOURCE.get():addoption("locale", "en")

-- Example JSON, normally from a related field
local exampleJSON = [[
{
  "field_name": [
    {
      "language": "en",
      "content": "This is the English content"
    },
    {
      "language": "es",
      "content": "Este es el contenido en español"
    },
    {
      "language": "fr",
      "content": "Ceci est le contenu en français"
    }
  ]
}
]]

-- Get the chose locale
chosen_locale = DATASOURCE.get():getoption("locale")

-- Construct a JMESPath expression to get the selected locale
jmespath = "field_name[?language=='" .. chosen_locale .. "'].content"

-- Set the localized string on each record
for i = 1,records:size() do
   r = records:getrecord(i);
   value = processjson('jmespath', exampleJSON, jmespath);
   -- clean the result to remove JSON markup
   value = string.sub(value, 3, -3)
   r:field(local_specific_field_name):setcontent(value);
end

-- Create a new field for the translated content

local_specific_field_name = "locale content";

records:addfield(local_specific_field_name)

-- Add a new option. If it exists, does nothing

DATASOURCE.get():addoption("locale", "en")

-- Example JSON, normally from a related field

local exampleJSON = [[

{

"field_name": [

{

"language": "en",

"content": "This is the English content"

{

"language": "es",

"content": "Este es el contenido en español"

{

"language": "fr",

"content": "Ceci est le contenu en français"

}

]

}

]]

-- Get the chose locale

chosen_locale = DATASOURCE.get():getoption("locale")

-- Construct a JMESPath expression to get the selected locale

jmespath = "field_name[?language=='" .. chosen_locale .. "'].content"

-- Set the localized string on each record

for i = 1,records:size() do

r = records:getrecord(i);

value = processjson('jmespath', exampleJSON, jmespath);

-- clean the result to remove JSON markup

value = string.sub(value, 3, -3)

r:field(local_specific_field_name):setcontent(value);

end

Defining a custom sort order field

This example creates a custom sorting field based on the sizes of T-Shirts.

field_containing_size = "field"

-- Define the sort ordering
tshirt_sizes = {
    ["XS"]   = 1,
    ["S"]    = 2,
    ["M"]    = 3,
    ["L"]    = 4,
    ["XL"]   = 5,
    ["XXL"]  = 6,
    ["XXXL"] = 7
}
-- Create a new field to store the sort value
records:addfield("tshirt_sort")

-- Populate the sort order field for each record
for i = 1,records:size() do
   r = records:getrecord(i);
   
   -- Hardcoded for example
   -- size = r:field(field_containing_size):gsetcontent()
   size = "L"; 
   r:field("tshirt_sort"):setcontent(tshirt_sizes[size])

end

field_containing_size = "field"

-- Define the sort ordering

tshirt_sizes = {

["XS"] = 1,

["S"] = 2,

["M"] = 3,

["L"] = 4,

["XL"] = 5,

["XXL"] = 6,

["XXXL"] = 7

}

-- Create a new field to store the sort value

records:addfield("tshirt_sort")

-- Populate the sort order field for each record

for i = 1,records:size() do

r = records:getrecord(i);

-- Hardcoded for example

-- size = r:field(field_containing_size):gsetcontent()

size = "L";

r:field("tshirt_sort"):setcontent(tshirt_sizes[size])

end

Parsing JSON data using a cursor

The following example shows breaking down a JSON field using the jsoncursor method. Only available in the 2024 version:

json = [[
{
  "product_name": "Example Phone X",
  "brand": "TechCo",
  "model": "X2023",
  "color": "Midnight Black",
  "release_date": "2023-03-15",
  "display": {
    "type": "AMOLED",
    "size": "6.5 inches",
    "resolution": "1080 x 2340 pixels",
    "refresh_rate": "120Hz"
  },
  "processor": {
    "brand": "TechCo",
    "model": "T-9000",
    "cores": 8,
    "clock_speed": "2.5 GHz"
  },
  "storage": {
    "internal": "128GB",
    "expandable": true,
    "expandable_type": "microSD",
    "max_expandable_capacity": "512GB"
  },
  "camera": {
    "main_camera": {
      "resolution": "64 MP",
      "aperture": "f/1.8",
      "image_stabilization": true
    },
    "selfie_camera": {
      "resolution": "32 MP",
      "aperture": "f/2.0"
    }
  },
  "battery": {
    "capacity": "4000 mAh",
    "fast_charging": true,
    "wireless_charging": false
  },
  "operating_system": "Android 13",
  "connectivity": {
    "network_support": ["4G", "5G"],
    "bluetooth_version": "5.2",
    "wifi_version": "Wi-Fi 6"
  },
  "sensors": ["Fingerprint", "Accelerometer", "Gyroscope", "Proximity"],
  "dimensions": {
    "height": "156.2 mm",
    "width": "74.5 mm",
    "thickness": "8.9 mm",
    "weight": "185 grams"
  },
  "water_resistance": "IP68",
  "additional_features": ["Face Recognition", "Stereo Speakers", "NFC"],
  "price": "$699.99"
}

]]


local function type2string(cursor) 
	result = "";
	local event_type = cursor:type()
	if event_type == "string" then
		result = result .. cursor:getstring();
	elseif event_type == "bool" then
		result = result .. tostring(cursor:getbool());
	elseif event_type == "bool" then
		result = result .. tostring(cursor:getbool());
	elseif event_type == "double" then
		result = result .. tostring(cursor:getdouble());
	elseif event_type == "int64" then
		result = result .. tostring(cursor:getint64());
	elseif event_type == "uint64" then
		result = result .. tostring(cursor:getuint64());
	end
	return result .. "";
end

local function summarize(cursor)
    local result = "";

	indentlevel = 0;
     
    while not cursor:done() do
        local event_type = cursor:type()
        if event_type == "beginarray" then 
            indentlevel = indentlevel + 1;
	        cursor:next()
        elseif event_type == "beginobject" then
            indentlevel = indentlevel + 1;
	        cursor:next()
        elseif event_type == "endobject" then 
            indentlevel = indentlevel - 1;
	        cursor:next()
        elseif event_type == "endarray" then 
            indentlevel = indentlevel - 1;
	        cursor:next()
		elseif event_type == "key" then
            result = result .. string.rep("    ", indentlevel - 1)
			result = result .. cursor:getstring() .. " : ";
            cursor:next()
			local event_type = cursor:type()
			if event_type == "beginarray" or event_type == "beginobject" then
				result = result .. "\n"
			else
     			result = result .. type2string(cursor);
				result = result .. "\n"
		        cursor:next()
			end
		else
            result = result .. string.rep("    ", indentlevel - 1)
			result = result .. type2string(cursor);
			result = result .. "\n"
	        cursor:next()
		end
    end
        
    return result
end

value = summarize(jsoncursor(json))


records:addfield("JSON Sample");
for i = 1,records:size() do
   r = records:getrecord(i);
   r:field("JSON Sample"):setcontent(value);
end

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

json = [[

{

"product_name": "Example Phone X",

"brand": "TechCo",

"model": "X2023",

"color": "Midnight Black",

"release_date": "2023-03-15",

"display": {

"type": "AMOLED",

"size": "6.5 inches",

"resolution": "1080 x 2340 pixels",

"refresh_rate": "120Hz"

"processor": {

"brand": "TechCo",

"model": "T-9000",

"cores": 8,

"clock_speed": "2.5 GHz"

"storage": {

"internal": "128GB",

"expandable": true,

"expandable_type": "microSD",

"max_expandable_capacity": "512GB"

"camera": {

"main_camera": {

"resolution": "64 MP",

"aperture": "f/1.8",

"image_stabilization": true

"selfie_camera": {

"resolution": "32 MP",

"aperture": "f/2.0"

}

"battery": {

"capacity": "4000 mAh",

"fast_charging": true,

"wireless_charging": false

"operating_system": "Android 13",

"connectivity": {

"network_support": ["4G", "5G"],

"bluetooth_version": "5.2",

"wifi_version": "Wi-Fi 6"

"sensors": ["Fingerprint", "Accelerometer", "Gyroscope", "Proximity"],

"dimensions": {

"height": "156.2 mm",

"width": "74.5 mm",

"thickness": "8.9 mm",

"weight": "185 grams"

"water_resistance": "IP68",

"additional_features": ["Face Recognition", "Stereo Speakers", "NFC"],

"price": "$699.99"

}

]]

local function type2string(cursor)

result = "";

local event_type = cursor:type()

if event_type == "string" then

result = result .. cursor:getstring();

elseif event_type == "bool" then

result = result .. tostring(cursor:getbool());

elseif event_type == "bool" then

result = result .. tostring(cursor:getbool());

elseif event_type == "double" then

result = result .. tostring(cursor:getdouble());

elseif event_type == "int64" then

result = result .. tostring(cursor:getint64());

elseif event_type == "uint64" then

result = result .. tostring(cursor:getuint64());

end

return result .. "";

end

local function summarize(cursor)

local result = "";

indentlevel = 0;

while not cursor:done() do

local event_type = cursor:type()

if event_type == "beginarray" then

indentlevel = indentlevel + 1;

cursor:next()

elseif event_type == "beginobject" then

indentlevel = indentlevel + 1;

cursor:next()

elseif event_type == "endobject" then

indentlevel = indentlevel - 1;

cursor:next()

elseif event_type == "endarray" then

indentlevel = indentlevel - 1;

cursor:next()

elseif event_type == "key" then

result = result .. string.rep(" ", indentlevel - 1)

result = result .. cursor:getstring() .. " : ";

cursor:next()

local event_type = cursor:type()

if event_type == "beginarray" or event_type == "beginobject" then

result = result .. "\n"

else

result = result .. type2string(cursor);

result = result .. "\n"

cursor:next()

end

else

result = result .. string.rep(" ", indentlevel - 1)

result = result .. type2string(cursor);

result = result .. "\n"

cursor:next()

end

return result

end

value = summarize(jsoncursor(json))

records:addfield("JSON Sample");

for i = 1,records:size() do

r = records:getrecord(i);

r:field("JSON Sample"):setcontent(value);

end

Augmenting data from another data source

Creates a set of HEADING and VALUE fields as described in an external data source:

-- Get all the records from the other data source
f2_records = DATASOURCE.get("EXTERNAL_DATASOURCE"):getrecordset()

-- Define the maximum number of HEADING and VALUE fields created
local num_possible = 15
local field_to_match_in_this_data_source = "CODE"
local field_to_match_in_second_data_source = "CODE"

-- Function that takes 2 records
-- f1_record = record from this data source
-- f2_record = matching record from the second

function populate_record(f1_record, f2_record)

  -- Iterate though NUM1(1) to NUM1(num_possible)
  for i =1, num_possible do 
	  num_field = "NUM" .. tostring(i);
	  val_field = "ALPH" .. tostring(i);
	  table_heading_field = "HEADING" .. tostring(i);
	  table_value_field = "VALUE" .. tostring(i);

	  -- Get the matching field content
	  num_field_value = f2_record:field(num_field):content()

	  -- If a field name is present
  	  if num_field_value ~= '' then

		  -- Get the value of this field
		  v = f1_record:field(num_field_value):content();
		  h = num_field_value;
		  f1_record:field(table_heading_field):setcontent(h)
		  f1_record:field(table_value_field):setcontent(v)
	  end
  end
end

-- Add 'num_possible' fields, HEADING, VALUE
for i =1, num_possible do 
	table_heading_field = "HEADING" .. tostring(i);
	table_value_field = "VALUE" .. tostring(i);
	records:addfield(table_heading_field);
	records:addfield(table_value_field);
end

-- For each record in the data source
for i = 1,records:size() do

	-- get the record
   r = records:getrecord(i);

	-- get the content of the Ligne_Produit field
   group_value = r:field(field_to_match_in_this_data_source):content();

	-- Get the matched records in the second data source
	-- by matching Ligne_Produit in this record to Ligne in that

   f2_matched_recorrds = f2_records:filter(field_to_match_in_second_data_source, group_value);

	-- There should only be one match 
	if f2_matched_recorrds:size() == 1 then

		-- Populate this record with the matched record
		populate_record(r, f2_matched_recorrds:getrecord(1))
	end
end

-- Get all the records from the other data source

f2_records = DATASOURCE.get("EXTERNAL_DATASOURCE"):getrecordset()

-- Define the maximum number of HEADING and VALUE fields created

local num_possible = 15

local field_to_match_in_this_data_source = "CODE"

local field_to_match_in_second_data_source = "CODE"

-- Function that takes 2 records

-- f1_record = record from this data source

-- f2_record = matching record from the second

function populate_record(f1_record, f2_record)

-- Iterate though NUM1(1) to NUM1(num_possible)

for i =1, num_possible do

num_field = "NUM" .. tostring(i);

val_field = "ALPH" .. tostring(i);

table_heading_field = "HEADING" .. tostring(i);

table_value_field = "VALUE" .. tostring(i);

-- Get the matching field content

num_field_value = f2_record:field(num_field):content()

-- If a field name is present

if num_field_value ~= '' then

-- Get the value of this field

v = f1_record:field(num_field_value):content();

h = num_field_value;

f1_record:field(table_heading_field):setcontent(h)

f1_record:field(table_value_field):setcontent(v)

end

-- Add 'num_possible' fields, HEADING, VALUE

for i =1, num_possible do

table_heading_field = "HEADING" .. tostring(i);

table_value_field = "VALUE" .. tostring(i);

records:addfield(table_heading_field);

records:addfield(table_value_field);

end

-- For each record in the data source

for i = 1,records:size() do

-- get the record

r = records:getrecord(i);

-- get the content of the Ligne_Produit field

group_value = r:field(field_to_match_in_this_data_source):content();

-- Get the matched records in the second data source

-- by matching Ligne_Produit in this record to Ligne in that

f2_matched_recorrds = f2_records:filter(field_to_match_in_second_data_source, group_value);

-- There should only be one match

if f2_matched_recorrds:size() == 1 then

-- Populate this record with the matched record

populate_record(r, f2_matched_recorrds:getrecord(1))

end

Preprocessing Field Options Hilighting the Document Selection

Augmenting Datasources via “CreateContent.lua”

Examples:

Duplicating records using a delimited category field

Transforming XML field content using XSL

Creating a configurable option to select data

Defining a custom sort order field

Parsing JSON data using a cursor

Augmenting data from another data source

What’s New In 2024

EasyCatalog Training

Whats new in 2023

Whats new in 2022

Start your free trial today