Inventory and Pricing

[Eileen Healy]

I make jewelry and I’ve created two forms – so far. One form is for various jewelry parts including
beads, gemstones, clasps, etc. I’ve viewed this form as inventory. The other is for projects that I’ve either completed or intend to work on and want to make sure I have enough inventory available.

1. I want to be able to automatically reduce my inventory by the parts used to make the jewelry piece from the projects form; example, I purchased 20 silver beads and need 6 for a bracelet. Amount on Hand field would be 14. I haven’t found a way to create a calculation based on fields from two different forms. Can this be accomplished without scripting? Do I need another form, or other forms, to separate out purchases and its related information versus inventory?

2. I also want to determine the cost of materials used to make the jewelry piece. I was able to link the parts form to the project form, and I can attach the parts used, but it pulls all my inventory and not the amount of parts used. I have a field set up to calculate the per piece cost on the inventory form.

I’m hoping this is something I can accomplish in Tap Forms without scripting because I know nothing about scripting. I’ve looked through the forum and templates and haven’t found what I’m looking for. Any feedback is appreciated.

Eileen

[Brendan]

Hello Eileen,

1. You would have to do some scripting for this. You would need a script that when the quantity you type in for the selected part for your project changes, Tap Forms would have to know the part you selected and then decrement the quantity on hand value by that quantity entered.

This is a special case workflow issue that would be common in a dedicated inventory management application, but since Tap Forms is a general purpose database application, you’d have to build that part yourself. But I’ll see if it’s something I can maybe add to the sample Invoices document that I am about to tell you about below.

2. Instead of using a Link to Form field to keep track of your parts used for a project, it’s better to use a Table field. You can have the Table field linked to your Parts form and choose some fields to copy the values from into the Table field record. In this way, you can have a separate field that you type in the quantity of the part used for that project. This can be used in the calculation to determine the cost. It won’t be pulling in your full quantity on hand value when you do that. This is because when you link forms together with a Link to Form field, you’re actually referencing that exact record from the other form. But with a Table field, it’s copying the values from the linked form and there ends the connection. So you can change the values or add additional fields that don’t even exist in the linked form.

A good example of this implementation is the Invoices document I have in the Template Exchange forum. But for your convenience, I’m attaching it here.

There’s 3 forms in the example. Customers, Products, and Orders. Orders would be like your Projects form. Products would be like your Parts form. I don’t know if you have a Customers form, but it links to the Orders form. So for any customer you can see the list of orders. For an order, you can see the list of products.

Thanks,

Brendan

Attachments:

You must be logged in to view attached files.

[Eileen Healy]

Thanks Brendan, I’ll spend some time with your template and see what I can figure out.

Eileen

[Author]

Posts