Max Price across field (row) not column

[northstarcomp]

I am using FMP11 Adv. I want to do something similar.  I have Multiple vendors for products but not every vendor has all of the products.  I want to find the average price and then mark the avg price up. I would also like to have a double check that the Sell Price is above the highest vendor price so I never sell for a loss.

 

Example 

Product                  Sku                     Vendor 1           Vendor 2           Vendor 3         Avg Cost       Max Price       Sell Price     

Mouse USB           1001                          9.97               13.42               10.41                11.27           13.42              14.08

Keyboard USB      1002                         10.01                                      15.48                12.75           15.48              15.94

Optical Drive         1003                         45.23               84.91               44.98               58.37           84.91              106.14     Calculated value off average would be -->      72.96       <-- Two vendors are clearing stock of an Item but the third vendor is not. The 2 vendors run out of the item but the more expensive still has stock. so to fill the order I have to get it from the vendor with highest cost. I need a double check here for the selling price so I don't accidentally blow it.

 

One way I have thought to do this is simply to use the highest priced  of all vendors. I know how to do this with the Max command in Excel but I can not seem to get an equivalent to work in Filemaker. 

 

I have done a lot with MySQL on servers and Excel on workstations. But I am new to Filemaker. I have FM running on a dual xeon quad core with 24GB so cpu time or memory is not a big deal. I know some calculations from all the reading I have been doing takes large amounts of both. My database is around 260K items and around 90Meg on the drive. I do want a clean and maintainable solution. My product list changes about 3 times a week. As I update stock and prices. 

[David Jondreau]

Sounds like you should normalize your database some. Vendor1, Vendor2, Vendor3 fields in one table is not a good practice. You should have a separate table for each Vendor, Price, and Item.

 

Your Sell Price formula could be: Max ( Average ( Vendor 1 ; Vendor 2 ; Vendor 3 ) * ( 1 + Markup Percentage ) ; Max ( Vendor 1 ; Vendor 2 ; Vendor 3 ) )

 

Additionally, if you want to make sure you don't see at a loss, but want to be competitive,you probably should be using a different formula. Selling below the highest vendor price doesn't mean it's a loss. You should be tracking how much you bought items for and marking that up. Or making sure the prices you have actually have the stock or the price should be null.

[Wim Decorte]

Based on that grid there should be 3 tables:

 

- products

- vendors

- vendorProducts (join between products and vendors), this one has the purchase cost in it

 

Then from the products you can use all the regular functions (average, max, min,...) on the vendorProducts relationship

[northstarcomp]

Thank you for your quick help.

 

Right now I have the data in a single table. So I will have to break it back out into separate tables. All files I get from  vendors are CSV. I normally import it to excel to trim the columns I want. I don't worry about their categories and sub categories. I have attached a 500ish record ample of what I currently had built.

 

So I am going to do a fresh database. But I a not sure how the three tables will work. One table per vendor for products and one per vendor for prices?

 

My Excel file from a vendor will be from a single vendor. It includes all products and prices sku numbers ect. But each vendor uses a different internal SKU number. So I have created an internal SKU to my company that has match's to each vendor/ manufacture SKU.

 

 

Vendor 1 Table   -->|

                                      |

Vendor 2 Table   -->|    All Vendor   

                                      |    Products

Vendor 3 Table   -->|

 

 

 

Sample one and sample 2 are still in what I was originally trying to refine. I think you will get what I was doing. But it looks by your thoughts I will have to start from scratch on my build.

Sample.zip

Sample 2.zip

[Wim Decorte]

posts were merged...

[northstarcomp]

Thank you for your help David and Wim. I think you both have me starting in the right direction. So what I am doing now is building the database from scratch.

 

 

Vendor 1 Table   -->|

                                      |

Vendor 2 Table   -->|    All Vendor   

                                      |    Products

Vendor 3 Table   -->|

 

 

All Vendor Products    --> Will have the the formulas to get and calculate prices based on data from Vendor tables. This will bring only in stock items and the max price for items in stock. If Stock > 0 then max price for item. All Vendor Products will end up with products with stock greater than 0  with total stock available and what the max price is. I will need to carry over some number that I can use to map the products all the way through. In my flat database I have my Internal SKU  Vendor_one_PN Vendor_two_PN Vendor_three_PN (I really have close to a dozen vendors only three so far in my first build) all together on my flat file. To do the original match up I had to use UPC/EAN numbers on some and manufactures numbers on others. If you can point me in a little bit of a direction I am sure I can narrow it further down. I am getting Vendor tables imported now.

[bruceR]

Why would you have a different table for each vendor?

 

Vendor is just a field in an all-vendors product table.

[northstarcomp]

Well essentially that would be what I have already. I have one table with all the data. Or am I missing what you are saying Bruce? 

 

What I wanted to do is just a simple calculation based on my first post. But it looks like filemaker is not excel in that part.  

 

What I understand from David and Wim. And I may be wrong in this. But Filemake will as long as the data is in separate table compare that data. 

 

Vendor 1 Table

 

Product                 Our  Sku                     Vendor Sku     Manufacturer UPC     Vendor Price        Vendor Stock        Manufacturer PN 

Mouse USB               1001                         AB323                                           13.42                        123                      345765

 

 

 

Vendor 2 Table

Product                 Our  Sku                     Vendor Sku     Manufacturer UPC    Vendor Price        Vendor Stock         Manufacturer PN

Mouse USB             1001                        MOU47G5         00393844484           9.82                       16

 

 

 

Vendor 3 Table

Product                 Our  Sku                     Vendor Sku     Manufacturer UPC    Vendor Price        Vendor Stock         Manufacturer PN

Mouse USB             1001                              45429         00393844484           10.22                       93                           345765

 

 

What I did to first build this was manual sorts and imports and merges. With updates by import by matching on vendor sku this would update vendor sku stock and price. But I would love to automate this. But my initial plan was just to get my pricing as my first post asked. 

 

What I have now is a new database with 3 vendor tables one for each of my vendors. I also have a table we will call "all_vendors_products". I am planing on using this to merge and sort the vendor data automatically. It will end up with total stock available max price I will have to pay from a vendor ect. I am still working on trying to figure out a automated way to do the matching I manually did. As every vendor does not have all data filled out. Some products  have one or the other  UPC, MPN and some have both. I know Filemaker is powerful enough to do it but I have to figure it out. 

 

​How would you recommend a solution Bruce? Is there a way to do the math with the files I have already? 

 

Has this cleared anything up for any of you on what I am trying to do?

[Wim Decorte]

What you describe as "vendor 1 table", "vendor 2 table" and so on needs to be combined into one table, the one I called "vendorProducts"

 

So going on the USB mouse example:

- the products table would have one record: SKU 1001

- the vendor table would have 3 records: one for each of the 3 vendors, with the relevant vendor info (name, address, phone,...)

- the vendorProducts table would have 3 records: the mouse USB product for each vendor.

 

Products and VendorProducts are linked on SkU1001

Vendors and VendorProducts are linked on Vendor ID

[David Jondreau]

You should have a separate table for each Vendor, Price, and Item.

 

I could have phrased this better.

 

Wim and I are saying the same thing. You need one table for all Vendors. One table for all Items aka Inventory aka Products. And one table for all Prices ( linking products to vendors aka vendorProducts aka Prices).

 

DJ

[northstarcomp]

Ok. In the vendor table. Each vendors item will be kept as separate records or all three vendors items be merged into a single record in the table. Can you show me a simple example of what you mean? Somehow I am missing something in the explanation or I am over thinking something. Because it just isn't clicking. 

 

Here is an example of data from one vendor data in the attached spreadsheet. Sometimes it is incomplete. So I had manually took the data in my original database in an attempt to compile all fields I wanted. If you look at the data some vendors sort the stock by location some do not. Maybe this will help you understand what I am starting with. I want to do it right.  But I think I am trying to hurdle before I walk. I know keep it simple stupid.. 

 

 

Dan

 

Sample of Venodor data.zip

[bruceR]

One record = one product from one vendor. Oversimplified: a vendor; a product ; a price.

[bruceR]

That's a pretty mixed up dataset. What are you (and what are we) supposed to do with that?

BasicProducts.fp7.zip

[northstarcomp]

Bruce looks like you keep hours like me. Well its all the same data from three different vendors. One vendor breaks the stock out by warehouse another already has it totaled. That's why I was thinking one table for each vendor to normalize the data. Add separate warehouses into one total stock and just pull the total stock up. Ect. Each vendor keeps their own internal SKU. I figured I could use this for updating prices on the vendor table. This would cascade through to my calculated price. Then from this normalized data I think it would operate much like what Dave Wim and you have all recommended.

 

 

Normalization would be

adding of stock from warehouse to total stock number.

This total stock would be carried up to the next table

field names

field order

 

Now to match products I originally matched them manually by taking the products and matching them against each other by manufacture part number. So vendor_1 would go directly in. Then vendor_2 would match to vendor_1 on MPN. Those items that did not have a MPN would not be imported.. Then what was left I told to Match to UPC number. Anything left add to the bottom of the list. Then vendor_3 would do the same to this list built by vendor_1, vendor_2 import. I did all of this manually with the import engine of Filemaker. I will be doing this update at least 3 times a week (could do it 5 times a week).

 

Here I have included full xls files with the same manufacture and all products carried from them by each vendor. 

 

 

Sample of Vendor data from each vendor.zip

[northstarcomp]

OK. I have been trying and trying. I know how to write an SQL statement for this but cant seem to get filemaker to do a Many-to-one. reverse one to many. Essentially compile more than one table into a single table. I think it is possible to get the data to where I want/need it this way. 

 

I have created a few tables and put in sample data. (everything from one manufacture from 3 vendors). This should better show how I am trying to get the data up to a table that has the information I need.. 

 

I have tried to show all the information as its carried through on the relationship table. But I cant seem to have the data go anywhere.

OK. I have been trying and trying. I know how to write an SQL statement for this but cant seem to get filemaker to do a Many-to-one. reverse one to many. Essentially compile more than one table into a single table. I think it is possible to get the data to where I want/need it this way. 

 

I have created a few tables and put in sample data. (everything from one manufacture from 3 vendors). This should better show how I am trying to get the data up to a table that has the information I need.. 

 

I have tried to show all the information as its carried through on the relationship table. But I cant seem to have the data go anywhere.

Website Master Database 2013.zip

[bruceR]

You keep missing the point.

Website Master DB 2013.fp7.zip

[northstarcomp]

I guess I am missing it. Why put the vendors names in a separate table? What is the benefit? How does this make updating a few times a week any easier? And how can I get my math to the front table? I am missing something. I dont want to sound dumb but its just not clicking..   When it does I know its going to be an I am stupid moment.. Because it was right there and didnt see it.

 

Dan

[Wim Decorte]

Without the proper table structure you will continue to have problems reporting on your data.

 

Vendors are an entity by themselves.  That allows you to track information about them (address, whether they are your preferred vendor,... things like that).

 

While it may seem that it makes your updates harder for now; if you add a vendor in the future for one of your products you will definitely see the light.  Once you have the proper database structure, your updates should become a lot easier.

[bruceR]

Northstarcomp: I don't think your expectations are realistic.

 

You are not missing "it" where "it" is a simple thing.

 

You are missing significant training and experience in data modeling concepts and normalization that are the foundation for database work; as well described by Wim and David.

 

A closer analogy is that you are missing first year algebra. You don't have a simple problem. You have significant work and study to do.

 

You have multiple vendor data sources, which have a lot in common but which also have some vendor specific details. 

 

In some cases these details appear to include inventory levels across multiple locations.

 

You should probably hire somebody to train you or set this up for you.

[David Jondreau]

I guess I am missing it. Why put the vendors names in a separate table? What is the benefit? 

 

Do you ever expect to have more than 3 vendors? If so, you're going to have to do a lot of work on your database each time. By having a separate Vendors table, it becomes simple.

[northstarcomp]

That's why I wanted separate vendor tables David. I actually right now have close to 10 or 12. I am starting with 3 to get the database setup. Make sure there are no breaks in math merging editing.. The purpose of this database is to make all of my data uniform and then use it in my web store and my brick and mortar store. But the web store is back end is MySQL. So I have to get the data to into it as well. 

 

What I have done currently is Excel and Filemaker edits to get the files into the format I need. about 3 hours of work  three times a week.

 

What I want to do I import to a table from the CSV file and all the adjustments are made and export the built table on the other end. I thought I could do this in filemaker and I am sure it can be done. I have never had training on Filemaker but have been working with Databases for years. But I have done administration and configuration of the servers and applications. 

 

David and Wim. 

 

Vendor name address location is not important information to me. Just that they have XYZ part and 123 Price and they have 50 of them. Secondary data I care about Is length width height and weight for shipping. But I only need to know which vendor has the best price and to do the markup based on the worst price.

 

In my original flat table it is actually functional if I could get the math to work across per my original question. 

 

Or in my Database upload where each vendor is in its own table "  Website Master Database 2013.zip" I am working on taking the data I want off the individual vendors tables and bringing them up to one unified table. 

 

I dont think Bruce gets what I am trying to get out of this. I am not building a portal. I want to rearrange and have it do math. 

 

Step 1

 

Import updated data and/or new data

 

Step 2

 

Depending on vendor it adds together warehouses in to single stock number (I don't care what location has stock. Just that they have stock.) When all vendor table calculations are done I have a uniform set of fields.(Currently use excel)

 

Step 3

 

Take uniform fields from vendor tables and bring them up to a table where like items (Based of Manufacture PN and or UPC/EAN) are matched to compare prices for mark-up and selling price. Currently I have gotten to this step by using the update existing add remaining records to get the data matched on the MPN or UPC/EAN data. 

 

Step  4 export file to be imported by MySQL on Web server.

 

I will figure it out. It may take some time Bruce. If you don't want to help that's fine. But the problem is really quite simple. I need to find the vendor with the highest price. Excel can do it. I know filemaker can too. Maybe I am not explaining my end well or maybe I am not getting your thoughts on it right. Thats why I Mea culpa'd for not getting it to click. But I think you want to reinvent the wheel in your data solution. I am keep it simple. I am just using filemaker to format data and get calculated data back out. I am not using it for reporting or anything else.

 

 

Dan

[Wim Decorte]

I will figure it out. It may take some time Bruce. If you don't want to help that's fine.

 

That's just nasty... All of us are pouring expensive time into helping people out here.

 

The long and short of it is that the best approach is to properly normalize your data whether you want to a add attributes (like address info) or not.  Anything other than that is going to be a difficult structure to maintain.  All of the math you have in mind is going to be a lot smoother and future-proof, even it it means doing a little more scripting up front to properly bring in the data.

[bruceR]

If the only thing that is important is price comparison then the only fields you should be including in your tables are fields related to that purpose.

 

If you DO need the other fields such as inventory totals: the data you have supplied clearly indicates more relational complexity where some products show totals across multiple locations. You mention multiple original data sources, which may have other variations in data design that you need to accommodate.

 

What do you think it means that a group of highly skilled and experienced developers who are volunteering expensive time are all telling you exactly the same thing?

 

"The problem is really quite simple." The problem of world peace is also simple. Everybody be peaceful.

 

No, the problem is NOT simple, and to present it as simple is an indicator of what you understand about data normalization. The level of complexity is not clear depending on how important all the none-price data is. The ANSWER may be simple, once the problem (of currently uncertain complexity) is better defined and handled by an appropriate data design.

 

You have already been shown a solution. (Message #13 for lowest price method with simplistic dataset; message #16 for integrated data table). You just refuse to recognize it. You admit that you have no training. Get some training, and in the mean time pay somebody with experience to solve the problem. Show the thread to your boss.