Seeking guidance with basic data structure

[Kirk Williams]

Greetings,

Apologies in advance if I’m over-complicating things here (it’s something I tend to do often lol)…

I am seeking suggestions for my initial forms structure. I’ve searched the forums extensively, but I’m having difficulty translating my situation into database-related terminology. The file I am creating is an asset inventory database consisting of items in various categories (computers, printers, devices, phones, keys, etc). The data is being migrated from GLPI (a PHP/SQL application), so it resides in a multitude of separate SQL/CSV tables. I’m fine with updating the source data in terms of translating IDs to text and restoring relationships, but I’m also looking to re-organize the inventory for use in tapforms.

Essentially, every item has a unique identifier field, referred to as “ASSET ID”. This field is a simple sequential value ranging from “PSC000001” to “PSC002013”. What I haven’t been able to figure out is how (and if) I can continue using this identifier field while using multiple forms within the file.

To elaborate: a “Computers” form would obviously have many different fields than a “Printers” form. What is the best way for me to use different forms for each, but to have BOTH forms use a single, related “Asset ID” field? I had considered taking a reverse logic approach in which all items were listed in a single form, but I’ve found that “hiding” fields that are not relevant to the device category is either not possible or is far beyond my scripting capabilities. I also feel this would negate many of the organizational benefits of using tapforms.

I’m sure the first question many of you will ask is “why is it so important to retain this Asset ID field”… There are a few reasons I can offer, and I am 100% open to alternative suggestions:

1. “Asset ID” has been used in our office for many years as the primary identifier for these items. It is incorporated into our ticketing and purchasing systems, both of which are outside of my control.

2. The Asset ID (with barcode) is also pre-printed on our inventory labels that are applied to all newly purchased items. These labels are often scanned for use with clinical software, so they’re not going away or changing format any time in the near future.

3. Obviously, it is imperative that the Asset ID is a UNIQUE value without any duplications. Less critically (but still important) is the need to ensure sequential numbering. The next new item added to our inventory should be assigned “PSC002014”, regardless of whether its a computer, a phone, or a television.

I already sense that I’m rambling, so I’ll just leave this here and cross my fingers that what I’m asking makes sense. Thanks in advance for ANY suggestions anyone may have for a way to create this file without having to re-invent the wheel!

Best,

Kirk

[Daniel Leu]

I would create a dedicated form to keep track of your asset ids. This form has a number field that is auto incremented with an initial value of 2014 (next ID). This way, all Ids are unique. A second field of type calculation with return type text is used to create your unique asset id. The calculation is CONCAT("PSC";FORMAT(number;"000000")).

In your product forms (eg, computer, printer), you would create a one-to-many relationship to the asset id form. Now whenever you click on the + icon (left most in the link-to-form table), a new product id is created.

To get the asset id for the current product, I use following simple field script:

function assetID() {

	// field id of asset-id form link
	const asset_ids_id = 'fld-xxx';
	
	// field if of asset-id in asset-id form
	const asset_id_id = 'fld-xxx';

	// get asset Ids records
	let assetIds = record.getFieldValue(asset_ids_id)
	
	if (assetIds) {
		// get asset id
		let assetId = assetIds[0].getFieldValue(asset_id_id)
		console.log("Asset ID: " + assetIds)
		return productId;
	}	
}

assetID();

This script fetches the assetId field of the first asset record used in the one-to-many field (there should be only one…).

Hope this helps to get you started!

• This reply was modified 2 years, 6 months ago by Daniel Leu.
• This reply was modified 2 years, 6 months ago by Daniel Leu.

[Daniel Leu]

After some more thinking and tinkering, I got a simpler version….. You have a form Asset ID with a number field number with auto increment and start value set to 2048. Then you have several product forms. Each has two text fields, one called Name and the other Asset ID. Additionally you have a field script shown below.

This script is called when a new record is created. It adds a new record in the Asset IDs form which creates a new unique number. Then this number is stored in the proper format in the Asset ID field. That’s a bit easier to use than my first response.

function Get_Asset_Id() {
	// id of Name field. This is only needed to force execution of this script
	// when a new record is created.
	const name_id = 'fld-xxx';
	let name = record.getFieldValue(name_id);

	// id of Asset ID field
	const asset_id_id = 'fld-xxx'

	// id of number field in Asset IDs form
	const number_id = 'fld-xxx'

	// Check if Asset ID field is already set
	if (record.getFieldValue(asset_id_id)){
		console.log("Asset ID already set. Aborting")
		return
	}
	
	// Create new Asset ID number
	let asset = document.getFormNamed("Asset IDs").addNewRecord();
	
	// get asset number
	let assetNumber = asset.getFieldValue(number_id)
	console.log("got new asset number: " + assetNumber);
	
	// store value
	record.setFieldValue(asset_id_id, "PSC" + String(assetNumber).padStart(6, '0'))
	document.saveAllChanges();	
}

Get_Asset_Id();

In order to use this script, you have to set the three field ids and the “Asset IDs” form name has to match or you adjust the script accordingly.

Hope this helps!

[Sam Moffatt]

If you’re pre-printing the stickers with barcodes on them, then you just need to make sure what ever process prints the barcodes is unique and everything else should flow from there. I use an Amazon packed pre-printed barcodes for my cataloguing and they’re sequential numbers. Once they get indexed into TF via either the barcode functionality or via a hand held barcode scanner (USB or BT), they’re unique and you can print other stickers if you need them.

Then for each form, add an asset ID field and use the “All Forms” search to search for a barcode across all forms. On iOS, I use the global search tab as a default and the desktop it’s one click away. It makes it easy to find records where ever they are in your document.

You could normalise things but I find search works well enough.

[Sam Moffatt]

Oh and on the migration front, I’d 1:1 import everything to start with. Get all of the data in and verify it landed safely. You know that data model works, for certain definitions of work, so start there. Data migration can be complicated enough without changing things along the way. Get it all over in one piece and then progressively make changes. Always keep a backup and if possible, keep multiple copies using something like Time Machine. The last thing you want is to be second guessing if your data migration lost data.

For normalising your data, what I’d suggest is leveraging table fields. Table fields allow you to embed sub data into your forms. If you’re just tracking textual data, a table field in a sort of key/value setup will let you track arbitrary data easily. You can use scripting to prefill keys in the table based on an entity type to get stuff over. This gives you prompts for values that can be completed whilst being relatively flexible.

This does limit the utility of Tap Forms in that these values aren’t as accessible but short of multiple layouts per device type (also only available on the desktop), I don’t think there is an out of the box solution for what you want (visible fields based on the value of another field). You might be able to use sections to make it harder to hide those fields in a really wide form but that’s still a hard data normalisation problem.

[Kirk Williams]

@Daniel Leu
@Sam Moffatt

Wow – I simply can not thank you both enough for taking the time to assist me!

Daniel, your second “simplified” suggestion seems like it may be the perfect solution (and one that I NEVER would have thought of on my own anytime soon)! I’ve just tinkered a bit with some “dummy” test product forms and it works flawlessly. I now just need to play some more in order to work out different scenarios such as incorporating records with existing Asset IDs, error corrections, etc. Note that this ‘playing’ is for my own self-education, not for verification of the script lol. If this strategy functions as it appears it will, it will prove to be the answer I’ve been agonizing over for months!

Sam, our barcoded labels are provided by the hospital I work at. While I am not certain of the exact format, I presume the code is for the six-digit numeric value only (based on the fact that “PSC” is an acronym for our department likely added internally for clarification). I can’t imagine the text prefix would make sense, or even be recognized by the other applications that incorporate scanning. I am also 100% on board with your suggested potential of using field ids for further relative functions! I’ve only used tapforms for basic, personal projects thus far and it’s becoming obvious that I’ve underestimated its capabilities.

Regarding the migration of data: I actually do have all the source data imported already (and I’m admittedly OCD with backing up, so no worries there!) and modified to reflect the references properly. By that I mean I’ve updated fields with textual data in place of the corresponding SQL table/field IDs they originally imported with. My point in mentioning that is I now realize I could have saved myself a lot of time and effort by scripting those replacements. Lesson learned.

FWIW, I am treating this document as a sandboxed environment for the time being, as the GLPI application is still operable. With that in mind, I have the confidence to tinker without the risk of compromising any critical data. Ultimately, our department is abandoning the use of the GLPI database, but I personally use it frequently and think it’s well worth the time to migrate, even if only for my own use (which is likely since our health system is almost entirely windows-based).

I’m certain I may have some additional questions as I dig deeper, but I am beyond grateful to you both for your time and help!!!!

K

[Author]

Posts