3 Replies Latest reply on Apr 22, 2011 2:14 PM by philmodjunk

    Database architecture question - trying to avoid creating too many calculated fields



      Database architecture question - trying to avoid creating too many calculated fields



      I am somewhat new at filemaker database architecture, I was hoping someone may be able to help me define a better way to create my database - as I know there has to be an easier way then what I am looking at doing. 

      I work with software and have the need to create a filemaker database that would allow our distributors to come in and list their DSRP (Software sales price) by SKU number. I have roughly 100 SKUs and 30 Distributors and then for each SKU I would have a MSRP (Manufacture sale price), Calculated DSRP, Distributor determined DSRP and then a buy price from the manufacture to the Distributor. Most of these SKU fields would all be calculated off of the MSRP number but 1 of the fields could be hard entered by the distributor. 

      The idea is that the distributor could come to the page and access only their record and see the MSRP and then enter the Price Factors to calculate their DSRP and then if they want to play with the pricing they could change their DSRP in the hard entered field. 

      The only way I can think of creating this would be to list all 100 of the SKU fields and multiple calculated fields (once for the MSRP, once for DSRP, once for Buy price, etc.) with the individual calculation for each price type and then create a timestamped record for each distributor each time one of the price factors changes. 

      Is there an easier way to do this? Any suggestions would be greatly appreciated.

      Attached is a screen shot of a simplified version of what I am trying to accomplish. 


        • 1. Re: Database architecture question - trying to avoid creating too many calculated fields

          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.

          • 2. Re: Database architecture question - trying to avoid creating too many calculated fields

            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.


            • 3. Re: Database architecture question - trying to avoid creating too many calculated fields

              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.