6 Replies Latest reply on Mar 25, 2009 1:41 PM by Sewlong

    Newbie question - Lookup multiple records?



      Newbie question - Lookup multiple records?


      I am using a table to create records for the “Generic Items” I build at my shop, I use 2 joining tables to calculate the typical cost of the “Generic Item” i.e.. “Join Materials” & “Join Labor” tables.


      I then have a table “Projects” that looks up the generic item information so we can produce a Specific Item. What I would like to do is have the ability to Lookup all of the related records from the “Join Materials” & “Join Labor”  tables so I can change them for the specific project with out changing the original “Generic Item”

      How do I go about this?

      Thanks in advance!

        • 1. Re: Newbie question - Lookup multiple records?



          I suppose you have a layout to select the "Generic Item" you plan to create and a few portals in it, to "Select & Mark" the "Materials" and "Labor" you plan to use. I also suppose you have a button in that layout to process the "New Project" based on the selection.


          Make the "New Project" button copy the "Generic Project" serial number (I suppose you are assigning one) and go to the "Projects" layout, create a new record (Project) and give it a "Project  serial number" different from the "Generic serial number". Paste the "Generic" serial number and use that field to define the lookups from the "Project" to the "Generic" table, that will give the new project the basic information. Now, call from the "New Project" script linked to "New Project" button, a script that will "Find" the selected and mark items in "Join Materials" and export them to the main FileMaker Folder with the fields you need, when it finish, the control will return to the "New Project" script and from there do an import to the table that holds the "Materials" of the "New Project" and paste and replace on them the "Serial Number" of the "New Project" to relate them to the project.


          Do the same process for the "Labors" and you are done. 


          P. D. Make sure that you a Materials and labor tables for the "Projects" apart from the generics

          • 2. Re: Newbie question - Lookup multiple records?

            Thank you for trying to clarify what I am asking. This has been driving me crazy!

            I actually have 2 separate tables  (& layouts) for the items i.e.. "Items" the generic items and "Projects"the specific item that we are going to make for a customer.


            I use a drop down on the "projects" table/layout to chose the Item (& Item ID) we want to make then from the Item ID it looks up all of the information from the generic item and copies it into the Project record so i can change it to meet the specific needs of the customer.


            Where I am having the trouble is the joining tables.


            for example on the labor (and materials) do I need one or 2 joining tables, i.e.. one table to join item-labor and one to join project-labor or can I use the same labor-join table for both?


            Perhaps with this information you could further clarify how I would go about copying the information.


            Thanks again!


            • 3. Re: Newbie question - Lookup multiple records?

              My experience tell me that you should separate them. Have a "Materials" and a "Labor" tables for the Generics and another set of "Materials" and a "Labor" tables with different names and serial numbers for the projects. That way if you modify the generics, the actual projects will stay the same and you will be able to actually know what is just suggestions and what you actually needed for the project. Joins are used for many to many relationships and from what you tell me you only need a one (Project) to many (Items and Labors). So keep it simple. The ID of the Project is to relate to Items and Labors and the Generics ID is going to be your "Key" to trigger lookups. But I think you already have that working.


              When you do the Export and Import of the tems and Labors, after the import you will end up with a Found Set of what you just imported, that is the time to go to the first record, paste the "Project" id, and replace the id on all the selected records. That, will establish the relationship between the Project and the Selected "Materials" do the same with the "Labors" and you will have it.


              Have the following:


              Generic Items (Generics ID) - Related to Generic materials (Generics ID and Generic Materials ID) and Generic Labors (Generics ID and Generic Labors ID)


              Projects (Projects ID) - Related to Project materials (Project ID and Project Materials ID) and Project Labors (Project ID and Project Labors ID)

              • 4. Re: Newbie question - Lookup multiple records?

                I finally had a chance to think about this some more.

                Unfortunately I am not using a script to add and copy the "Items" into the "Projects" table.

                I simply Choose the "Item" from a field on the "Projects" layout/table and it lookup all of the related information and copies it into the projects table.

                SO is there another way, perhaps a formula that will automate this process when I chose an "Item" from the "projects" table?


                Thank you again for your response!

                • 5. Re: Newbie question - Lookup multiple records?



                  You never told me, what operating system you are using and what version of filemaker?. WHat do you have in the layout of the project.





                  • 6. Re: Newbie question - Lookup multiple records?

                    Obviously I am new!

                    I am running Windows Vista with FMP 9


                    I have one layout for "Items" where I have a few fields for the item name, description etc... And 2 portals (J_Item_Mat & J_Item_Labor)

                    I also have a layout for the "Project" with virtually all of the same fields And 2 portals, but these are all the "Project" fields that are looked up when I select the "Item ID/Item" from the drop down list on the Project layout.

                    I have created separate Joining tables for the "Project" i.e.. J_Proj_Mat & J_Proj_Labor

                    But that is where my problem is, I would like to have the Portals populated with the information looked up from the related generic "Item"


                    Let me know if you need additional information,,, AND Thanks for your help!