9 Replies Latest reply on Mar 11, 2015 11:36 AM by philmodjunk

    Simple calculation Not working



      Simple calculation Not working


      Hello All,

      Here I am again. A noob that can't create a simple calculation. It was sure I had this worked out but oh well.

      So I want to create a layout that will keep track of daily quantities of items that were installed on a job site.

      I want to use an item number field with a drop down list that will have all of my item numbers. Then I want the description field to auto populate a description that corresponds to the item number. I tried a sample calculation just to make sure it would work with only two item numbers and descriptions but will not work the way I have it set up. please see the attached images. Any help would be appreciated.




        • 1. Re: Simple calculation Not working

          That's not how you would use the operator "and" as it is used to combine two values of either False or True (called Boolean values).

          A Case function could be made to work for what you are attempting here, but every time you change what is in this list, you'll have to redefine your calculation.

          I suggest that you use a very different method to produce the effect that you want where entering an ID looks up the description from a related table of such ID's and values.

          See: Auto Fill

          • 2. Re: Simple calculation Not working

            Sounds good. Makes since to use a table. I will check into that post.

            Thanks again!



            • 3. Re: Simple calculation Not working

              So I created a table called "TDOT Item Numbers" and I have my original table called  "Estimated Quantities".

              The" Item No" field in the" Estimated Quantities" table has a value list populated with the Item Number field from the TDOT Item Numbers Table.

              I then added the description field and unit field from the TDOT Item Numbers table to my Estimated Quantities Table.

              I created a relationship using the item numbers because they are unique identifiers.

              When I pick an item number from the drop down list in the estimated quantities table, FM auto completes the description and unit field with the correct values. All is good.

              But, now I want to create a complete set of item numbers, descriptions and units for my project. And then I will fill out the quantities field for each day at the job site.

              So I create Item NO. 2 and quantity 2 in the Estimated quantities table. I add Item no 2 to the relationship.

              But FM doesn't know to auto populate the description field  from the item no 2 chosen value. and now the first relationship doesn't work either.

              So what should I do. I am sure I have again gone about this the wrong way.






              • 4. Re: Simple calculation Not working

                Here is a second image.

                • 5. Re: Simple calculation Not working

                  You need  third table and a portal:

                  Start with these relationships:

                  Estimated Quantities-----<LineItems>-----TDOT Item Numbers

                  Estimated Quantities::__pkEstimatedQuantID = LineItems::_fkEstimatedQuantID
                  TDOT Item Numbers::Item Number = LineItems::_fkItemNumberID

                  You can place a portal to LineItems on the Estimated Quantities layout to list and select  TDOT Item Numbers records for each given Estimated Quantities record. Fields from TDOT Item Numbers can be included in the Portal to show additional info about each selected TDOT Item Numbers record and the _fkItemNumberID field can be set up with a value list for selecting TDOT Item Numbers records by their ID field.

                  For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                  Caulkins Consulting, Home of Adventures In FileMaking

                  • 6. Re: Simple calculation Not working

                    So is the Line items table a duplicate of the TDOT item numbers Table?

                    • 7. Re: Simple calculation Not working

                      It is  a separate table, but some fields in it can be set up to look up data from TDOT if necessary. TDOT acts like a "product catalog" of items you might select for any number of different estimates. The LineITems table lists the specific items selected for a specific estimate.

                      This is much like an invoicing system with an invoice table with a record for each sale, a line items (or invoice data) table to list each item sold on that invoice and a products table that lists all products available for sale.

                      • 8. Re: Simple calculation Not working

                        Ok Here is where I am at.

                        I created the Line Item Table and a portal into my Estimated Quantities Table.

                        So my TDOT table is my product catalog and my Line items table is used to build an invoice with selected items from my product catalog.

                        I still can't make a multiple line "invoice" with several different items from my "product catalog". Not sure where I am going wrong there. But I am assuming that I create each line of my invoice in the line items table and then portal the whole thing on to the estimated quantities table. Which means the only thing that would be in my estimated quantities table would be a date and a quantity box.

                        For my solution. Each Estimated Quantity record will be the same items but just different days and quantities.

                        Here is my relationship as it stands.

                        thanks so much for all the help.

                        • 9. Re: Simple calculation Not working

                          Better take a closer look at what I posted the last time.

                          Your relationships are not correct. The match field used to link line items to estimated quantities cannot be the same match field to link it to TDOT. The match field in Estimated Quantities should be a field that uniquely identifies each record in Estimated quantities--typically an auto-entered serial number*. The match field in TDOT should also be a field that uniquely identifies each record in TDOT--best choice here is also an auto-entered serial number*.

                          And please note that you would put a portal to LineItems on the estimated quantities layout to use to list each item selected from TDOT.

                          * a text field with Get ( UUID ) as an auto-enter calculation could also be used