12 Replies Latest reply on Jan 20, 2014 6:39 PM by philmodjunk

# Looking For Solutions

### Title

Looking For Solutions

### Post

I am currently building a stock management database for my business using Filemaker Pro. So far I have the following tables:

- Product List

- Order Line Items

- Sales Line Items

- Other Item Transactions

All of the tables are linked together using "Item Name" in the "Product List" table as the relation. Each table has various columns with various data including a column called "Unit Cost". At the moment these columns are just looking up the "Unit Cost" from the "Product List" table using the "Item Name" relation. I have however run into a problem. Many of my stock items are often ordered in at different prices. For example, I may take advantage of a 20% reduction offer from a supplier, or a supplier may introduce price increases. I could have the following situation:

Product A - 20 ordered in @ £6.00 on 4 Jun 2013.

Product A - 80 ordered in @ £4.00 on 9 Nov 2013.

Normally these orders would be entered into the "Order Line Items" table and a "Units Ordered" column in "Product List" table would calculate the sum of the items ordered using "Item" as the relation. There would however be no way of breaking down the figure for units ordered to see how many were ordered at what price. Ideally, I would like to be able to be able to see this breakdown without having to create two variations of Product A in the product list.

When it comes to putting in a sale, I would like to be able to choose which product price to use, and I would like my "Product List" table to show the units on hand of Product A @ £6.00 and Product A @ £4.00.

So if, for example, I sold a Product A which cost me £4.00, the units of product A @ £4.00 would decrease to 79, and once all 79 are sold I would no longer be able to enter any more sales of Product A @ £4.00 in.

Does anyone have any suggestions on how to deal with this problem?

• ###### 1. Re: Looking For Solutions

There are a number of potential issues with what you describe.

I'd use a single data source table for all Order Line Items, Sales Line Items and Other Item Transactions. A "type" field can be used to identify each line item's transaction type in order to distinguish them and different fields of the same table would be used to record quantities that increase inventory and quantities that decrease inventory.

And I don't see a table of parent records for your sales, orders, etc. Normally this type of system is build around this basic set of relationships:

Invoices---<LineItems>-----Products.

There might be different tables for Orders and Invoices or just the same table with different layouts. There are a number of possible approaches that can be set up.

All of the tables are linked together using "Item Name" in the "Product List" table

This sets the stage for a number of potential problems that can be avoided if you use a Product ID instead of a name. Names may not be unique, are subject change and vulnerable to data entry errors. An auto-entered serial number or Get ( UUID ) value avoids those issues.

Many of my stock items are often ordered in at different prices.

Usually, the LineItems records are set up to auto-enter (copy) a price from Products. Since this is a copy, you can edit the looked up price to enter a different price without changing the standard price listed in Products. And you can include discount fields that are included in your calculation if you need them. These can be flat rate or percentage discounts.

And I would like my "Product List" table to show the units on hand of Product A @ £6.00 and Product A @ £4.00.

A portal to lineItems can be set up to show all unsold ordered items for that product, showing the price for each. ExecuteSQL in a calculation field can also list these values.

I suggest taking a look at this thread that describes how I would set things up in more detail: Managing Inventory using a Transactions Ledger

• ###### 2. Re: Looking For Solutions

I think in order to tackle the pricing issue for the same products and availing certain quantity of products for certain amount.

Can use a Inventory table that would keep the track of a given item/product quantity, available to be used for sales.

In the inventory we can have the multiple related records for a given item as per the different choose prices for that item.

To track the item qty available for a given choose price.

• ###### 3. Re: Looking For Solutions

I see no need. If we are talking about the price you paid to purchase the item, you can track that information from your lineitems table--whether you keep them separate as in your original idea or in a combined table as I have suggested.

• ###### 4. Re: Looking For Solutions

Hi guys thanks for the replies. It was always my intention to create parent tables such as "orders" and list any transactions using portals. I have taken your advice Phil, and I have created a table called "Transaction Line Items" with the field "Transaction Type" to separate them all. I do not, however, understand how I would create a portal to show all unsold ordered items for that product, showing the price for each without creating a separate inventory table as Priya suggests.

For example in my "Transaction Line Items" table I may now have the following data:

Item Name                     Transaction Type                     Units In Cost                     Units In Discount                     Units In Cost + Discount                     Units In                     Units Out Cost + Discount                     Units Out
Product A                     Order                     6.00                     10%                     5.40                     7
Product A                     Order                     6.00                     20%                     4.80                     5
Product A                     Sale                                                                                                             4.80                     5
Product A                     Sale                                                                                                             5.4                     1

Currently when I put in a sale transaction I am having to manually type in an amount for "Unit Out Cost + Discount" to match it up to "Unit In Cost + Discount". Ideally I would like there to be a drop down list showing only the "Units In Cost + Discount" values where there is stock available at that price. For example in the table above, when entering the first sale the drop down list would include 4.80 and 5.40 to choose from but with the second sale there would only be 5.40 to choose from.

You are quite right about the problems associated with using "Item Name" to link all of the tables. The only reason I have done this is because it would be difficult for members of staff to remember the product ID associated with each product. It would be much easier for them to choose the item name from a drop down list which is what I have done. It is a small business anyway and we are unlikely to have two items the same name.

• ###### 5. Re: Looking For Solutions

The only reason I have done this is because it would be difficult for members of staff to remember the product ID associated with each product. It would be much easier for them to choose the item name from a drop down list which is what I have done. It is a small business anyway and we are unlikely to have two items the same name.

Just because you link records by ID does not mean that users have to remember ID numbers. It's quite possible to set up a value list that displays the name but enters the associated ID number and then you have a drop down list or pop up menu that allows you to select by name even though you are linking records by ID number.

In terms of a portal of unsold items, you could add another number field to transactions used only with the Order transactions, QtySold and a calculation field that subtracts to compute the amount remaining on hand at that order price. Each time you Add a Sales record, a script can find the oldest Order for the same item for with product still on hand and increment the QtySold field accordingly. This script would need to loop to update more than one such record if the QtySold on the Sales Transaction is greater than the QtyOnHand of the oldest Order record that it updates. Self Join relationships can make accessing all order records of the same itemID but with QtyOnHand > 0 pretty straightforward.

With the data managed like that, a portal from products to Transactions can be set up that matches only to Order Transaction records of the same ItemID with QtyOnHand > 0.

• ###### 6. Re: Looking For Solutions

I also have a table called "Cost Line Items":

 Cost Line Timestamp Item Name Unit Cost Tax Rate 13/06/2013 12:22:37 Product A 5.60 20% 19/01/2014 16:33:46 Product A 5.40 20%

My "Products List" table is looking up the "Unit Cost" of the Item but it will only lookup the first entry. How can I set it so that it will lookup the most recent entry instead of the first?

• ###### 7. Re: Looking For Solutions

Hi Phil thanks for the reply. I tried to link the records by an Item ID but when I enter in the item name from a drop down list it fails to lookup any data from the Products List table, including the Item ID. As you have suggested, I have added two fields into the Transaction Line Items table, "Total Units Out" and "Units on Hand" but I have no idea how to do the script so that it only subtracts the units sold where the price is the same.

• ###### 8. Re: Looking For Solutions

Your value list has to be defined with the "use values from a field" option. That takes you to a dialog box where you can select two fields from two table occurrences to use with your value list and an option that allows you to hide the data from the first field. In this dialog, you select the product ID field from Products for field 1 and a Name or description field from Products for field 2.

Please note also that what I have just described is the "simple beginner level" method for selecting a product by name but linking them by ID. Other, more sophisticated methods using more relationships and scripting are also possible.

I have no idea how to do the script so that it only subtracts the units sold where the price is the same.

That sentence raises some questions about what you mean by that. Doesn't each item have two prices? The selling price and the purchase price?

Don't you have an inventory of multiple items that are identical except for the price for which you purchased them? All with the same selling price? How would you identify that the specific item sold was purchased at a specific price?

I have assumed that you are trying to do FIFO (first in first out) accounting here and thus would not be finding transaction records with the same price but rather finding the oldest transaction records for which the product has not yet been fully sold rather than trying to match by price.

But now I am thinking that I misunderstood you?

• ###### 9. Re: Looking For Solutions

No I think we have misunderstood. I have several tables which look like so with example data. This is the "Products List" table which only has one entry for each product:

Item ID                     Item Name                     Unit Cost                     Tax Rate                     Unit Cost + Tax Rate                     Units Ordered                     Units Sold                     Units on Hand
1                     Railway Jigsaw                     4.00                     20%                     4.80                     32                     2                     30
2                     Motorcycles Jigsaw                     5.00                     20%                     6.00                     8                     0                     8

The "Unit Cost" and "Tax Rate" fields are being looked up from a table called "Cost & Tax Rate Line Items". I have done this because our suppliers often change their prices from year to year:

Date Entered                     Item ID                     Item Name                     Unit Cost                     Unit Cost + Tax Rate

12/05/2013 16:34:06

1                     Railway Jigsaw                     4.00                     20%

19/01/2014 16:40:01

2                     Motorcycles Jigsaw                     5.00                     20%

20/01/2014 14:58:03

1                     Railway Jigsaw                     4.50                     20%

What I was hoping to do was for the "Products List" table to look up the most recent price entered for that item. Unfortunately it only looks up the first entry in the table which will be the oldest. This is a problem I need to solve.

The "Transaction Line Items" table looks like this:

Transaction Line ID                     Item ID                     Item Name                     Transaction Type                     Units In Cost                     Units In Discount                     Units In Cost + Discount                     Units In                     Units Out Cost + Discount                     Units Out                     Total Units Out Cost                     SaleValue
1                     1                     Railway Jigsaw                     Order                     4.00                     5%                     3.80                     12
2                     2                     Motorcycles Jigsaw                     Order                     5.00                     5%                     4.75                     4
3                     1                     Railway Jigsaw                     Order                     4.00                     10%                     3.60                     20
4                     1                     Railway Jigsaw                     Sale                                                                                                             3.60                     2                     7.20                     15.00

As you can see from the table, the item "Railway Jigsaw" has been ordered in at two prices, first at 3.80 and second at 3.60. What I was hoping to do was for the field "Units Out Cost + Discount" to have a drop down list showing the "Units In Cost + Discount" values of "Item Name" if there are units remaining at that price. Say for example, the following additional sales happen:

Transaction Line ID                     Item ID                     Item Name                     Transaction Type                     Units In Cost                     Units In Discount                     Units In Cost + Discount                     Units In                     Units Out Cost + Discount                     Units Out                     Total Units Out Cost                     Sale Value
1                     1                     Railway Jigsaw                     Order                     4.00                     5%                     3.80                     12
2                     2                     Motorcycles Jigsaw                     Order                     5.00                     5%                     4.75                     4
3                     1                     Railway Jigsaw                     Order                     4.00                     10%                     3.60                     20
4                     1                     Railway Jigsaw                     Sale                                                                                                             3.60                     2                     7.20                     15.00
5                     1                     Railway Jigsaw                     Sale                                                                                                             3.60                     18                     64.80                     135.00
6                     1                     Railway Jigsaw                     Sale                                                                                                             3.80                     3                     11.40                     22.50

This time on Transaction Line ID #6, the only option in the drop down list for "Units Out Cost + Discount" would be 3.80, as all 20 of the Railway Jigsaw @ 3.60 have been sold.

What I was also hoping to do was for my products List table to have a portal to expand the values looked up in the fields "Units Ordered" and "Units Sold" and show the individual figures for the individual prices like so:

Item ID                     Item Name                     Unit Cost                     Tax Rate                     Unit Cost + Tax Rate                     Units Ordered                     Units Sold                     Units on Hand
1                     Railway Jigsaw                     4.00                     20%                     4.80                     32                     23                     9
3.60                     20%                     4.32                     20                     20                     0
3.80                     20%                     4.56                     12                     3                     9
2                     Motorcycles Jigsaw                     5.00                     20%                     6.00                     4                     0                     4
4.75                     20%                     5.70                     4                     0                     4

Hopefully this makes things a bit clearer, it is very confusing sorry.

• ###### 10. Re: Looking For Solutions

What I was hoping to do was for the "Products List" table to look up the most recent price entered for that item. Unfortunately it only looks up the first entry in the table which will be the oldest. This is a problem I need to solve.

That would appear to be a different problem than what we have discussed thus far, but if there is a date or year field in that Cost & Rate table, you can include it in your relationship to match to the most recent record for that product ID. This is most simply done by specifying a sort order in the relationship.

What you describe matches my understanding that you are looking at your purchase cost, not the customer's selling price. But what has me scratching my head is: "why on earth do you care?". You have a total of 64 Railway Jigsaws ordered. When one is sold, why does it matter that the specific item being shipped out to your customer was purchased at a unit cost of 4.80, 4.32 or 4.56? It's exactly the same puzzle in all three cases isn't it?

That's why I have described a system where you do not try to select the specific unit cost for which the item was purchased, but simply have a script find the Oldest Railway Jigsaw transaction that still shows product on hand and update it to show more units sold--while I'm by no means a bookkeeper, that's what I recall from my college days as FIFO accounting and what is most typically done in this situation.

What you describe could be accomplished with a special kind of conditional value list called a diminishing value list--a relationship would drop out values as that option's quantity on hand reaches zero, but it seems a needless complication here.

• ###### 11. Re: Looking For Solutions

I know it is pointless, I would only use it for making more informed decisions when ordering. For example, with one supplier I deal with they offer a discount on certain items each month and it would be useful to separate the number of an item sold at the normal price from those sold at the discounted price so when the offer comes round on that item again I have a better idea of how many I should order.

• ###### 12. Re: Looking For Solutions

But a far as I can tell, what I am describing allows you to do that. Linking specific items sold to specific purchase prices is a purely artificial action that tells you nothing different from the FIFO method that I am describing, the key difference being that you have less to do to log the items out when they sell as the script can match up the items for you without having to pull down a menu.