addToTableIfMissing script

[Sam Moffatt]

This is another one of my convenience functions that adds rows to a table if a given key/value pair are missing. This does mean that you can have duplicate ‘keys’ with different values and this is intentional. The use case is for picking up attributes from external sites like eBay or AliExpress that have this sort of attribute or variant data available to hand. This is another one in my ‘Script Manager’ form which I’m going to attach for reference as well. It includes the four modules I’ve posted for the logger, the currency conversion, setIfEmpty and this addToTableIfMissing function.

// ========== addToTableIfMissing Start ========== //
// NAME: addToTableIfMissing
// VERSION: 1.0.1
// CHANGELOG:
//      1.0.1: Add support for specifying a custom record to use.

document.getFormNamed('Script Manager').runScriptNamed('Logger');

/**
 * Add's a key/value pair to a table if and only if it's missing.
 *
 * If you have a key that exists already but with a different value,
 * then this will add another table row for 
 *
 * fieldId:			The field ID of the table field.
 * keyField:		The field ID of the key field in the table field.
 * key:				The value of the key field to check against.
 * valueField:		The field ID of the value field in the table field.
 * value:			The value of the value field to check against.
 *
 * return: Empty return value in all cases.
 */
function addToTableIfMissing(fieldId, keyField, key, valueField, value, currentRecord)
{
	if (!currentRecord)
	{
		currentRecord = record;
	}
	logger.logMessage(<code>Adding to ${fieldId} with ${key} and ${value}</code>);

	var table = currentRecord.getFieldValue(fieldId);
	for (var index = 0, count = table.length; index < count; index++)
	{
     	var targetKey = table[index].getFieldValue(keyField);
     	var targetValue = table[index].getFieldValue(valueField);

		if (targetKey == key && targetValue == value)
		{
			logger.logMessage(<code>Found existing value for ${fieldId} with ${key} and ${value}</code>);
			return;
		}
	}

	var newRecord = currentRecord.addNewRecordToField(fieldId);
	newRecord.setFieldValue(keyField, key);
	newRecord.setFieldValue(valueField, value);
	return;
}
// ========== addToTableIfMissing End ========== //

Here’s a sample of one of the note parsers that I use to take key/value summary data and turn it into something mildly useful. Most of the heavy lifting is done by a PHP script that I run locally to process and extract the data, this script is just handling the response data:

document.getFormNamed('Script Manager').runScriptNamed('Logger');
document.getFormNamed('Script Manager').runScriptNamed('setIfEmpty');
document.getFormNamed('Script Manager').runScriptNamed('addToTableIfMissing');

function noteParser()
{
	var note_id = 'fld-bf19d52c18cb4f5198df191ef7902e1b';
	var notes = record.getFieldValue(note_id);
	
	if (!notes)
	{
		return "Notes field empty";
	}

	var result = Utils.postContentToUrlWithContentType(notes, "http://localhost/~pasamio/research/scrape/extract_note.php", "text/plain");

   if (!result)
   {
      return "no result returned from web service";
   }
   

	if ("title" in result.fields)
	{
		setIfEmpty(record, 'fld-0d0edd2552ea461e929f806a4e5552b5', result.fields.title, null);
	}

	if ("price" in result.fields)
	{
		setIfEmpty(record, 'fld-08129d71ab0f4fa4a2749456281fca07', result.fields.price, null);
	}

	if ("brand" in result.fields)
	{
		setIfEmpty(record, 'fld-1e250019d7b249f282cc572814d3e71d', result.fields.brand, null);
	}
	
	if ("itemkey" in result.fields)
	{
		setIfEmpty(record, 'fld-ae7379d699e9473aa2ab16a2a2f002d4', result.fields.itemkey, null);
	}

	if ("colour" in result.fields)
	{
		setIfEmpty(record, 'fld-a8626656cc90455ea9336dd2488d4aef', result.fields.colour, null);
	}
	
	if ("category" in result.fields)
	{
		setIfEmpty(record, 'fld-6fdd09891a8c4d73be1b24aa07d077be', result.fields.category, null);
	}
	
	var variant_data_id = 'fld-eb212e705eb34e9ea5cc4386ea7a9b1f';
	var key_id = 'fld-ecc1b1ede8414912a63ec144012fa9e9';
	var value_id = 'fld-e4ce093c1c22416192eb80554272d6cd';

	if (result.data)
	{
		for(datum in result.data)
		{
			addToTableIfMissing(variant_data_id, key_id, datum, value_id, result.data[datum])
		}
	}

	document.saveAllChanges();
	
	return result;
}

logger.consoleHeader('Note Parser', 'Purchases');
var result = noteParser();
logger.consoleFooter('Note Parser', 'Purchases');

JSON.stringify(result);

I quite often use JSON.stringify to take an object and turn it into JSON that is stored in the field. This helps with debugging and seeing the contents of the execution after it has run. This used to be more necessary before the console was added but is still useful to have a durable copy of the last execution.

Attachments:

You must be logged in to view attached files.

[Author]

Posts