Splitting purchases to multiple related records

[JonT]

I am new (posting) to this group. You've all helped me for many months and I appreciate you for it. This particular time, I can't see any clear cut info on my question, so I wanted to jump in and ask. Thanks in advance.

Simple Setup:

Purchase Order.fp5 - Used to enter items we purchase.

Inventory.fp5 - Records of those items we purchase (currently placed there through a portal).

Question:

Many times there is more than one quantity of each item we purchase. Two of this, three of that, etc. How can I take each line of the purchase order and split it into multiple records in the inventory?

Example:

purchase order.fp5

2 Widgets 24.99 hardware

inventory.fp5

Widgets 24.99 hardware

Widgets 24.99 hardware

If you need any more information, I would be happy to provide it.

Thanks again,

JonT

[jasonwood]

Hi Jon!

That's a bit of an odd way to keep track of inventory. It sounds as if you create a new record in inventory for every single item ordered (even that item has been ordered in the past). Your inventory database is more like what we call a "line items" database.

Normally you would have only one record per product in your "inventory" database. This is convenient because you can keep track of a lot of information about the product (which doesn't need to be entered every time you order a unit), as well as reporting, sales, and many other advantages.

The purchase orders database normally relates to a "line items" database, which looks up information from "inventory" when you enter the item numbers.

Upon receiving an order, you normally run a script which increments the quantity in inventory for that product by the quantity received.

Do you think you'd like to change to something more like this?

[JonT]

I am open to accomplishing my task in another way. In fact, I was hoping to brainstorm this and see if other ideas surface.

Perhaps, a better way to describe my "inventory" database would be an "asset" database. The purchase orders are for buying computer assets. The information saved for each asset would be date purchased, serial numbers, description, category (hardware, software, etc.), and cost. Does this change your thoughts?

Thanks,

JonT

[Ugo DI LUCA]

May be this would not change your problem, but here are my thoughts.

I am sure that at the moment of purchase, you do not know the serial number of each IMac you buy. So you still buy 10 IMac in a row, not 1+1+1+1+1.

Now when receiving these 10 computers, you still have 10 Imacs in Inventory, but each of them have a specific serial N

[jasonwood]

The system I am developing has the following databases (among others)...

Products

Serial Numbers

Invoices

Line Items

Purchase Orders

PO Line Items

There is 1 record per product in the Products database, which also happens to have a field for inventory.

To order a product, I enter the product number on line item in a PO. When the item is received, a serial number is entered. A script transmits this information to a new record in the Serial Numbers database.

When an item is sold, the product number and serial number are entered on a line item in the Invoice. When the invoice is processed, the system looks for a record with that product number/serial number on the serial numbers database, and marks it as sold (unless it is being returned, in which case it is marked as returned).

I only allow one unit per serial number on invoices, but I can still order multiple items on the same line because serial numbers are entered one at a time using a button next to the serial number entry field on the line item.

Maybe that gives you some ideas...

[Ugo DI LUCA]

There is 1 record per product in the Products database, which also happens to have a field for inventory.

Hi Jason,

I can see my thoughts weren't far from what you are building.

In order to help JonT's brainstorming (and also mine), how is that field for inventory setted ?

Do you keep a separate inventory or does it stay within the Product file (related to the Line Items) ?

serial numbers are entered one at a time using a button next to the serial number entry field on the line item.

Do you mean the user enter the serials directly from the line items ? Wouldn't it be possible and more secure to have it entered within the product file (using a relationship to a Purchase Order/Received File) ?

[JonT]

Thanks for your time to respond to my question. You have given me a couple of different ideas to think about.

The information that I need to track in the asset database (formerly described as inventory)is usually specific. Imagine a database of company vehicles. You may purchase two identical vehicles at the same time, same make, model, body type, etc. More often though, each vehicle is different. Perhaps they are the same make and model but one has two doors, the other has four. One is red, one may be blue, and of course they have different VIN numbers. Because of the differences, however small, in the past I have felt they would be best kept as different records. Hardly any two items are so similar that they could be stored as one "thing".

Perhaps a script would be the way to go here, and just do away with my portal all together?

[Ugo DI LUCA]

each vehicle is different. Perhaps they are the same make and model but one has two doors, the other has four. One is red, one may be blue, and of course they have different VIN numbers

I know this is just an example, but the only UNIQUE record here is still the VIN number. You may not sell the same make, model, color,...but someone may, even if it is harly the case. But they will never sell the same VIN number.

In a Product database, a model sold with 2 doors and a model sold with 4 doors would be considered as a different product. Even a color would make these products different. You would have a different ID for

- Mercedes 500 SL Blue Air Conditioning with Sport Tires and

- Mercedes 500 SL Blue Air Conditioning with Sport Tires and Alarm

Perhaps a script would be the way to go here, and just do away with my portal all together?

I do not know what you mean with your "portals being all together" but I am sure that you must store these serials somewhere and call them by a script at the moment of the invoice.

[jasonwood]

Do you keep a separate inventory or does it stay within the Product file (related to the Line Items) ?

Actually my inventory field is just a number field. When you receive something (there is a receive button), the number increments. When you sell something (there is a process invoice button), the number changes to reflect the quantity sold.

You have to be careful with this method (you have to think of everything!), but in the end I like it much better than using a calculation related to line items because those calculations can take longer to compute as the years go by.

Do you mean the user enter the serials directly from the line items ? Wouldn't it be possible and more secure to have it entered within the product file (using a relationship to a Purchase Order/Received File) ?

Not sure where you're coming from here. Normally when you're selling something you're on an invoice. You don't want to be going back and forth through databases (let the scripts do that!). Plus you want the serial numbers to appear beneath the items on the invoice.

[jasonwood]

The information that I need to track in the asset database (formerly described as inventory)is usually specific.

Yes... it looks like in your case the one-record per item is appropriate. But then again, ironically enough, your original question was entirely focused on wanting the ability to order more than one at once!

The easiest way to deal with this may be to have a button to duplicate lines on your PO. Put the button on the line item, and set it up to...

Go to related records (show only related records)

Perform Script (external, Items::duplicate item)

Refresh Window (bring to front)

Then in your Items database (or whatever you call it), make that "duplicate item" script...

Duplicate record/request

[Ugo DI LUCA]

Not sure where you're coming from here.

I meant into the line items. Sorry for that. But I now understand you script the line item as usual...

[JonT]

Thanks again for your time and suggestions. I believe that I have the information I need to complete this task. I think I just needed to throw it around in my head for a bit. Bouncing ideas back and forth always seems to help.

Thanks,

Jon

[JonT]

Ugo,

Thank you for helping me think this through. "Doing away with (something) 'all together'" is an expression, which can be hard to communicate across languages. What I meant was, maybe I should not use portals at all and use a script instead. Sorry for the unintended misunderstanding.

Salute,

Jon