Search Results for 'script'

[Sam Moffatt]

Is privacy and security the main reason for choosing CouchDB over iCloud?
Do you recommend downloading the iOS version in the beginning or after you have all your forms build? What determines that decision?

I started using Tap Forms with version 3 and followed the upgrade to 5. Version 3 did a lot of what I cared about and used iCloud to sync all of the documents around. iCloud was really slow with some of my attachment heavy documents (my main document is 6GB) which meant sync took forever to run and bulk changes even longer. I have an iMac at home that I set up essentially as a server using the P2P sync and then once CouchDB was added it became the CouchDB host. CouchDB gives me access to the innards of my TapForms documents to do interesting stuff like build a tree of my database structure, build my own more fine grained backup tooling and to integrate with tools like ElasticSearch and Kibana to do some graphing of the data.

I had TapForms on both iOS and Mac at that point to replace a Bento based workflow and I think it’s been a great shift (except for layouts, Bento had iOS layouts which helps it there). I generally develop new forms on the Mac and then push them from there but I’m not afraid of building stuff on the fly on my phone too. You can build everything as you go and it’ll sync the structure to all of the devices.

Are there a lot of differences in syntax between the Mac and iOS versions and do they work seamlessly with each other despite those differences?

The two versions offer almost all of the same features, the only missing one on iOS is custom layouts. There are different user interactions that can make things a little more tedious on iOS but a lot of that is due to the smaller form factor. I’m sure there are a few other things missing but most of the time I don’t miss them (except for the oft requested custom layouts, which to get right on iOS will be a chunk of work as Bento only had custom layouts on iPad, not iPhone).

Let’s talk form building and linking strategy for a minute. If you have a main topic, we’ll use medicinal herbs for example, :)

I want to track: Broad categories and intricate details about each herb.

1. History of each:
Acquired it: When, where who from, how and why acquired: Grew it, Bought it, Wildcrafted it.
Positive identification attributes of each herb.
2. Culinary Information
a. Edible parts
b. Various possible preparations
c. Nutritional information
d. Recipes

3. Growing Information for each herb, both recommended and actual and success rate of each method.

4. Medicinal Information: Properties, Actions, Constituents, Various medicinal preparation possibilities .

5. Work in Progress- Start, Strain, Bottle, Label, and Store

6. Expenses: Assets, Supplies (consumable, Functional, Packaging)

7. Inventory:
Location,
Container size and date,
Amount on hand,
Age and expiration date
Consumable Supplies
Macerating
Ability to increase and decrease amount in each, broken down by preparation type:
Tinctures,
Infused Oils,
Vinegars,
Macerating
Consumable Supplies,

Looks like you’re well on your way to a database structure already :)

My spreadsheets have become too long, too many, too time consuming and too redundant and added to that using Quicken to manage the expenses. So the broad end goal here is to greatly reduce the paperwork time and organization of it all by entering each piece of information about each herb ONCE and being able to, at a click, see any individual piece of information or all the information about a particular herb.

What you’re looking for here is generally referred to as “third normal form” for a database. There are a few guides out there that explain it but functionally the idea is that you identify as precise a subset of the data and then give it a key. In TapForms, each record has it’s own generated key which you can then use to link.

As far as data entry for new records goes, the more input boxes where necessary, multiple choice check boxes and radio buttons where possible and branching decisions via IF, Then, Else statements seems logical to me but I have no experience with databases so I am open to suggestions.

That being said, design-wise, Is it better to have more or fewer forms?

The determination I’d go with is what unique details does this form capture. I started off in Bento with a “Purchases” form that stored a bunch of details about the purchases I make and image attachments for reference. Within TapForms I’ve now morphed that into a much larger structure with a form that stores unique “Order” details (when purchased, where, how much, tax, discounts, etc), a line item “Order Item” form which then links back to my more detail orientated “Purchases” form. I also have “Shipments” which are linked from “Orders” and also “Purchases” because an order can have multiple shipments so all of those shipments are linked to the order and I sometimes link the individual purchases to shipments when orders are split shipped.

Design wise you follow third normal form to reduce each form to it’s unique items and progress from there. As you can likely tell, I’ve done that progressively over time and I use the increasing amounts of automation in TapForms to be able to make some of that quicker whilst maintaining my legacy “Purchases” structure as well.

For the forms that I do create, (all information revolving around each individual herb), should they all have a one to many link with inverse relationship checked? Or say, in the case of storage location and growing conditions, since they all have them, be linked as many to many? I am unclear as to what determinations to consider in the design phase. Is that determined by how you want to view the end results?

When I look at my Purchases based document, I have two entry points: one from Orders and one from Purchases. Purchases is a relatively “flat” document with a lot of duplicated fields whilst “Orders” has what I started off as more normalised. Orders -> Order Items is 1:M, Order Items -> Purchases is M:M however I do some tricks. I use a calculation field to generate a composite key called “Purchase Key” that combines Marketplace, Store Name and Order ID which is in both “Orders” and “Purchases” which is used by a Link to Form JOIN field to link Purchases directly to Orders automatically as well. I didn’t build all of this in a day, it’s been evolving over years to get to where it is today. Just make sure you do a backup before majorly restructuring your database and also disable sync to prevent the changes propagating as you make them.

Ideally, I imagine an opening screen asking me which herb I want to work with and letting me choose either from a list of Common name OR Latin name.

Your entry point would be the herb names in those two forms, that’s your first document. Then you link to form into the rest of the data structures.

Once chosen, it would ask me what a I want to do with the herb and show me the following choices:

Acquire it,
Grow it
Process it – Dry it , Freeze it, Package it or Store it
Work in Progress
Start a Preparation –
Strain a Preparation
Build a value added product
Research it
Count it
Find it – Finished product in storage or Macerating
View Logs
Daily Log
Individual herb log
Herb Identity Checklist

Once chosen then launch into each aspect of those categories and store any input information in a record under that herb.

Those mostly map into forms that makes sense. Acquire sounds like a form about where you got the herb, Grow it similarly. Preparations is a single form with some filters as is your logs (which I suspect is the original use case). You can use prompter to provide simpler UI interactions or you can just use links.

Then there is the question of importing the past 8 years of data. Should I arrange the spreadsheet columns to match the new data records tables or design the new data entry to match the spreadsheet columns?

Thoughts?

This is actually the truly hard question of the lot because you only make this decision once. The rest you can evolve over time to add new fields, new forms, new links, etc. This one is truly a one time decision.

The advantage of creating your TapForms structures to replicate your spreadsheets is that you might be able to continue to use TapForms in a not dissimilar way to how you use your spreadsheets today. That means you can leverage slightly more structured data entry to start getting everything better from a structure you know already. The disadvantage here is that you’ve likely got some amount of duplication in place today and this propagates it (not dissimilar to my own “Purchases” form). If you’re using any sort of automation, macros or formula in your spreadsheets then those would also need to be ported across before you can be fully productive. The other advantage of keeping it 1:1 early on is if you decide that TapForms isn’t the answer, then exporting the data back out again and reintegrating it into your spreadsheets will be easier.

The advantage of building everything brand new in TapForms would be the ability to design out your structure in third normal form and have everything set up right from the beginning. You already have a strong idea of your data model (it’s there in your spreadsheet), you just need to go through and figure out the unique items that are duplicated and turn them into forms. The downside of this is that your spreadsheets aren’t in this format and to import your data will require a lot of massaging to get that to work. Instead of taking what you already have and immediately mapping it across and then going through to validate that all of the data loaded into TapForms correctly using it’s built in table view, you’re going to have to do that twice: once when converting your sheets into a form TapForms can import and a second time when you import it into TapForms. As with any data conversion activity, you want to make sure that you’ve got everything moved across accurately and the only way to trust that is to verify the transformed data. If you find records or entries in the first situation that got broken in transit, then it’s a 1:1 mapping to fix whilst if you find records mismatched here, you have to do the normalisation yourself.

My leaning would be recreate the data in TapForms aligned to your current structure and then start to use the TapForms functionality to progressively make it better. Thinking back to my own “Orders” -> “Order Items” -> “Purchases” use case, I have a script in “Order Items” that takes the current record, looks up it’s parent “Orders” record and uses data in both of those to create a new “Purchases” record. Make sure you keep backups (TapForms makes it easy) and just progressively improve your data structure.

Here’s the meat of the script that takes order items/orders and makes a new purchases record:

function createDetailRecord()
{
	// The order ID linking field.
	var order_id_fld = 'fld-c3a6725d7d9446da92bbb880ffe90a9e';

	// Get the parent order field.
	var order = record.getFieldValue(order_id_fld);

	// Pull some details from this record (order item)
	var title = record.getFieldValue('fld-39ca9564ef2347ac93f933bc9a2316ac');
	var price = record.getFieldValue('fld-a2973999a60d4319baf0b4480d6f57a0');
	var note = record.getFieldValue('fld-d0cfab9ec09d497294fbd8b5b52caf16');
	var line_number = record.getFieldValue('fld-f95b68d488cb4b058bbf3de84e1a7c3b');

	// Pull some other values across.
	var purchase_date = order.getFieldValue('fld-bc2e4b152dee42ac9361539a6e37cb5d');
	var marketplace = order.getFieldValue('fld-fa37906add2942c88bce3b500561c42d');
	var order_id = order.getFieldValue('fld-8228040b4641449b96aabfaea15f1ac5');
	var store_name = order.getFieldValue('fld-c153da2f9a504be4b6fee4b5b62a1c11');
	var ship_date = order.getFieldValue('fld-6ab700ccc11d418fbd27d8899d00c7a9');
	var delivery_date = order.getFieldValue('fld-4b1c4180dc1b4bb08b32d16fa60cae66');
	var purchase_key = order.getFieldValue('fld-3e49aaa5bc32429c8f0f0f234878356d');

	// Do something
	// Details field names
	var details_title_id = 'fld-0d0edd2552ea461e929f806a4e5552b5';
	var details_price_id = 'fld-08129d71ab0f4fa4a2749456281fca07';
	var details_notes_id = 'fld-bf19d52c18cb4f5198df191ef7902e1b';

	var details_purchase_date_id = 'fld-ccbd9a8f51d34246bebfb31aa4e397dd';
	var details_ship_date_id = 'fld-cb3a9886ac7f4ec487447801a3911a1a';
	var details_received_date_id = 'fld-bb17d48e41c7423692ab586f6c884d05';

	var details_order_id_id = 'fld-e3e66a0f2e5c4df7b9496f65355e0bcf';
	var details_marketplace_id = 'fld-c163aba17ae64c4d93b5a53819a139dc';
	var details_store_name_id = 'fld-3d98dc6cdcae4a88909c97c80dde7bfb';

	var details_state_id = 'fld-9402f8c0d53c43b986fee4ebc3468929';

	var details_shipping_tracking_number_id = 'fld-6ea45a9c141343628940bfbcfa38ee90';
	var details_shipping_carrier_id = 'fld-12644a7a4ae24ed8a7926123832d3557';
	
	var details_purchase_key_id = 'fld-8be9b2c2603f458f8349082237c41964';
	var details_order_line_number_id = 'fld-da763fa0946d4be79039b4e828cf85f4';

	var data = {
		// Order Item Details
		[details_title_id]: title,
		[details_price_id]: price,
		[details_notes_id]: note,
		[details_order_line_number_id]: line_number,
		
		// Order Details
		[details_purchase_date_id]: purchase_date,
		[details_ship_date_id]: ship_date,
		[details_received_date_id]: delivery_date,
		[details_order_id_id]: order_id,
		[details_marketplace_id]: marketplace,
		[details_store_name_id]: store_name,
		[details_purchase_key_id]: purchase_key,
	};

	// If there is a delivery date, state is delivered.
	if (delivery_date)
	{
		data[details_state_id] = "Delivered";
	}
	// If there is a ship date, state is shipped.
	else if (ship_date)
	{
		data[details_state_id] = "Shipped";
	}
	// If we have no ship or delivery dates then it's purchased
	else
	{
		data[details_state_id] = "Purchased";
	}

	var order_shipments_link = 'fld-db2fcdb4d79c466ea09671c47d2ae645';
	var order_shipments_records = order.getFieldValue(order_shipments_link);

	var shipments_tracking_number_id = 'fld-c487390743c947969cbe661cff596855';
	var shipments_carrier_id = 'fld-0950c430cb0c41f79c51d43a544b366b';

	var shipping_tracking_number = '';
	var shipping_carrier = '';

	for (var index = 0, count = order_shipments_records.length; index < count; index++)
	{
		if (shipping_tracking_number.length > 0)
		{
			shipping_tracking_number += " ";
		}
		
		if (shipping_carrier.length > 0)
		{
			shipping_carrier += " ";
		}
	     
	    shipping_tracking_number += order_shipments_records[index].getFieldValue(shipments_tracking_number_id);
	    shipping_carrier += order_shipments_records[index].getFieldValue(shipments_carrier_id);
	}

	data[details_shipping_tracking_number_id] = shipping_tracking_number;
	data[details_shipping_carrier_id] = shipping_carrier;

	console.log(JSON.stringify(data));

	// Last but not least push the new record.
	var details_id = 'fld-ac04de32d98242b88333977c89526fc1';
	var detailsRecord = record.addNewRecordToField(details_id);
	detailsRecord.setFieldValues(data);
	document.saveAllChanges();

	return "Created child record for " + title;
}

And here’s the full document structure (using the build tree script) of my purchases document:

Purchases: (frm-efc0199a2b1543f79e722383014533b0)
	'Image 01' photo (fld-e631165b67374734a3b8f384708b5922)
	'Title' text (fld-0d0edd2552ea461e929f806a4e5552b5)
	'Subtitle' calc (fld-45ef928f87e24bcd93e6751c8c21a6cb)
		Referenced Fields: 
		 - State (fld-9402f8c0d53c43b986fee4ebc3468929)
		 - Colour (fld-a8626656cc90455ea9336dd2488d4aef)
		 - Category (fld-6fdd09891a8c4d73be1b24aa07d077be)
	'State' text (fld-9402f8c0d53c43b986fee4ebc3468929)
	'Previous State' text (fld-636a7a4671c14877b1b17ea1b579cef5)
	'State Watcher' script (fld-45463af0b409465ea78ad7c498ee896d)
	'Colour' text (fld-a8626656cc90455ea9336dd2488d4aef)
	'Category' text (fld-6fdd09891a8c4d73be1b24aa07d077be)

	=== 'Main' section (fld-76597ce17f924c25bbcb195df984331c) ===
	'Date Created' date_created (fld-0d049abe706b41afb680ab9a1bf99d46)
	'Date Modified' date_modified (fld-59a06347614e48e8bf547a855b781582)
	'Purchase Date' date (fld-ccbd9a8f51d34246bebfb31aa4e397dd)
	'Ship Date' date (fld-cb3a9886ac7f4ec487447801a3911a1a)
	'Received Date' date (fld-bb17d48e41c7423692ab586f6c884d05)
	'Last Worn' date (fld-c275ddef83824707b5fcccb5e0698768)
	'Order ID' text (fld-e3e66a0f2e5c4df7b9496f65355e0bcf)
	'Marketplace' text (fld-c163aba17ae64c4d93b5a53819a139dc)
	'Store Name' text (fld-3d98dc6cdcae4a88909c97c80dde7bfb)
	'Brand' text (fld-1e250019d7b249f282cc572814d3e71d)
	'Source' web_site (fld-da9d866bf3ca4d47aade04a77efd7301)
	'Source Scraper Script' script (fld-429e3e7ca20a49d38b26417e25e6db26)
	'Item Key' text (fld-ae7379d699e9473aa2ab16a2a2f002d4)
	'Price' number (fld-08129d71ab0f4fa4a2749456281fca07)
	'Shipping Tracking Number' text (fld-6ea45a9c141343628940bfbcfa38ee90)
	'Shipping Carrier' text (fld-12644a7a4ae24ed8a7926123832d3557)

	=== 'Storage' section (fld-f99f779335f54b9cb0a4179a90bb97dd) ===
	'Bag Barcode' text (fld-32d459f0b5fb4dc4974795c484832af1)
	'Storage Box' text (fld-c08e3a9eb7784d7f8ee3a5576c0adffa)
	'Attributes JSON' text (fld-f95fdbc7b2de4b6e8c3efb46c4c5452b)

	=== 'Product Data' section (fld-5a42b3a215d947399c120078ea868672) ===
	'Seller Category' text (fld-c89e0cb1479e4aa7bace4532320ab697)
	'Title (Native)' text (fld-7b07b948fcee448daa06c41759e60233)
	'Notes' note (fld-bf19d52c18cb4f5198df191ef7902e1b)
	'Note Parser Script' script (fld-7ad209e1e70a4d53985fd229d122bcfd)
	'Product Details' note (fld-f4e804c1869740a4bfd99a9adcfb3c49)
	'Attributes' table (fld-6b2e26f53e6c4f0fb7ebc14400b4f118)
		- 'Key' text (fld-1ff8c9d03e5e48beac09bdf639c0b286)
		- 'Value' text (fld-6a145374b8774cfca13fdc0c1756d00f)
		- 'Date Created' date_created (fld-ff31511cb4f54ce1b9f7ba85a8c0f43f)
	'Attributes Extractor Script' script (fld-134d8663f295429e8e671e4e445e16d2)
	'Variant Data' table (fld-eb212e705eb34e9ea5cc4386ea7a9b1f)
		- 'Date Created' date_created (fld-482c2129901640299867923ced44ea01)
		- 'Value' text (fld-e4ce093c1c22416192eb80554272d6cd)
		- 'Key' text (fld-ecc1b1ede8414912a63ec144012fa9e9)
	'Features' table (fld-1c20f096120845d98c2be64d2102c135)
		- 'Feature Name' text (fld-0a478f6fce164226afab62946a3cec96)
		- 'Feature Type' text (fld-84bd72ca7f354f8db68d469c427c25d0)

	=== 'Attachments and Relationships' section (fld-339f3e44f6f142ddb5ceb1df699f494d) ===
	'File Attachment' file (fld-c28b98cb3d8a43f98ee65503b30a6658)
	'Order Items' from_form (fld-4862767002cf4aadad853e78dffb2eed) manyToMany 'Order Items' (frm-7a809372942a4031ae4bdf014f69e99b)
	'Gallery' from_form (fld-41cea237bfa44135a788d09b2a390019) manyToMany 'Gallery Items' (frm-1dd95cbe70cc490c972487920317620c)
	'Purchases' form (fld-2dfc5804be564f17bcc3c66fd1080121) manyToMany 'Purchases' (frm-efc0199a2b1543f79e722383014533b0)
	'Shipments' form (fld-263993691f544158a16dd13fdf633562) manyToMany 'Shipments' (frm-ac823b8717fb428fa48b65b0efa4a2c3)

	=== 'Images' section (fld-642fef453ecb4d32b74d2e34993da182) ===
	'Image 02' photo (fld-28b749eeaff24492a730e427364ca683)
	'Image 03' photo (fld-1f0ae66bce7e4b63981b244e40ce4366)
	'Image 04' photo (fld-3cf1f20172104500af3070b96da2528e)
	'Image 05' photo (fld-0a8930a83d1f41b68012178ffe54d2ab)

	=== 'Linking Metadata' section (fld-fd8627a7114a431bb9199bdc2bd67ad8) ===
	'Order Line Number' number (fld-da763fa0946d4be79039b4e828cf85f4)
	'Purchase Key' calc (fld-8be9b2c2603f458f8349082237c41964)
		Referenced Fields: 
		 - Marketplace (fld-c163aba17ae64c4d93b5a53819a139dc)
		 - Store Name (fld-3d98dc6cdcae4a88909c97c80dde7bfb)
		 - Order ID (fld-e3e66a0f2e5c4df7b9496f65355e0bcf)
	'Shipment Key' calc (fld-7652273e8d02496f8f8ebc6c46d93230)
		Referenced Fields: 
		 - Shipping Carrier (fld-12644a7a4ae24ed8a7926123832d3557)
		 - Shipping Tracking Number (fld-6ea45a9c141343628940bfbcfa38ee90)
	'Modified Age' calc (fld-9e5bc8ce982c4a468c4b66cad92ecc5b)
		Referenced Fields: 
		 - Date Modified (fld-59a06347614e48e8bf547a855b781582)
	'Purchase Age' calc (fld-9675f0fb104847f0a9705da74bb8bd1a)
		Referenced Fields: 
		 - Purchase Date (fld-ccbd9a8f51d34246bebfb31aa4e397dd)
	'UUID' calc (fld-13790925689a4a189aee77b2c4d0fcb6)
	'Sample Calc' calc (fld-cf5f7b65e683481696a7e864ab39b3e5)
		Referenced Fields: 
		 - Title (fld-0d0edd2552ea461e929f806a4e5552b5)
		 - Order Items::Title (frm-7a809372942a4031ae4bdf014f69e99b::fld-39ca9564ef2347ac93f933bc9a2316ac via fld-4862767002cf4aadad853e78dffb2eed)
		 - Variant Data::Key (frm-efc0199a2b1543f79e722383014533b0::fld-ecc1b1ede8414912a63ec144012fa9e9 via fld-eb212e705eb34e9ea5cc4386ea7a9b1f)
		 - Purchases::Purchase Key (frm-efc0199a2b1543f79e722383014533b0::fld-8be9b2c2603f458f8349082237c41964 via fld-2dfc5804be564f17bcc3c66fd1080121)
	'Orders' from_form (fld-7bdce35a95dc42d596861eedf729eb73) join 'Orders' (frm-6c5adbc73d9f498c978b637c60d19561)
			 ON Purchases.Purchase Key == Orders.Purchase Key
	'Sync Toggle' check_mark (fld-b784d5a9b3bf435b93b71a20baa4d983)

Orders: (frm-6c5adbc73d9f498c978b637c60d19561)
	'Purchase Date' date (fld-bc2e4b152dee42ac9361539a6e37cb5d)
	'Marketplace' text (fld-fa37906add2942c88bce3b500561c42d)
	'Order ID' text (fld-8228040b4641449b96aabfaea15f1ac5)
	'Store Name' text (fld-c153da2f9a504be4b6fee4b5b62a1c11)
	'Store Name (Alt)' text (fld-9d12d614867b49d78ad1b9a5958d9bcd)
	'URL' web_site (fld-fc8fb7f30fe4459495600e4d396160c2)
	'URL Parser' script (fld-868cd41df6754bed94f90174262373a0)
	'Shipping Cost' number (fld-288cfa093d164eb79f0432c722c71ba4)
	'Sales Tax' number (fld-42664ebbbe98429c902b8669551d0d6e)
	'Order Cost' number (fld-acdbf6132877436d8f86d12a7a732a13)
	'Order Cost (Items)' script (fld-34223ce7a81d49af8fcf34b498348760)
	'Vouchers' number (fld-3242366818524c3cb6ac65851a49a599)
	'Total Cost' calc (fld-a4f4eff5a667421fa84fb319bb3f9f30)
		Referenced Fields: 
		 - Order Cost (fld-acdbf6132877436d8f86d12a7a732a13)
		 - Shipping Cost (fld-288cfa093d164eb79f0432c722c71ba4)
		 - Sales Tax (fld-42664ebbbe98429c902b8669551d0d6e)
		 - Vouchers (fld-3242366818524c3cb6ac65851a49a599)
	'Currency' text (fld-a069572060fe46daa4f151f7b4ff84cc)
	'USD Total' number (fld-568f8cf940824f70bd12d2d5bf8091f2)
	'Payment Method' text (fld-84341c7fc70f43f29bdc277a879a3d6f)
	'Statement Entry' text (fld-f18af4bc1ed047f2a4820c6bfe507688)
	'Note' note (fld-eb7bbf4f816844739549ae82b19ea8eb)
	'Shipping Address' note (fld-d85b9118ea0346bea2b29c337c68bc60)
	'Ship Date' date (fld-6ab700ccc11d418fbd27d8899d00c7a9)
	'Delivery Date' date (fld-4b1c4180dc1b4bb08b32d16fa60cae66)
	'Order Item' form (fld-9db0c7698499435ab6b18b7eb420e0ae) toMany 'Order Items' (frm-7a809372942a4031ae4bdf014f69e99b)
	'Shipments' form (fld-db2fcdb4d79c466ea09671c47d2ae645) manyToMany 'Shipments' (frm-ac823b8717fb428fa48b65b0efa4a2c3)
	'Purchases' form (fld-07c0ec05287a48c9a67b64d816007518) join 'Purchases' (frm-efc0199a2b1543f79e722383014533b0)
			 ON Orders.Purchase Key == Purchases.Purchase Key

	=== 'Calcs' section (fld-41cdc6a954524c05b9ec9e04c878f150) ===
	'Alternate Order ID' text (fld-e0cc0e4c813848d6bb1dbe7fb5574c4a)
	'Alternate Order ID Autocomplete Script' script (fld-75daa7631d7d4a10821b7cdd1df43104)
	'USD Autopopulate Script' script (fld-89d9508a06594cf7bc5f4409699b796d)
	'Shipping/Delivery Autopropagate Script' script (fld-544b7acca8b64e98b78bef29cdc6ac2c)
	'Purchase Key' calc (fld-3e49aaa5bc32429c8f0f0f234878356d)
		Referenced Fields: 
		 - Marketplace (fld-fa37906add2942c88bce3b500561c42d)
		 - Store Name (fld-c153da2f9a504be4b6fee4b5b62a1c11)
		 - Order ID (fld-8228040b4641449b96aabfaea15f1ac5)
	'USD Total Calc' number (fld-a50be30e2a974dee84a68195ffac913f)
	'Aggregate Shipping Tracking Numbers' script (fld-7e8c8aa428304e53b9a559b6e6651d94)
	'AutoURL' script (fld-b658ac413ef04b298d57121cc1206f82)

Order Items: (frm-7a809372942a4031ae4bdf014f69e99b)
	'Title' text (fld-39ca9564ef2347ac93f933bc9a2316ac)
	'Title (Native)' text (fld-ef385456de924ee2ade8db3fec9415c7)
	'Transaction ID' text (fld-9330e89b024e40cc86f541dc4c3fe686)
	'Price' number (fld-a2973999a60d4319baf0b4480d6f57a0)
	'Quantity' number (fld-39379cdff743496f9a1ccbdc1ae56297)
	'Line Number' number (fld-f95b68d488cb4b058bbf3de84e1a7c3b)
	'Total Price' script (fld-a2339a503f3d458ebfc0f9e7aa831017)
	'Note' note (fld-d0cfab9ec09d497294fbd8b5b52caf16)
	'Note Parser' script (fld-f1a20232b9ab4286b790fa57c4c4b0cc)
	'Details' form (fld-ac04de32d98242b88333977c89526fc1) manyToMany 'Purchases' (frm-efc0199a2b1543f79e722383014533b0)
	'Order' from_form (fld-c3a6725d7d9446da92bbb880ffe90a9e) toOne 'Orders' (frm-6c5adbc73d9f498c978b637c60d19561)
	'Shipments' from_form (fld-0489f6c24c50466f9348313584893941) toOne 'Shipments' (frm-ac823b8717fb428fa48b65b0efa4a2c3)
	'Gallery' from_form (fld-6ffd4369da0b418fb790a975f69a0ab2) manyToMany 'Gallery Items' (frm-1dd95cbe70cc490c972487920317620c)
	'Default Values' script (fld-7c7222480794423a844685e6bd5954ab)
	'Date Created' date_created (fld-6dcd45dd73a4463ca21378bf4ea48c69)
	'Date Modified' date_modified (fld-b97807466ad044d0a646e06017db8ea1)

Script Manager: (frm-a311565fe3614c5ca97a3942a2973450)
	'Installed Version' calc (fld-c45a76a8b28b4546821f0a76d6076621) - calculation field missing formula!
	'Source' calc (fld-6ecd7dbcad784799b651945616fc4e26) - calculation field missing formula!
	'Enable Updates?' check_mark (fld-077d7d4c5619419abb25c7e513e61697)

Shipments: (frm-ac823b8717fb428fa48b65b0efa4a2c3)
	'Tracking Number' text (fld-c487390743c947969cbe661cff596855)
	'Carrier' text (fld-0950c430cb0c41f79c51d43a544b366b)
	'Tracking Details' script (fld-7a29242731d9451092c92d8586dbc94a)
	'Alternate Tracking Numbers' table (fld-cf8718051bea4cc2aba0069ae76f32b7)
		- 'Carrier' text (fld-193ef28f49c04c73affcfbba09001524)
		- 'Tracking Number' text (fld-7342203d8f36415191bf8419fb6f70dc)
		- 'Notes' text (fld-82a805c52d3c4408a775a3fc04bdc19f)
		- 'Tracking URL' web_site (fld-de244f5ddb5d4f4ba86f218d7c0bf141)
	'Tracking URL' web_site (fld-83e7a9bd104e495fb48de1da74b00c43)
	'Tracking URL Autocomplete' script (fld-dddcdc15e1c44aa4a99bba6314dc7a07)
	'Shipping Date' date (fld-1aa32f17e059424fb4e24bf894b34fdf)
	'Received Date' date (fld-e3e3539ee04f4cc7971c7098c572104d)
	'Unverified' check_mark (fld-abdb319b7db74fc39812a94778c433cc)
	'Note' note (fld-b6352a3e22ca4a7d966cf4a216a7c135)
	'ZIP Code' number (fld-4f73faa8937446a0a3b24e6dd4624d6b)

	=== 'Join Fields' section (fld-42dce0fcedce4368b334ab72b765e7e3) ===
	'Date Propagation' script (fld-f7aba3b5ddd6430cb8e9a211e0086c84)
	'Order Item' form (fld-f3c8f85d6dd14c2f8199d050ad7fc5f9) toMany 'Order Items' (frm-7a809372942a4031ae4bdf014f69e99b)
	'Purchases Join' form (fld-5c4a2d1aadad4005a82d424216d1bb7b) join 'Purchases' (frm-efc0199a2b1543f79e722383014533b0)
			 ON Shipments.Shipment Key == Purchases.Shipment Key
	'Shipping Events' table (fld-1ed7b8bddb8a463fa10b94ac11d64ecd)
		- 'Date / Time' date_time (fld-6d279f9411c8497d9f6dcd2d74c01ef6)
		- 'Location' text (fld-93730ed5a3ab41749b972d17c8c6880f)
		- 'Message' text (fld-c861230085ab4b46ac05feab59a5add0)
	'Shipment Key' calc (fld-201444109af7460d831ce21a96087e13)
		Referenced Fields: 
		 - Carrier (fld-0950c430cb0c41f79c51d43a544b366b)
		 - Tracking Number (fld-c487390743c947969cbe661cff596855)
	'Date Created' date_created (fld-8b02c9de87d240dba0aa9b774a5deca1)
	'Purchases' from_form (fld-691985f25f2c4ce8b36dcc112a3ae600) manyToMany 'Purchases' (frm-efc0199a2b1543f79e722383014533b0)
	'Orders' from_form (fld-6ee418159c264ff5bb1b5da1fde43e2f) manyToMany 'Orders' (frm-6c5adbc73d9f498c978b637c60d19561)

Gallery Items: (frm-1dd95cbe70cc490c972487920317620c)
	'Image' photo (fld-66620a6542a14b4eb3df98aa19f0afac)
	'Title' text (fld-c4919a9d54c142b78eaf0fef3cc91e73)
	'Keywords' text (fld-de6f53f4658f4321a77377f6bb9a736c)
	'Source' web_site (fld-c32969738e4c4ab7947c53054c82bef8)
	'Source Scraper Script' script (fld-0a45936b78e847fe8aeb2da089ac3fae)
	'Purchases' form (fld-c5ae0e2263334e07ab558734ba6f4f9c) manyToMany 'Purchases' (frm-efc0199a2b1543f79e722383014533b0)
	'Order Items' form (fld-c352a2dfa2884fb7ab9f380a08860ba3) manyToMany 'Order Items' (frm-7a809372942a4031ae4bdf014f69e99b)
	'Date Created' date_created (fld-721df5a648de40a194a40bb8ab7d1946)
	'Date Modified' date_modified (fld-4655757bd1824f70ad42fc558776f65b)
	'Notes' note (fld-f6ce1405bd7a435080c03f8b0598c9f5)
	'File Attachment' file (fld-b2f3efd087fb4d5f853309ef79ab3d31)
	'Metadata' table (fld-7ccfc47e23194d6b8856632dabb3097d)
		- 'Value' text (fld-4bf84f891e544ba6a03c56d1978ef4b2)
		- 'Date Created' date_created (fld-8369762f3c7b410ab7f5c92dd87f132c)
		- 'Key' text (fld-d5e45b1c9a184aaeb26622a7eae3ca8f)
	'Extended Metadata' text (fld-b8e7d09b55384e45a9922bf652e05f96)

Desirable Acquisitions: (frm-20f8844810bb4884a22ab1a3c0e143a4)
	'Name' text (fld-bdd887ce763243bc847afdf6f2549f52)
	'Notes' note (fld-c3920264a1b54927831a8984de7ea984)
	'State' text (fld-9cebaa1a64254c4ba4bd064eeeb72a81)
	'Date Created' date_created (fld-361ec793242e40b38657d9563681dab9)
	'Date Modified' date_modified (fld-6845c7dbbeaf4ac9915cee48b00c5f72)
	'Photo' photo (fld-b9ad86e8707c460885002b828b8dadc1)
	'Web Site' web_site (fld-e0b4c7a6bb82474297b38e2ccc1fe39a)
	'Acquisition Samples' form (fld-2eb1061b9fce4dd5a5508896c22c3784) toMany 'Acquisition Samples' (frm-34193fbd729f498194bff2d11bec5de0)
	'Purchases' form (fld-3314dab138a14046862da7dc3700e11c) manyToMany 'Purchases' (frm-efc0199a2b1543f79e722383014533b0)
	'File Attachment' file (fld-0287b866efa0428d9dc417d7531b39c3)

Acquisition Samples: (frm-34193fbd729f498194bff2d11bec5de0)
	'Title' text (fld-e2f18890c0344e82a6486704ea65f527)
	'Notes' note (fld-ad2c2752eefb4d4eb96220ff1b042ad9)
	'Source' web_site (fld-25b14dc7565d40a1b58121554ba8a4aa)
	'Price' number (fld-72464af6aea84a3d8e26a8c17f39738f)
	'Pictures' photo (fld-265e6017684547fb8cb0a891a89e35c8)
	'Desirable Acquisitions' from_form (fld-6b1b4faa00094613a1ce44c582393d36) toOne 'Desirable Acquisitions' (frm-20f8844810bb4884a22ab1a3c0e143a4)
	'File Attachment' file (fld-c59caf13ff1941acbc700b281feb7a72)

[Sam Moffatt]

I’m happy to help folk on the forum, means everyone gets to learn and everyone can pitch in to help if they know the answer :) I was always taught if you have a question then you’re probably not the only one, so it’s helpful for others to see.

The beauty of Tap Forms, especially version 5, is that you can work on either platform (iOS or Mac) and with the sync the changes get replicated to each device. I used P2P for the longest time but now I have CouchDB for everything (I have one iCloud Document I do testing with). Each platform requires a purchase but I feel it’s worth it.

One trick you can do for looking at the same form whilst you edit scripts or calculation is to open a new window pointing at the same document. There are some caveats with it so I usually leave it read only but you can use that to put stuff side by side for reference. Just keep your main window for changes and the second window for reference.

[D J Leason]

@Sam Moffatt

Thanks, Good to know. I am reading about JavaScript now. Not sure I have time to learn it all in it’s entirety, but I will learn the basics. I think that would come in handy for the small screen of an iPhone. Can I feel free to ask you questions form time to time? I’ll understand completely if you don’t have time.

@DJ

[D J Leason]

@Brendan,

Oh. OK. Thanks. Good to know. Should I go ahead and download the iOS versions for iPad and iPhone to make sure it works there or should I wait until I complete the database. Is there a 5.3 version of iOS available and is it included with the Mac version 5.3? Is there a link to the download for it? Is iPhone 6s+ still supported?

Are the Tap Forms syntax rules the same as for JavaScript? Are spaces ignored in calculation editor? Is there a way to move the calculations editor window so you can see refer to the form as you enter stuff?

[talal alotaibi]

hello

I wish everyone a happy day

How to use a tool larger than or smaller than with a replacement

Example

if X>Y print “No balance”

Can I do it through the calculator
Or should be placed in a script

If you put it in a script, add it in the form

[Sam Moffatt]

@djleason yes there is a prompter that let’s you popup windows and then store it’s result somewhere, there is an example at the bottom of the JavaScript API page on how to use the prompter. You can use a prompter within a form script to populate regular fields in your form or create other records as is the example I provided.

You need to wrap the date fields with a DATE([Field]; "yyyy-MM-dd") function to format them instead of returning the raw timestamp you’re seeing.

The other thing would be to guard your field accesses or ensure they have default values set. There’s a warning on the Calculations page about if not all fields have values in them for your record, you will get an empty result.

I also personally find using JavaScript a little easier to debug than the calculation fields, the JS editor will let you run your script inside the editor to preview it’s value making the develop/debug cycle quicker and it is usually good about giving you errors for where you made a mistake. The downside is that is a level lower than the calculation fields and can be a little confusing to map across the field ID’s and values all of the time compared to the calculation editor. I realise I might be unique here as I’ve been working with JavaScript for roughly two decades now back when it was a limited tool for interacting with web pages.

[Peter Riley]

This is brilliant, thanks. Until now I have been tracking my TV show progress with a spreadsheet, but this script does a much better job.

One issue though – the table seems to miss some episodes, even though they’re there on IMDb (see attached).

Attachments:

You must be logged in to view attached files.

[D J Leason]

Replying to Sam Moffat
Prompter? Are you saying that using JavaScript, you can write code within Tap Forms, allowing you to make a window pop open and prompt you for answers to either single or multiple choice questions then add those answers to a record and use them in calculations? If so, are you a teacher?

[Sam Moffatt]

If you’re willing to learn some JavaScript, JS has some options here as well and a lot more power around linked forms if this is relevant.

– Combine multiple values from a child Link to Form fields into a single value
– Form Script to quickly add a new record in Link to Form

[Ryan Stewart]

This script does almost exactly what I want to do, but I can’t get it to function exactly.

I’d like to run a form script that moves a field in FORM A to a new record in FORM B. The appropriate JSON is generated, but the new record is not being created as expected.

Anyone care to take a look? Thanks in advance.

// Posted: Case Field ID
var posted_case_id = 'fld-bd5454484fab41d1a4fd75cf8af6f358';
var posted_case_value = record.getFieldValue(posted_case_id);

// Perioperative: Record Field IDs
var periop_case_id = 'fld-416eb25e66aa48af949e528a2132d00e';

function move_to_periop() {
	
	var data = {
			[periop_case_id]: posted_case_value,
		};
	var periopRecord = record.addNewRecordToField(periop_case_id);
	periopRecord.setFieldValues(data);
	document.saveAllChanges();	
	console.log(JSON.stringify(data));	
}

move_to_periop();

[Sam Moffatt]

I posted a field script that does this with a pick list field over in the Script Talk forum under “Populate image based on pick list”. It’s not an ideal solution but demonstrates how to achieve what I was talking about. If you only edit on your own Mac, then you can put the images locally and use MacOS’ built in web server and use that to host the files you want to include. It’s not a great solution to the problem but it is an example of how to solve it with a script field. Would love to see custom embedded layouts for Link From Form fields because I think that would solve your problem here.

[Sam Moffatt]

In Linking Images in a Database an ask was made to pick images and whilst that’d be cool to see somehow with form links, I decided to implement this as a quick field script as an example though a Link from Form field with the ability to embed a custom layout I think would be a cool way to solve the problem..

This script borrows some publicly available images to solve the problem and uses a picklist to limit choices. I noticed I needed to hit refresh because the field script wasn’t being triggered. It will also not do anything if an image is already there, you have to manually delete the image to change it. Very simple sample form attached as well.

var disc_type = record.getFieldValue('fld-d829341555114d4fb0a1f6d05a80930a');
var disc_type_logo = record.getFieldValue('fld-9cd36df80c1141d4a87f59496222ebb0');
console.log(disc_type);
console.log(JSON.stringify(disc_type_logo));

if (!disc_type_logo.length)
{
	var url = '';
	switch (disc_type)
	{
		case 'CD':
			url = 'http://pluspng.com/img-png/file-cd-logo-png-1024.png';			break;
		case 'DVD':
			url = 'http://pluspng.com/img-png/dvd-movie-png-dvd-logo-600.png';			break;
		case 'Bluray':
			url = 'http://pluspng.com/img-png/ama-flat-track-logo-vector-png-blu-ray-disc-logo-400.png';
			break;
		case 'LP':
			url = 'http://pluspng.com/img-png/turntable-hd-png-turntables-png-cliparts-2640178-1000.png';
			break;
	}
	if (url.length)
	{
		record.addPhotoFromUrlToField(url, 'fld-9cd36df80c1141d4a87f59496222ebb0');
	}
}
else
{
	console.log('Skipping setting logo');
}

console.log('All done');

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

TapForms uses CouchbaseLite behind the scenes and that exposes a CouchDB compatible stream. Each change that happens to a document in TapForms is propagated to a change log which can be used for replication or in your case, recording each individual change that happens.

There are a few complications here:

• This change log isn’t guaranteed to be comprehensive. Some changes maybe squished together for the sake of efficiency and historic records may be removed to reclaim space. This may happen manually if you click “Compact Database” under preferences or it may happen automatically.
• If you are recording this via an iOS device and then sync’ing to your Mac using the CouchDB based replication options (P2P, Cloudant, CouchDB) then your iOS device will only send the latest change it had. If you have make multiple changes out in the field to a document and then sync them, your Mac will only get the latest change.
• Getting to this log is non-trivial. TapForms uses a randomised port each time the app starts per database which means you have to figure out what that port is via Bonjour/mDNS before you can get to the API. If you sync to an external CouchDB server, getting to the log is a little easier.

I’m not sure how detailed CASA want to be, I suspect by default TapForms is actually more conformant than they’d care to think. If you don’t have iOS, or you’re fine with only one remote change making it back to your Mac, then you can use this API to extract out the raw documents and changes from the TapForms document. In theory you could extract the changes from the iOS devices via the same API but that’s a little more complicated.

If you are interested in setting up a CouchDB instance, you can have TapForms replicate to it and then use it’s change log as your source of truth. If your devices are always online and the CouchDB server is always online then it will obviously replicate the changes as you go automatically.

Ok, so how do we get the changes? The first step is to get an application called “Discovery” from the App Store. Discovery is a Bonjour/mDNS/DNS-SD browser that will let you find instances of TapForms on your personal network. Once you’ve downloaded it, open it up and scroll down to the bottom to find the entry “_tapforms-sync._tcp.” and expand it out. It should list all of the instances of TapForms running on your network with an entry each for the documents that are open. It looks like this:

If you have more than one document open, you’ll need to know the ID of the document that you’re interested in. You can find this in the Preferences pop over for your document under the “Sync” tab

Once you’ve got this document ID, you’ll need to expand it out and copy the line that has the IP address on it, in my case “10.0.1.145:57343”. You’ll need to use this with the database ID to get the change log.

Next up, grab “Terminal” from “Applications” > “Utilities” and run a command like this:

curl -k https://[YOUR_IP_ADDRESS:YOUR_PORT]/[YOUR_DB_ID]/_changes?include_docs=true

For me with the example above, it looks like this:

curl -k https://10.0.1.145:57343/db-f8365d0d8a99446db370477295e705fe/_changes?include_docs=true

When you run that, it’ll spit out a raw transaction log of all of the changes that this TapForms instance is currently retaining.

You can checkout the CouchDB docs on the _changes endpoint for some more details about how it works and it’s limitations.

Now the complication of this is that you need to find the new port each time you open and close TapForms. I tried to build some automation for this because I was curious about building an automatic network backup tool to find TapForms instances on my network and automatically backup their change logs but I never got it finished and it never worked reliably. What I do is I actually use a CouchDB server on my local network and replicate the changes to there. I use that to then build automatic hourly snapshots of my database using GIT.

I have two tools I use to keep backups, the first is a Time Machine based backup solution which I have put up on GitHub. I don’t think this will work for you because that method is inherently lossy. The second one I built scrapes the CouchDB and pulls out the internal revisions of the documents to store to disk. I don’t have that online yet, I need to integrate it properly into the little framework I’ve built but it isn’t a transaction log per se but a per record version history. The challenge with both of these is that inherently they don’t offer non-repudiation and are themselves in theory editable. You control all of the devices and at that point there is no audit pathway. Short of relying upon a third party service that is fully managed by someone else, I’m not sure you could assure an auditor of the veracity of your logs.

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

Originally noted in the general forum, this is a method equivalent to Excel’s VLOOKUP. It’s actually mostly templated from the snippets, the child records loop snippet looks mostly like the vlookup function and the prompter snippet was in the other form script POC.

This will work, it requires using script field instead of calc field but it’s not that bad. You need to tell it the value you want to lookup with, the “join” field (table or link to form), the search field to match the lookup value on and then the field to return.

Create a new form script called “vlookup” with the following contents:

function vlookup(lookup, join_field, search_field, return_field) {
	var entries = record.getFieldValue(join_field);

	for (var index = 0, count = entries.length; index < count; index++){
     	var target = entries[index].getFieldValue(search_field);
		if (target && target == lookup) {
			return entries[index].getFieldValue(return_field);
		}
	}
	return "";
}

Then create a script field to map the values across:

form.runScriptNamed('vlookup');
var addresses_id = 'fld-34e22de8a7cf438fb4a83146108f0511';
var address_name_id = 'fld-f05929829d674141aaed98efe11e29f1';
var street_id = 'fld-04ec2a23e3554770b3e1f1d771157dd6';
var primary_address = record.getFieldValue('fld-9b2865aa57b74b70bd4421b27081d65b');

vlookup(primary_address, addresses_id, address_name_id, street_id);

In the script editor, select the fields from the linked form and use the “ID” button instead of double clicking them to get the var syntax. You’ll want to change the last field to match your fields across.

I’ve attached a sample archive which should demonstrate what I’m talking about. It also has another form script using a prompter to handle the address change but for some reason the script fields aren’t updating afterwards, you have to manually press refresh on the record.

Sample archive link.

[Sam Moffatt]

There is one other way I can think of doing it which would be to use the scripting API to download the image from a known location into the document with either a prompter to pick the type via a form script or a field script watching a text field with a pick list configured. There are two problems with this: the first is that it would duplicate the attachment for each document which could get expensive if you have a lot of records and the second is you’d also need a web server somewhere to download it from. A new API for copying images could work here as well to remove the web server requirement but the duplication is a side effect of copying the image to every document.