Inventory with Serial tracking

[Sin Cohen]

Hello,
I am looking at options for an inventory system for my personal business. It has grown past the point of convenient bookkeeping with a spreadsheet. The main issue I face is that I have a bunch of different products with technical specifications I like to list out in the inventory list, and then specific instances of each of these products that have unique serial numbers. It seems pretty easy to add any number of descriptive fields to a product listing, but serial number tracking seems less obvious. To be clear, I am not looking for a hand-out or built-it-for-me template. I am happy to do the hard work of learning the software and building the database I need. But before I head down that long path I want to know if Tapforms can even do what I’m trying to do.

I tested out FileMaker, it has an inventory Quickstart template that does what I want right out of the gate. But I found even basic customization of the template they provide to be very unintuitive, so while filmmaker remains a possibility I am looking for other options first.

So the main function I am looking for is to be able to define “Product A” with all its technical specifications, then define one or more “instances” of “Product A” each with a unique serial number. Each instance of “Product A” would be calculated (simple count function) to provide an inventory overview- without having to re-list each instance of “Product A” as if it were a separate product.

Ideally I would then be able to list given instances (by serial number) as sold, at which point there would be some basic sales record info attached to that instance and that instance would be removed from the inventory count for that product.

So again, not looking for a cut and paste solution, but is this a system I can build with tapforms or is it not the right software for the job?

Thanks,
s

[Brendan]

Hi Sin,

You can certainly do this with Tap Forms. You can easily define the fields you want in Tap Forms, or you could actually export the Inventory table from FileMaker as a Merge file, then import that into Tap Forms and it will re-create the fields in a new form for you.

How do you generate the serial numbers? Is there a specific naming convention you would need for that? Or is a random value or sequential number appropriate? You can do both in Tap Forms. With a Calculation field you can use the UUID() function to generate a random string. Or add a Number field with the Auto-Increment option enabled.

As for having a product with individual instances, one way of handling that would be to simply create the product once manually and then each time use the Duplicate record command to create a new instance of that product. Tap Forms should generate a new serial number for you even when duplicating the record. You could then have a field on your form that specifies if that instance of the product has been sold or not.

So yes, I think what you’re wanting to do is very easily done in Tap Forms.

Thanks,

Brendan

[Sam Moffatt]

If you’re willing to go a little deeper into multiple forms, it sounds like I’d set up three forms in Tap Forms:

– Product form: master form for all of your products with the product details.
– Instance form: instances of your product which has some sort of unique identifier (UUID() for example). This is linked to from the “Product” so that when a new instance (item? part?) is entered, you go to the product and then create a new linked instance record.
– Sales form: this has your sales transaction details stored in it which is linked back to instances. I’d do this as a many to many (M:M) link because it’s possible an instance of a product is returned and you sell it to someone else at which point this instance (part?) would have two sales records.

Riffing a little more, you could have a fourth form for returns which lists which instances were returned from which sale.

For extra credit, if you hook up Tap Forms on the Mac and then Tap Forms on the phone you can keep them in sync with the P2P sync (same network required) and then if your instances are tagged with barcoded identifiers, you can use the phone to find the instance and do the sale transaction on the phone as well as the desktop. I personally used pre-printed barcodes from Amazon with Tap Forms to find stuff I’ve cataloged already. In your case you would scan in the new instance with the barcode that was put on the device to make it easy to find later.

[Daniel Leu]

I personally used pre-printed barcodes from Amazon with Tap Forms to find stuff I’ve cataloged already.

I tried to create and print barcodes earlier this year and wasn’t happy. But it never crossed my mind that pre-printed barcodes exist. Cool! Thank you for the pointer, Sam!

[Sam Moffatt]

Happy to help! I was going to do the same thing but then remembered how much I hate printers and figured someone else might have done it for me :D (it is a bit of a rip off but I really hate printers)

I’ve been using those ones for a while now and they’re pretty good at being scanned in by Tap Forms on iOS, useful for cataloging and also searching later.

[Daniel Leu]

I found some cheaper pre-printed labels on Amazon too. But then I discovered that Avery has some software where I can create my own barcode labels that can be scanned with Tap Forms. Yep, I still have to print them but that’s fine with me. That’s a perfect solution! Inventory tracking just got so much easier.

[Sam Moffatt]

In theory you could also use Tap Forms to make a barcode sheet as well, it has layout support for Avery as well. For the field you want to barcode, go to the field settings and then you can enable the barcode option and if you create a new print custom layout you get the option to drag in a barcode to print.

Tap Forms Barcodes documentation:
https://www.tapforms.com/help-mac/5/en/topic/barcodes

I still hate printers :D

[Daniel Leu]

In theory you could also use Tap Forms to make a barcode sheet as well, it has layout support for Avery as well….

I know and I tried several times, but I never succeeded to print something that would work for me :(

[Brendan]

@Daniel, what didn’t succeed about printing barcodes? Is it that they wouldn’t print or they wouldn’t be recognized after scanning them back in?

[Daniel Leu]

Hi Brendan, I didn’t get them to print the way I wanted. There was a problem of scaling if I remember correctly. Maybe I’ll try another time but for now I’m fine with what I got.

[Sin Cohen]

Hi Guys,
Thanks so much for the feedback so far. This is very encouraging and sounds like tapforms may be the way to go. Sorry for the long delay in my reply, things got very busy there for a while. But I am back to tackling this project.

It might be easier if I give some more details about the products and tracking. Most of my inventory will be hard drives. The serial numbers are provided by the manufacturer, and are unique to each drive, so I don’t create those- they are pre-determined. And for purposes of warranty tracking, etc, it is important that I maintain these serial numbers consistently through my system.

Because different vendors use different numbering systems, there won’t be a uniform set of characters for each drive, WD will use a different format than Seagate, etc. So I don’t mind inputting these serial numbers as I receive inventory but that system has to be at least flexible enough to manage that, and I wont need to generate any of my own serials. In fact, because all these drives already have barcodes on their labels for the serial in addition to the human readable serials printed, while I don’t mind inputing this data manually, it would be especially nice if Tapforms could read the barcode and input this data for me.

I would love to have it organized where I can see an inventory based on various criteria- for instance- I have 32 3TB 3.5″ drives in stock, then within that category see that I have 20 of WD model XXXA and 8 of Seagate model XXXB and 4 of Toshiba model XXXC. And each instance of each of those models will have its own unique MFG assigned serial number. When I make a sale (usually, these drives go into RAID systems so are sold in groups) I can assign specific serial numbers to a sale record that has some basic info like date of sale, customer, sale price, etc- and then that instance is removed from available inventory, but remains searchable/archived so I can track it if I ever need to help a customer with a warranty issue for instance.

Hope that makes sense. In the meantime I am going to start playing with tapforms to see what I can build towards this goal.

Thanks,
s

[Sin Cohen]

@Sam Moffatt
This sounds an awful lot like what I am attempting to do!

I like the idea of having different forms so that information is nested. Its more important for me to see an overview of how many drives I have of what size and model when maintaining inventory levels, but when making a sale I need to go “deeper” and select specific serials numbers that are going to be used for each sale. And ideally the process of assigning a serial to a sale should remove it from the inventory count automatically so I don’t have to manually do that each time.

Does tapforms have this sort of “process” ability? where I can start a process like “receiving” for entering new inventory and “sale” for entering sales record info and removing inventory?

Thanks,
s

[Sam Moffatt]

Does tapforms have this sort of “process” ability? where I can start a process like “receiving” for entering new inventory and “sale” for entering sales record info and removing inventory?

Maybe.

For your use case with the hard drives, my guess is this is a basic process that might work:

– you order a bunch of drives from an upstream manufacturer (could be it’s own form)
– you receive those drives and they all have individual serials, you can load these into a drive form
– you make a sale to a customer and create an invoice and link various drives to them

First one you could put in Tap Forms or not, it does capture the start but also superfluous. The second one seems like your entry point for Tap Forms, you create new drive records when you receive and catalog them. Then the next step is an invoice table linked to your drive. Now Tap Forms will let you link all of this together with two or three forms no worries, what it won’t inherently enforce are your business rules.

You could set up an invoice form with a link to the drive form so that you can select candidate drives. This will work but runs the risk that Tap Forms will display everything in that form, including ones you’ve already sold. A potential solution is in fact two drive forms, one with “available” drives and another with “sold” drives where you move records from one form to the other. That’s possible to do but would interact awkwardly with the Tap Forms UI because the same data will be split across two forms.

If you’re fine eschewing the UI a little and happy to spend some quality time with Javascript, then you can use form scripts to automate some of the process. You could set up a saved search for “unsold” drives (e.g. ones not linked to a sale/invoice) and then implement a pre-check to make sure that the drive you just scanned isn’t already sold before linking it to a new invoice. Of course if you link it to an invoice that gets returned or similar then you’d have to unlink the drive from the invoice.

Whilst Tap Forms has a barcode scanner feature built in on iOS, a Bluetooth barcode scanner for your desktop will likely help you speed through this as well. It could also help you on the phone but I’d personally suggest automating via the desktop this functionality. If you can find a programmable Bluetooth barcode scanner that will let you append “Command+N” to the end then you could do really quick data input.

Without some work, Tap Forms can do some of what you want in terms of the data model but that wouldn’t enable the full process management. With some script fields and form scripts, you could automate most of the process to work for what you need with the checks you want. I’m not going to say yes because it’s going to require some scripting work to fully realise but it’s definitely not no: a solid maybe.

[Sin Cohen]

Ok maybe that whole process/program thing was a bridge too far. All I really need is for the information to be organized and trackable in the way I described. I suspect learning the in-and-outs of tapforms will be project enough without having to delve into javascript. Whatever I can do to automate at least the accounting side I am hoping can be done in tapforms without any scripting but the main things would seem to be having things organized well by drive type, then model, then inventory (serials) and then assign serials to sales and remove from inventory (maybe just by transferring that record to the sales sheet/form). A lot of the math could be done with basic spreadsheet type functions but putting that together with serial number tracking and in a nice-to-work-with form(at) is what I am hoping tapforms can do. Not to mention barcode scanning/printing.
Thanks,
s

[Sam Moffatt]

Starting with modelling the data is probably the first problem and then move on from there. Like any database, you’re only going to get so far before you need to teach it some of your custom logic and processes.

That said I write a lot of scripting for Tap Forms to help automate a lot of data entry and speed up common tasks. Scripting added a massive power boost for Tap Forms capability wise to enable all sorts of interesting functionality.

[Author]

Posts