Home inventory: Batch processing for assigning items to boxes

[Max Heart]

Hi everyone,

I am trying again to find help. The first round, including some good advise was here:

Inventory barcode scanning – assign box/ placement

Unfortunately, I did not manage to create a workflow as I am not familiar with scripting.

Here’s my goal and setting:
I want to create a home inventory which helps me finding my stuff quickly. I have two forms: one for boxes and one for the items. From the boxes form I created a one to many link as one box contains many items but one item can only be in one box at a time. Each box has a QR code, each item has a bar code.
I managed to create my forms and the relationship and search is working. The barcode search will find items and boxes.

Here comes the challenges:
1) Batch entry of items using barcode
Can I pull up a box by scanning its barcode and then start a batch scan barcodes of items as I place them in the box?
2) Batch checkout of items
Let’s say I pack for a trip and remove a lot of stuff from different boxes. I guess, in the boxes form I should create a box called “Checked Out”. Is it possible to first search for an item, have the box displayed where it is and then move this item to “Checked Out”?

I use my iPhone for scanning but in case this workflow only works with a dedicated barcode scanner on the desktop version, I would be happy to buy one. I am just kind of lost and couldn’t find a tutorial.

Thank you for your help, Max

[Sam Moffatt]

I have recently been working on something like that to scan a bunch of items into boxes and similar. There are a couple of ways of handling it.

I’m going to rephrase some of your questions:

Can I pull up a box by scanning it’s barcode?

This one is relatively straight forward application of the search functionality, that’s easy out of the box regardless of what platform you’re on. On the desktop you’ll need a barcode scanner to make that work but on iOS the built in barcode scanning functionality will make that work for you there.

Can I scan barcodes of items as I place them into the box?

The way I did this in January when I was doing a lot of box packing for moving was to create a second form called “Box Items” that is linked from my main “Box” form and then created child records. I was taking pictures of the item and adding some extra metadata in this case so I just used the built in Tap Forms barcode scanner to scan items and used voice to text to quick enter titles (worked enough times to save me having to type).

Now if you invest in a barcode scanner, you can take it a little bit further and create a prompter loop to scan barcodes and automatically create new child records. You can also pivot and if you’re confident an item will only be in a single box, you can make it’s box barcode a property of the item. I have a prompter loop I use for bulk scanning shipments, I’ll post that on the Script Talk forum later.

Can I checkout of a box?

If you go with the Link to Form and enable show inverse relationship (or what ever that checkbox is called), then when you scan in your item in Tap Forms using the search feature it will show you the record and the “Link From Form” will show you the details of the box that it thinks it should be in. In Tap Forms on the desktop you can select this and change it to search for a new box which you should be able to do by barcode for your checkout box or just have a box named “checkout”. By changing the value of the Link from Form field parent will register it to a new box. The iOS version has a similar interaction here but I feel that it isn’t quite as smooth as the desktop more due to the limitations of iOS rather than Tap Forms (the desktop shows a popover which makes it much easier to interact with).

If you go for the field based approach, you just need to update the field to where ever you put the item.

In both cases you can script it to handle it easier. Scripting the Link to Form/Link from Form is a little more difficult because in the current production release of Tap Forms there is no search by text via the script interface. Brendan mentioned that he’s adding a form.getRecordsForSearchTerm() to the next beta which would make that easier because the JavaScript API for the link fields works with records. Until then, it’s a little easier to update a simple text field based on a search.

I have a dedicated barcode scanner for the desktop, I bought a NETUM Bluetooth scanner that has both a USB cable for input and can pair via Bluetooth. I’m not sure I’d recommend it, it works fine in Bluetooth mode but when I plug in the USB cable it mangles the numbers some how (it’s like it’s pressing on the keypad numbers upside down). On the Mac I use Karabiner Elements whose mere existence seemed to make the scanner behave properly.

[Max Heart]

Hi Sam,

thank you very much for the advise. I am happy to hear that my objective should be reachable. When you post your script can you please give me a hint on how to get it running? I haven’t worked with scripts in Tapform yet. I know that “script” is a field type. Is it enough to just paste a script there?

Best, Max

[Max Heart]

Hi again,

I just followed your advise and for the manual quick linking of items from box to checkout and back it works. Thank you very much for this part.

Now it would be awesome to run this procedure script based: Scan a box and keep assigning items to this box by batch scanning their barcodes until the script is stopped.

So the script should do this:
1) Ask for a box to scan
2) Pull the box record from the box form
3) Prompt for barcode
4) Read barcode, assign the item to box, best with short feedback for 3-5 seconds “Item XX added to box”.
5) Ask for next barcode
6) Repeat procedure until stopped.

@Sam: Is this what your shipment script does?

[Sam Moffatt]

I’ll walk through this a little as this is my bulk scan script. It’s probably a bit much but it has a bunch of piece that might help.

document.getFormNamed('Script Manager').runScriptNamed('Prompter Functions');

This is pulling in my Prompter Functions via the Script Manager method. Basically Script Manager is a Tap Forms form named ‘Script Manager’ and then it uses ‘Form Scripts’.

var tracking_number_id = 'fld-c487390743c947969cbe661cff596855';
var received_date_id = 'fld-e3e3539ee04f4cc7971c7098c572104d';
var confirmed_id = 'fld-2adb9ba8cdd048bbbb614d46b415ada5';
var alternate_tracking_numbers_id = 'fld-cf8718051bea4cc2aba0069ae76f32b7';
var alternate_tracking_number_id = 'fld-7342203d8f36415191bf8419fb6f70dc';
var carrier_id = 'fld-0950c430cb0c41f79c51d43a544b366b';
var zip_code_id = 'fld-4f73faa8937446a0a3b24e6dd4624d6b';
var shipper_id = 'fld-1789ae45fd1f4f588294eff0c2fb6045';

These are all of the field ID’s I care about, you’ll see them spliced through. Obviously your fields will be different.

function findRecord(targetTrackingNumber)
{
	var targetRecord = null;
	MainLoop:
	for(candidateRecord of form.getRecords())
	{
		if (targetTrackingNumber == candidateRecord.getFieldValue(tracking_number_id))
		{
			targetRecord = candidateRecord;
			break MainLoop;
		}
		
		
		for (alternateRecord of candidateRecord.getFieldValue(alternate_tracking_numbers_id))
		{
			if (targetTrackingNumber == alternateRecord.getFieldValue(alternate_tracking_number_id))
			{
				targetRecord = candidateRecord;
				break MainLoop;
			}
		}
	}
	
	return targetRecord;
}

This is a quick method to basically brute force scan all of the records and search for them. I mentioned earlier that Brendan is adding a function to make this a little less relevant however what it’s doing is getting a list of all of the records, checking if the tracking number matches (in your case it’ll be your box barcode or item barcode) and it also checks the values of a table field that is in my shipments form. Because there are two levels of loops in this, I use a trick to make sure I break out of them easily. Shipping is fun where a package might have more than one tracking number, hence the crazy setup.

async function start(){
	try {
		while (1)
		{

This is the start of the meat and it has three things to be interested in. The async keyword is required to get my prompter functions to work properly and enable you to do blocking calls without having to manage the callbacks all yourself. The try is a fallback for an exceptions that might escape and the while (1) is our infinite loop.

			let zipcode = undefined;
			let carrier = '';
			let barcode = '';
			let scanBarcode = await promptText("Scan Shipment", "Barcode:");

This sets up some variables, in my case I’m tracking zipcode for the package (USPS embed this into their tracking number), a carrier that I can automatically infer (USPS and UPS) and then obviously the core barcode. This uses the promptText function which is in that Prompter Functions I mentioned earlier. It has await prefixed to make it block until we get a response back.

			if (!scanBarcode)
			{
				break;
			}

This is pretty straight forward, if we don’t get anything back from the barcode then we abort the loop. This is our exit criteria.

			let matches = scanBarcode.match(/420(9[0-9]{4})(.*)/);
			if (matches)
			{
				carrier = 'USPS';
				zipcode = matches[1];
				barcode = matches[2];
			}
			else
			{
				barcode = scanBarcode;
			}

This isn’t relevant to you as much but it basically looks for a USPS barcode and extracts the useful information out of it. It resets the barcode to be what the label and tracking number actually is versus the full details that are encoded into the barcode. I’m in California so my zipcode is prefixed with a 9 which is why it looks the way it does.

			matches = scanBarcode.match(/^1Z/);
			if (matches)
			{
				carrier = 'UPS';
			}

This also isn’t as relevant but it looks for a UPS style barcode and sets it automatically. Depending on how your barcode generation is done, this might be something you can apply where you have different barcode prefixes for stuff (or not).

			console.log(barcode);
			
			let targetRecord = findRecord(barcode);

I logged the barcode because I wanted to see what it was in the console. This is useful for understanding when something weird happens. This is then fed into that findRecord method before to find a record that matches, or not.

			if (targetRecord)
			{
				// Flip confirmed flag but otherwise leave it alone.
				targetRecord.setFieldValue(confirmed_id, true);
				document.saveAllChanges();
				console.log("Updated existing record for " + barcode);
			}

For me I’m looking to confirm or create a record that I’m scanning. In this case if for some reason the shipping number already exists and I found a matching record, I just toggle a flag saying that I know it exists and move on.

			else
			{
				let payload = {
					[tracking_number_id]: barcode,
					[confirmed_id]: true
				};

Ok, the else case means this tracking number doesn’t exist already so we need to create it. I start to create a new record here. This syntax with the square brackets is to get the value of tracking_number_id instead of using tracking_number_id as the key.

				if (carrier)
				{
					payload[carrier_id] = carrier;
					payload[zip_code_id] = zipcode;
				}

If there is a carrier set then this gets set up as well including the zipcode (USPS).

				let shipper = await promptText("Enter Shipper Name", "Shipper: ");
				console.log(shipper);
				
				if (shipper)
				{
					payload[shipper_id] = shipper;
				}

I ask for the shipper name in case that’s obvious, again with the promptText method. That’s useful for knowing where something is from if I want to add it in.

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

				let newRecord = form.addNewRecord();
				newRecord.setFieldValues(payload);
				document.saveAllChanges();

I log out what I’m about to create to see what it is during debugging. I then create a new record, use setFieldValues to set up the values and then save the changes. Too easy!

			}
		}
	} catch (error) {
		console.log("Error: " + error);
	}
}

start();

This is closing everything out and then triggering the script to begin with. The catch is the follow up to the try and is a fail safe to log the message and go from there. It’s closing out the loop so assuming you enter in a valid barcode, it’ll keep looping until it’s done.

I thought I had another script that handled a little closer to what you were doing but I can’t find where I put it.

Here’s the script in full:

document.getFormNamed('Script Manager').runScriptNamed('Prompter Functions');

var tracking_number_id = 'fld-c487390743c947969cbe661cff596855';
var received_date_id = 'fld-e3e3539ee04f4cc7971c7098c572104d';
var confirmed_id = 'fld-2adb9ba8cdd048bbbb614d46b415ada5';
var alternate_tracking_numbers_id = 'fld-cf8718051bea4cc2aba0069ae76f32b7';
var alternate_tracking_number_id = 'fld-7342203d8f36415191bf8419fb6f70dc';
var carrier_id = 'fld-0950c430cb0c41f79c51d43a544b366b';
var zip_code_id = 'fld-4f73faa8937446a0a3b24e6dd4624d6b';
var shipper_id = 'fld-1789ae45fd1f4f588294eff0c2fb6045';

function findRecord(targetTrackingNumber)
{
	var targetRecord = null;
	MainLoop:
	for(candidateRecord of form.getRecords())
	{
		if (targetTrackingNumber == candidateRecord.getFieldValue(tracking_number_id))
		{
			targetRecord = candidateRecord;
			break MainLoop;
		}
		
		
		for (alternateRecord of candidateRecord.getFieldValue(alternate_tracking_numbers_id))
		{
			if (targetTrackingNumber == alternateRecord.getFieldValue(alternate_tracking_number_id))
			{
				targetRecord = candidateRecord;
				break MainLoop;
			}
		}
	}
	
	return targetRecord;
}

async function start(){
	try {
		while (1)
		{
			let zipcode = undefined;
			let carrier = '';
			let barcode = '';
			let scanBarcode = await promptText("Scan Shipment", "Barcode:");
			
			if (!scanBarcode)
			{
				break;
			}

			let matches = scanBarcode.match(/420(9[0-9]{4})(.*)/);
			if (matches)
			{
				carrier = 'USPS';
				zipcode = matches[1];
				barcode = matches[2];
			}
			else
			{
				barcode = scanBarcode;
			}
			
			matches = scanBarcode.match(/^1Z/);
			if (matches)
			{
				carrier = 'UPS';
			}
			console.log(barcode);
			
			let targetRecord = findRecord(barcode);
				
			if (targetRecord)
			{
				// Flip confirmed flag but otherwise leave it alone.
				targetRecord.setFieldValue(confirmed_id, true);
				document.saveAllChanges();
				console.log("Updated existing record for " + barcode);
			}
			else
			{
				let payload = {
					[tracking_number_id]: barcode,
					[confirmed_id]: true
				};
				
				if (carrier)
				{
					payload[carrier_id] = carrier;
					payload[zip_code_id] = zipcode;
				}

				let shipper = await promptText("Enter Shipper Name", "Shipper: ");
				console.log(shipper);
				
				if (shipper)
				{
					payload[shipper_id] = shipper;
				}
				
				console.log(JSON.stringify(payload));

				let newRecord = form.addNewRecord();
				newRecord.setFieldValues(payload);
				document.saveAllChanges();
			}
		}
	} catch (error) {
		console.log("Error: " + error);
	}
}

start();

[Sam Moffatt]

Ok, so I found the other one which is actually a variation of the one I posted though simplified in a way. It works on two forms: one for boxes and a second for purchases that has a “box ID” field in it as a text field.

This uses the prompter functions again and also getRecordFromFormWithKey to find matching records.

Let’s get started!

var PARENT_SCRIPT = 'Boxes::Bulk Add To Box';
document.getFormNamed('Script Manager').runScriptNamed('Prompter Functions');
document.getFormNamed('Script Manager').runScriptNamed('getRecordFromFormWithKey');

Define the PARENT_SCRIPT to prevent stuff like Profiler running it’s self tests. My internal version of getRecordFromFormWithKey currently has profiler support, I’ll update it later. We need the prompter functions again from earlier as mentioned.

// Boxes
var box_id_id = 'fld-f25492cf56384369b278f7ea41ee258f';

var box_id = record.getFieldValue(box_id_id);

This form I have this in is called “Boxes” and it has a field called “Box ID” which we grab when we start the form script. These are those variables.

// Purchases
var state_id = 'fld-9402f8c0d53c43b986fee4ebc3468929';
var bag_barcode_id = 'fld-32d459f0b5fb4dc4974795c484832af1';
var storage_box_id = 'fld-c08e3a9eb7784d7f8ee3a5576c0adffa';
var title_id = 'fld-0d0edd2552ea461e929f806a4e5552b5';

Purchases is the track of things I bought, it has it’s own barcode (in “Bag Barcode”) and it also has a text field for the storage box. There are also state and title fields for it as well.

async function start(){
	try {
		while (1)
		{
			let barcode = '';
			let scanBarcode = await promptText("Scan Bag", "Barcode:");
			
			if (!scanBarcode)
			{
				break;
			}
			
			console.log(scanBarcode);

If this looks familiar, it should because it’s the previous code without the zipcode and carrier removed. Also “Shipment” is now “Bag”. Cool beans!

let correctBarcode = await promptConfirm(`Is ${scanBarcode} correct?`);
			
			if (!correctBarcode)
			{
				console.log('Incorrect barcode, skipping');
				continue;
			}			

This part is new, it asks if the scanned bar code is correct. This lets you hit cancel if it’s wrong. If you’re using this with a hand held scanner (which I am), then generally it’ll insert an “enter” at the end of the input. This means you can scan a barcode, validate it on screen and scan the barcode again to confirm this dialog. It gives you an escape key to abort from.

			let targetRecord = getRecordFromFormWithKey('Purchases', bag_barcode_id, scanBarcode, false, false);
				
			if (targetRecord)
			{

This looks for a record in my Purchases form looking in the bag_barcode_id field for the value of scanBarcode that we just scanned. The last two prevent some optimisations and also prevents autocreating records.

				let title = targetRecord.getFieldValue(title_id);
				
				let correctTitle = await promptConfirm(`Is ${title} expected?`);
				
				if (!correctTitle)
				{
					console.log('Incorrect title, skipping');
					continue;
				}

We do an extra confirmation to check that the title matches what we expect. Again if you are using a barcode scanner you can scan again to progress.

				// Set the delivered flag just in case it isn't set already.
				targetRecord.setFieldValue(state_id, 'Delivered');

My purchases has a state field and we just update this to delivered when we’re scanning something. My state tracking was a later addition so this helps to clean up anything I touch.

				let boxId = targetRecord.getFieldValue(storage_box_id);
				
				if (!boxId)
				{
				targetRecord.setFieldValue(storage_box_id, box_id);
				}
				else
				{
					Utils.alertWithMessage('Box ID Already Set', `This barcode already has already been scanned with ${boxId}`);
				}

We then check to see if we have a box ID already and if it isn’t set we set it. If it is set then we flag this with the user. In your case you could change this up to confirm with another prompt (like shown earlier) and confirm reassigning the boxes.

				document.saveAllChanges();
				console.log("Updated existing record for " + scanBarcode);
			}

Save all the changes and I make a log message. I like log messages.

			else
			{
				Utils.alertWithMessage('Unable to find record', `No matching record for ${scanBarcode}`);
			}
		}

The else is to prompt a message saying we can’t find the barcode.

	} catch (error) {
		console.log("Error: " + error);
	}
}

Just like the last one, we catch the error and log it as a fall back.

if (box_id)
{
	start();
}
else
{
	Utils.alertWithMessage('Invalid box', `Invalid box ID found: ${box_id}`);
}

This is a little different, it checks to see if you have a record in the Box form before starting up. The intent is that you are in a box and scanning items into said box.

Here’s the script in full:

var PARENT_SCRIPT = 'Boxes::Bulk Add To Box';
document.getFormNamed('Script Manager').runScriptNamed('Prompter Functions');
document.getFormNamed('Script Manager').runScriptNamed('getRecordFromFormWithKey');

// Boxes
var box_id_id = 'fld-f25492cf56384369b278f7ea41ee258f';

var box_id = record.getFieldValue(box_id_id);

// Purchases
var state_id = 'fld-9402f8c0d53c43b986fee4ebc3468929';
var bag_barcode_id = 'fld-32d459f0b5fb4dc4974795c484832af1';
var storage_box_id = 'fld-c08e3a9eb7784d7f8ee3a5576c0adffa';
var title_id = 'fld-0d0edd2552ea461e929f806a4e5552b5';

async function start(){
	try {
		while (1)
		{
			let zipcode = undefined;
			let carrier = '';
			let barcode = '';
			let scanBarcode = await promptText("Scan Bag", "Barcode:");
			
			if (!scanBarcode)
			{
				break;
			}
			
			console.log(scanBarcode);
			
			let correctBarcode = await promptConfirm(`Is ${scanBarcode} correct?`);
			
			if (!correctBarcode)
			{
				console.log('Incorrect barcode, skipping');
				continue;
			}			
			
			let targetRecord = getRecordFromFormWithKey('Purchases', bag_barcode_id, scanBarcode, false, false);
				
			if (targetRecord)
			{
				let title = targetRecord.getFieldValue(title_id);
				
				let correctTitle = await promptConfirm(`Is ${title} expected?`);
				
				if (!correctTitle)
				{
					console.log('Incorrect title, skipping');
					continue;
				}
			
				// Set the delivered flag just in case it isn't set already.
				targetRecord.setFieldValue(state_id, 'Delivered');
				
				let boxId = targetRecord.getFieldValue(storage_box_id);
				
				if (!boxId)
				{
				targetRecord.setFieldValue(storage_box_id, box_id);
				}
				else
				{
					Utils.alertWithMessage('Box ID Already Set', `This barcode already has already been scanned with ${boxId}`);
				}
				document.saveAllChanges();
				console.log("Updated existing record for " + scanBarcode);
			}
			else
			{
				Utils.alertWithMessage('Unable to find record', `No matching record for ${scanBarcode}`);
			}
		}
	} catch (error) {
		console.log("Error: " + error);
	}
}

if (box_id)
{
	start();
}
else
{
	Utils.alertWithMessage('Invalid box', `Invalid box ID found: ${box_id}`);
}

[Max Heart]

Hi Sam,

thank you so much for your two long posts with explanations. I will try to understand them and modify the variables so that they fit to my forms.

So far
– I imported your Script Manager form
– I added the Prompter script to the Script Manager form
– I created a script field in my box form and pasted your full script from the second post
(… and I bought a barcode scanner for my Mac)

Now I have to understand and adapt the script. The field which contains my QR code of the box is called “Barcode”. Is this field called “box_id_id” in your script?

[Sam Moffatt]

So for the second script, I have two forms, one is “Boxes” which stores the details about the physical box and then I have another form for “Purchases”. “Purchases” was the name of my original Bento library from what feels like a decade ago now and was mostly flat and denormalised. As I’ve gone along I’ve started to normalise everything but it’s all over the place. I had added ages ago “Bag Barcode” and “Storage Box” to the “Purchases” form but didn’t really do much with them. When I started moving, I decided I wanted to have a first class ‘Boxes’ form.

The ‘Boxes’ form looks like this:

Boxes: (frm-8ba92c77b5e8476a987945f40ff2b41a)
        'Title' text (fld-e289240daccc40409d75aee26493110c)
        'Box ID' text (fld-f25492cf56384369b278f7ea41ee258f)
        'Note' note (fld-69754dee7e9247e0b3087e2526881626)
        'Dimensions' text (fld-3e5e30f1d352495f989cc0dc1b306128)
        'Box Type' text (fld-97b2ab2bdbb1400d9384c7634e7c6d48)
        'Box Photos' photo (fld-72a520cff50a4bbd92f1e8ff6eec91db)
        'Purchases' form (fld-482c4f5c71ba4cd7a06c32d1c1d7d983) join 'Purchases' (frm-efc0199a2b1543f79e722383014533b0)
                         ON Boxes.Box ID == Purchases.Storage Box
        'Containing Box' text (fld-fe868108cdd844d895ea982e820b346c)
        'Boxes' form (fld-74466a4b96dc43b4bcbe153646b76911) join 'Boxes' (frm-8ba92c77b5e8476a987945f40ff2b41a)
                         ON Boxes.Box ID == Boxes.Containing Box
        'Tracking Number' text (fld-54af5c1e20e14d20a7c82a40679a2a8a)

It has some metadata about the box such as a title, it’s Box ID (which maps to the box_id_id variable) which is the unique identifier tied to the barcode, it has some other details that are useful and then it has a link to my existing ‘Purchases’ form. There is a ‘Link to Form’ as “JOIN” field here to tie the box record to the purchases record. I also have an extra field for when this box is inside of another box plus a ‘Link to Form’ self-JOIN field. Since some of these boxes are being shipped, they are also tied to a tracking number (I should probably have made this a link to the shipments form I have in this document but didn’t).

My Purchases form looks a little bit like this, I’m going to cut out some fields because it has 121 individual fields in it that aren’t particularly relevant. Here are the first few.

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 Details' section (fld-21cdfa78aaf44d3b83e5c0e16c8d40d1) ===
        '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)
        'Order ID' text (fld-e3e66a0f2e5c4df7b9496f65355e0bcf)
        'Marketplace' text (fld-c163aba17ae64c4d93b5a53819a139dc)
        'Store Name' text (fld-3d98dc6cdcae4a88909c97c80dde7bfb)
        'Brand' text (fld-1e250019d7b249f282cc572814d3e71d)
        'Source' web_site (fld-da9d866bf3ca4d47aade04a77efd7301)
        'Source Scraped?' check_mark (fld-a0557d4c58334bbc81b356b99f6f0a1d)
        '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)

Reasonably generic listing of fields for an object you’d buy, again it’s a little flat because of how I modelled it in Bento but you can see at the end in the storage section the two fields: ‘Bag Barcode’ (which maps to the bag_barcode_id field) and ‘Storage Box’ (which maps to the storage_box_id field). These are simple text fields that have the unique barcode for the bag storing the item as ‘Bag Barcode’ and then ‘Storage Box’ is the barcode of the box that this item is currently contained in.

[Max Heart]

OMG, Sam, I feel so bad and like a complete idiot … I appreciate your help so much but I am not able to translate it to my database.

If you still have patience to advise I would highly appreciate it. I will try to describe precisely where I am:

– I have two forms: “Inventar-Boxen” which has all the boxes and “Inventar-Inhalte” which has all the individual items. The field “Inhalte” in Inventar-Boxen is linked as one to many to the form Inventar-Inhalte. “Show reverse link” is enabled
– The form “Inventar-Boxen” has the following fields: “Boxname” (Calc Field: Box number, box name, box type), Place, box number, box type, contents (“Inhalte”: the linked field), Barcode (the QR code field – a calc field “Box+box number”)
– The form “Inventar-Inhalte” has “Foto”, “Item” (Calc Field: Manufacturer + item name), Boxen (re-link from the “Inventar-Boxen” form), “Barcode-ID” (this is the bar code identifier), “category”, “label”, “notes”, “invoice”, “purchase date”, …

What I did:
– I created the script manager form and copy pasted your “Prompter functions”
– I copy pasted your second script into the script section of “Inventar-Boxen” and named it “Bulk Add”. I assigned Alt+Cmd+Y as a keyboard short cut.
– I tried to exchange your fields with mine. However, I am already lost with the field IDs

Is there a chance to have a look at your tap forms file with just one set of sample data? Maybe I would be able to understand the scripting concept better then.

Again thank you very much and best regards, Max
–

[Sam Moffatt]

Ok! I did some work to set up a sample document with the scripts in it. There were some modifications to the original ones I posted, this edition doesn’t prompt to confirm the item name, it has support for automatically creating new records and it prompts if you try to reassign an item from one box to another if you want to do it.

I’ve attached three files:

• Boxes-20200228.tfarc: a Tap Forms archive containing all of the forms with some sample data.
• Boxes-20200228.tff: a Tap Forms Form Template with the box forms.
• Script Manager-20200228.tff: a Tap Forms Form Template with the Script Manager including an updated set of form scripts.

Took a bit of time to put this all together and test that it works roughly the way I’d expect. It includes two Link to Form fields, one set up as a 1:M type and another set up with a JOIN type. The “Bulk Add to Form” script will handle updating the 1:M field properly and the text field referred to by the JOIN type.

Right now it’s a little chatty, you might want to tune that down a little or bypass some of the confirmation items.

Let me know if that helps a little more with figuring things out. If it makes sense, I might put it up on the template exchange as well.

Attachments:

You must be logged in to view attached files.

[Max Heart]

Thanks a million, Sam. I will download the files and will give you feedback asap.

EDIT: It works. Amazing. I will now try to transfer your script and adapt it to my forms which are relatively similar.

Just a curious side question, slightly OT: You have UPC as one field. Would it be possible to pull item data from the internet automatically by using the UPC?

[Brendan]

@Max, I can answer your UPC data from Internet question by pointing you to the code snippet on the Scripts manual page under the topic “Fetch Movie Data from Web Service”.

https://www.tapforms.com/help-mac/5.3/en/topic/scripts

There’s sample code there that shows how to fetch data from the Internet given a barcode.

[Max Heart]

@Brendan: Thank you.

[Sam Moffatt]

Good to hear that it is working for you. The UPC is intended to eventually autopopulate, Brendan’s got the movie one but I want to make it a little more generic for my own use cases to handle more than just movies. I haven’t gotten around to it but the hope was to scan in UPC and populate records for those entries without having to do manual typing as much.

[Kurtois]

Just a quick “Thank you!” to Max, Sam, and Brendan for a very informative post.

[Author]

Posts