6 Replies Latest reply on Jan 3, 2011 10:35 AM by FentonJones

    calculation in a field

    keiththirgood

      Title

      calculation in a field

      Post

      I'm a complete beginner at Filemaker (I don't even know the terms for the various functions and features). What I'm trying to do is create an inventory of artwork. 

      I have a field that has a drop down list of painting sizes. (5 x 7, 8 x 10, etc.)

      I created a "table" with the cost of artwork according to size. (I also created a table with the corresponding prices. I even managed to create relationships between the sizes and the prices.

      What I want is, when I choose a size from the drop down, the cost field in the record for that piece of art automatically fills in with the cost corresponding to the size.

      I can't figure out how to do that.

      I've attached a screen shot to show what I think I've done so far. 

      I need very careful explanations, as I've very usnsure of the steps to do things and the names of fields, actions, etc.

      I'm on a Mac, using Filemaker Pro 9 Advanced.

      Picture_28.png

        • 1. Re: calculation in a field
          FentonJones

          If paintings only exist in the fixed sizes, then you only need 1 table for a "size vs. price reference" table (known as a "lookup table"), with 1 Size and 1 Price field (that's all). There's no need for a separate Prices table. In that case you'd just need a relationship (from your "sales" table) based on Size. The Price in "sales" would be a Lookup, based on that relationship. This is an auto-enter option.

          If however, there are paintings between the sizes (18x19 for example), then you'd need a way to get the price. There is a Lookup (next higher) function. But that would mean that the sizes would need to sort in the same order as the prices; either alphabetically, numerically, or by sq in (multiplying the 2 sides), with no exceptions.

          We can't really tell by your picture just what prices correspond to which sizes. The Prices do not seem to increment strictly according to sq in. For example, 18x24 is 432 sq in, 20x20 is 400 sq in, but 20x20 appears to cost more. 18x24 sorts before 20x20, either alphabetically or numerically. But what about 10x10 vs 9x12 (which is more sq in, and appears to cost more). If none of the above sorts work, then a Rank field would allow you to manually specify the order.

          You would absolutely not have separate fields for each size, or each price. In this "reference" table(s) there should be only 1 Size field, and 1 Price field. Each new combo is a new record. There would be only 1 relationship, from the actual "sales" table to here, based on Size.

          So, that's all I can tell you, until I know: 1. Whether paintings can exist at other non-specified sizes?. If so, 2. What the actual order of sizes vs. prices is.

          • 2. Re: calculation in a field
            keiththirgood

            >So, that's all I can tell you, until I know: 1. Whether paintings can exist at other non-specified sizes?. If so, 2. What the actual order of sizes vs. prices is.

            The size of the paintings are set (At least they're set at the moment. The could get larger, but not likely different sizes from what is in the able.).

            Frame costs are separate from the painting costs and relate to the size of the painting, and also depend upon the kind of frame. 

            There are three kinds of frame: gallery, commercial and custom. I had hoped to be able to use the information from the size field to calculate the frame costs depending upon which frame is chosen (radio button?). Unfortunately, just like the paintings themselves, it's not a simple mater of multiplying the square inches by a single, set dollar amount. As the paintings and frames get larger, they cost less per square inch.

            • 3. Re: calculation in a field
              FentonJones

              If there are ONLY the sizes you enter, never "in-between" sizes, then you only need 1 "lookup" table, with 1 field for Size (type: Text) and 1 field for Price (type: Number), as I said in my first paragraph. It is not complex. 

              In your "sales" table, you'd build a relationship to this table ("PaintingPrices"?) based on the Size field in both tables:

              Sales::Size =::PaintingPrices::Size

              The Sales::Price field would use the Auto-Enter option [x] Looked-up value. This will open a 2nd dialog. At the top you choose the relationship (above), Starting from: Sales (whatever your name is for where you use the price), and choose the Price field (showing at the lower left, in the fields from the PaintingPrices table).

              Then, whenever you enter or modify the Size field in Sales, the Sales::Price field will Look Up the Price from the lookup table.

              --

              Now, the Frames are another matter. So, there are only 3 possible Frames for any given size Painting (gallery, commercial and custom), hence only 3 possible prices for them? I would put this in a new FramePrices table. Because it would have 3 times the records of the PaintingPrices table. And it makes things simpler.

              It would have all the Sizes of the PaintingPrices table, but 3 records for each painting size, one for each of the 3 frame types, which is entered into a "Type" field. You could likely automate their creation via script; or just enter manually. The fields of FramePrices would be: Size; Price; Type. You'd probably want to enter the records in the order of (though you could also later sort by something, if things need to get entered out of order):

              Size1; Price; Type = "gallery"
              Size1; Price; Type = "commercial"
              Size1; Price; Type = "custom"

              In the Sales table you'd have another text field, "Frame Type". You'd have a Value List of those 3 values. This field would be included in a relationship to the FramePrices table. This relationship would target the correct price for that size painting and the correct frame.

              So the Sales::Frame Price would be a Lookup, much like the Painting Price, but using this new relationship, to FramePrices, looking up the Price field there.

              • 4. Re: calculation in a field
                keiththirgood

                >If there are ONLY the sizes you enter, never "in-between" sizes, then you only need 1 "lookup" table, with 1 field for Size (type: Text) and 1 field for Price (type: Number), as I said in my first paragraph. It is not complex.

                I don't understand (I said I was very new at this.) I have made a new table called "size price". It has two fields. "price" and "size". Where and how do I put the information about the sizes and prices?

                • 5. Re: calculation in a field
                  philmodjunk

                  Where and how do I put the information about the sizes and prices?

                  Use the layouts drop down in the upper left corner to select the layout FileMaker created automatically when you made this new table. Select View as Table from the view menu. Type the data for size and price into the two columns on your table. You can use New Record from the Records Menu to add more rows to your table so that you can document all your sizes and prices.

                  • 6. Re: calculation in a field
                    FentonJones

                    I think this is one of those "a file is worth a 1000 words" situations. 

                    Size_Price_Lookup.fp7.zip