I imagine you are much more familiar with spread sheets than you are relational databases such as FileMaker Pro. The above screen shot is a typical approach to setting this up in a spreadsheet. Keep in mind that spreadsheets and databases are very different types of software and what is easy to setup in one can be complicated to be set up in the other.
Working from your screenshot, each row should represent a FileMaker record and each column should be a field. Rows 2 - 11 should be records in one table and rows 18 - 22 should be rows in another table. FileMaker enables you to use portals as a selective view of multiple related records, most often displayed in a table like view similar to what you have here, so I suggest you start by reading up on portals in FileMaker help. You'll also need to learn how to use Manage | Database to define fields, records, tables and relationships.
You'd likely have at least these tables and relationships to start with:
Distributers::DistributerID = PriceListFactors::DistributerID
Distributers::DistributerID = Skus::DistributerID
Distributers::DistributerID would be an auto-entered serial number and the other two DistributerID fields would be simple number fields.
With that structure, you can place a portal to PriceListFactors for the data you show in rows 2-11 on your Distributers layout and a second portal to SKUs would show the data you put in rows 18-22.
Thanks for your quick reply Phil, I am much more familiar with excel then filemaker. I am used to using portals in filemaker and get the concept of displaying for each distributor their SKUs based on a Distributor ID.
You mention that each row should be a record, how do I go about defining at the record level how the MSRP, DSRP ect. should be calculated though?
In my example above, each of the fields in rows 18-23 are calculated based on the components of their SKU's. My problem is that I don't know how to enter just one calculation for MSRP that would calculate the price based on all of the variables in the SKU.
Thanks again for your help.
You appear to have a three part SKU with parts separated by hypens. 1 - A - X would thus have 1 for the first part, A for the second and X for the third.
This can be three fields, one for each part and you can use each separately to link to the needed price factors.
Let's call these three fields, SKU1, SKU2 and SKU3.
You can create three more table occurrences of PriceListFactors by selecting PriceListFactors and clicking the button with two green plus signs three times. You haven't created any new tables here, just three more ways to refer to the PriceList table. You can double click each new table occurrence box and change it's name. Then you can set up relationships for all three like this:
SKUs::SKU1 = PricelistFactorsSKU1::FactorLabel
SKUs::SKU2 = PricelistFactorsSKU2::FactorLabel
SKUs::SKU3 = PricelistFactorsSKU3::FactorLabel
Now, in your calculations you can refer to PriceListFactorsSKU1::Factor, PriceListFactorsSKU2::Factor, and PriceListFactorsSKU3::Factor tp combine three different factors in a single calculation.
Note: You can either enter the three parts of your SKU directly into SKU1, SKU2, and SKU3 with a calculation that combines the three to produce the full SKU, or you can enter the full SKU into a text field and SKU1, SKU2 and SKU3 can be calculations that extract the three parts of your SKU.