9 Replies Latest reply on Oct 10, 2013 4:01 PM by philmodjunk

    Lists with caluclations



      Lists with caluclations


           I'm trying to create a calculation that allows for a count on how many items there are in a certain kit. There are sometimes the same item needed in several different kits. I created a calculation field called "Qty in Kits", and created a calculation that looks like this:



                If ( List ( In Kits )  =  Materials::Assembly Number & List ( In Kits ) ≠ "Not found in any kits"; List ( Materials::QtyPrt ) ; "False" )

           What happens, is for the items that are only in kits, the calculation comes back the way I want it to.., but if it is in more than one kit, the calculation comes back with a question mark, not even false. Even with items that in no kits, the calculation does not even come up with "false", just a blank space. When deleting,



                & List ( In Kits ) ≠ "Not found in any kits"

           The calculations at least start coming back as false if it can't figure it out like with items that are "Not in any kits", but it still shows false on items that are in more than one kit.


           Thank you for any help.

        • 1. Re: Lists with caluclations

               The & is used to join items you need to use the word and in place of &.

          • 2. Re: Lists with caluclations

                 Oh yeah! Thank you. That solved the "false" problem, so my solution can at least come up with false, but it still shows items that are in multiple kits to be false, when it should be coming up with a number, or even a list of numbers.

            • 3. Re: Lists with caluclations

                   List ( in Kits ) makes no sense. It simply returns the value stored in the field name In Kits for the current record and only the current record. You can simply refer to In Kits in your calculation and get the same result.

                   Example: If the current record has a value of "5" in the In Kits field, List ( In Kits ) will return "5".

                   What kind of data are you putting in this field? a single value? A list of values?

                   Materials::Assembly Number is a reference to a field in a related table. What relationship exists between the table where you have defined this calculation and Materials?

                   You may find this recently created demo file of interest. It deals with a Bill of Materials (BOM) table where the "parts" that are listed in the BOM for a given item can themselves have a BOM. The reports that can be generated from this file can, nonetheless, produce a comprehensive list of all parts that make up a given item.


              • 4. Re: Lists with caluclations

                     So I realized that the relationships were a bit odd, so I reworked the Formula a bit with some new tables, and m formula now is:



                          If ( In Kits =  Materials 2::Assembly Number and In Kits ≠ "Not found in any kits"; Materials 2::QtyPrt  ; "False" )

                     And in the relationships,



                          Inventory::In Kits  =  Materials 2::Assembly Number

                         But it still says "False" for "QtyPrt" when more than one project is listed for that item.


                         Also, for reference, The "In Kits" field is a field that lists which kits the items are in. It's the name of the project it is is. The "Assembly Number" is the same thing. "QtyPrt" is the amount I wish to calculate.. to see how many items are in a list of projects it is in. For instance, if the item "nail" was in projects "Projector" and "Case", I would want "QtyPrt" to see how many "nails" there are in total from the "Projector" and "Case". It would have been nice to have been able to create it so that the kits would be in a list below the total number of that item is needed, and then listed below were the different projects with a number indicating how many of the record's item is needed in each of the projects listed, but I'm afraid that would probably end up being too complicated.

                     Hopefully I explained things alright.

                • 5. Re: Lists with caluclations

                       I see a number of problems and missing details that keep me from posting a detailed description of what to change in your current design.

                       You may have changed your relationships, but since you have not told me what tables and relationships you have in place, I don't know if they are set up correctly or need to be changed. Any guesses that I make at this point would very likely be wrong. You've so far mentioned only two tables by name and haven't identified in which table this calculation is defined. Best guess is that you need at least three tables linked in relationships that facilitate a many to many relationship, but I am doing a lot of guessing to say even that.

                       I recommend uploading a screen shot of Manage | Database | Relationships cropped to just the part that applies to this issue, but please DO NOT just upload the picture, please also describe the purpose behind each table. That way I can see both what you have and also understand better how you need it to work.

                  • 6. Re: Lists with caluclations

                         Inventory- My main Table. 

                         Materials - The first copy of the materials table. It contains most information on items, and is linked to the "Inventory" Table through "Materials"'s "Vendor Part Number. This is needed in order list the different projects a single item is in. It is used in the "In Kits" calculation field, saying 



                              If ( Inventory::Manufacturer Part Number = ${Materials}::Vendor Part Number; List ( ${Materials}::Assembly Number) ; "Not found in any kits")

                         Then, I created a copy of Materials, "Materials 2", to create another relationship. Inventory::In Kits = Materials2::Assembly Number, for the equation that this question is based around.


                    • 7. Re: Lists with caluclations

                           Looks like you are missing a BOM table. Presumably,  multiple materials are used to assemble a specific item in inventory and a given material may be used to assemble more than one such item. This would require a BOM table placed between Inventory and materials to facilitate a many to many relationship between Inventory and Materials.

                           Please review the design of the demo file for which I posted a down load link in my first comment posted to this thread. While not a complete, functioning system for managing a manufacturing process, it illustrates this basic design concept that is used in manufacturing databases around the world.

                      • 8. Re: Lists with caluclations

                             Well the file that was posted was for FileMaker 12. Unfortunately, I am currently only upgraded up to FileMaker 10. Any way around this? Or any other way to open the file?

                        • 9. Re: Lists with caluclations

                               The method still works in FileMaker 10. I just happened to create that file for another forum poster that had FileMaker 12.

                               Start with these relationships:


                               Inventory::__pkInventoryID = BOM::_fkInventoryID
                               Materials::__pkMaterialID = BOM::_fkMaterialID

                               You can place a portal to BOM on the Inventory layout to list and select a Materials record for each given Inventory record. Fields from Materials can be included in the Portal to show additional info about each selected Materials record and the _fkMaterialID field can be set up with a value list for selecting Materials records by their ID field. A quantity field is typically added to the BOM for documenting the quantity of material used to manufacture one unit of the Inventory item.

                               And in many systems, Inventory and Materials would actually be two occurrences of the same data source table so that inventory for all items, whether consumed or produced by the manufacturing process can be listed in a single table. This also facilitates sub assemblies--which is what I would guess you mean by a "kit".

                               You might try downloading a free trial copy of FIleMaker 12 so that you can open this file and see how it works. The demo file illustrates a method for working with such subassemblies using two different type "little red wagons" for the example records. I can't think of anything in that demo that couldn't be implemented in FileMaker 10.