Copying records from one table to another table.

[laguna92651]

I have two tables, a Products table and an Inventory table. I want to copy over the some of the records in the products table to the inventory table. There are product records that are already in the inventory table that I don't want to duplicate and there product records that are not ready to go to inventory. I was going to mark the products that were ready to go inventory with a checkbox or fill a date field. What would be some suggestions on a good way to go about the copy process? Still getting my arms around FM, it seems like it should be a fairly straight forward process.

Thanks

[CEATH Company]

FileMaker only imports data from the source file based on the current found set.  So make sure only those records you want to import are currently "found" before doing the import.

 

Also note the import based on match function.  By matching on product number and selecting the correct settings for the import, you won't create duplicate records in the inventory table.

[LaRetta]

"I was going to mark the products that were ready to go inventory with a checkbox or fill a date field. What would be some suggestions on a good way to go about the copy process?"

IIUC, you do not want to write Products to Inventory but rather SALES (LineItems). Usually Inventory holds a grouped summary of sales for a period, such as week or month. Those LineItems are then set with a flag (date or check mark) to signify that they have been written. Inventory holds a record for a Product ONLY when it's stock has changed such as adding a purchase or posting sales usually monthly.

Can you explain what you mean by 'ready to go to Inventory' (sales side or purchases side)?

[laguna92651]

It seems there two ways to track inventory:

1) have a field in products, Inventory Level, that you add to if creating new inventory and subtracting from if selling product.

2) or have a separate inventory table that captures the various additions and subtractions to inventory. The net figure could then be passed to the product Inventory Level field.

 

What I meant by 'ready to go to Inventory' is in the number 2 scenario, I want to 'initialize' a product for inventory, create a new InventoryID and add the product ID to the record and possibly the initial inventory amount. So this would be from the purchases side. I would want to automate this, select a product or group of products, enter a starting inventory amount (assuming it was the same for all selected products, and create the InventoryID. Is this the correct approach?

[LaRetta]

Yes, there are two ways.  The first way is the easiest but you have no track of history and it is easy to get off on the numbers. 

 

Would you always want to add 5 size Small and 5 size Large?  Usually inventory is tracked at Product level (SKU) and not the Article because some sizes will sell faster than others but there are always exceptions.

 

So do you want to view a Product and enter the quantity received in a portal, scrolling through how many Products of an Article?  Or do you want to script all sizes with the same quantity?  There are many ways to go, the best would be determined on the user perspective when needed to input the purchases.  Scripting this type of process can open you up to errors.  Unlike with LineItems records where you can set them as written by inserting a date, you cannot SET anything from this standpoint to stop the User from clicking the same button a few hours later and running it again 'semi-blind'.  This is why portal and human eyeballs on entering quantities is usually suggested with purchases and adjustments.

 

This is particularly true when the bang-for-the-buck is not there (probably limited usage).  How many Products might you want to receive inventory on simultaneously?  A handful?  There may be other validations to protect Inventory and you will have to potentially deal with those in your script as well.  Having said all that, attached is an approach.

 

This is to create either single or found set of Products in Inventory with Quantity and Type specified (fp7 and fmp12 format).  Just entering into the portal seems easier.  There are other methods, such as using a list view of Products with empty single-row portal to Inventory with filter to 0 and Allow Creation etc etc but simple script gathering ids and going and performing the task seems simplest IF you decide not to just use the portal to add new Inventory records. :^)

 

And of course script will be required to write Sales to Inventory.  I have listed several examples on the Product which opens on how Inventory might be worked.

Inventory.zip

[Wim Decorte]

2) or have a separate inventory table that captures the various additions and subtractions to inventory. The net figure could then be passed to the product Inventory Level field.

 

Additions = purchases

Subtractions = sales

So if you're already keeping track of you those you do not need new tables.

 

Accounting-wise there are various ways of calculating the monetary value of your inventory (FIFO, LIFO, ...) so make sure you know what needs to be done there before you decide on how to build it.