9 Replies Latest reply on Jan 5, 2010 8:36 AM by philmodjunk

    lookups help!



      lookups help!


      hello! i almost (thanks to the fm forum) finish my invoce database, BUT some issue is killing me!, i have 2 tables on the same file, i can made all the relationships, all the filed copy from one table to other, BUT when I copy the field description (wich is the one that have from 1 to 34 description of my products) I try to copy to other table and just copy the firts one, all the other from 2 to 34 doesn´t copy, some one can help me, i check the relationships, the lookup option but just copy one, this happen in description, cant, price....



        • 1. Re: lookups help!

          I can't really follow this description of your problem. "when I copy the field description" could be interpreted multiple ways as there is more than one way you could be trying to do this.


          Can you post a shot of your relationships? Can you post a shot of your layout?

          To post images, you have to upload the image to a file sharing site and then post the link to that image here in the forum.


          Perhaps you could fill in the details of what you are trying to do in a step by step process.

          • 2. Re: lookups help!

            Hi Phil!


            Thanks for your reply,  I'm sorry for my bad explain... i have 2: tableA and tableB, in the table A i have 1 field named: description, this field have 34 repetitions (here i put the item inforamtion that i'm sold) I need to copy the information of the field description with all the 34 items on the table A to the table B on the field call invoice_description.


            With the copy paste just copy the first item, and i can copy the others, i read a lot in the forum and i found a script but doesn't work.


            by the way both fields are related, I don't know if this inforamtion can help, let me go to the mac where i have the file and take some snapshots and put here

            • 3. Re: lookups help!

              I've seen other make this suggestion to you and now I'll echo them. Your best bet is to eliminate the  repetitions and use a table of related records instead. Check out portals in filemaker help and see if it makes sense to you.


              If you insist on repetitions, you'll have to copy over one repetition's value at a time or use Set Field in a script to move the data one repetition at a time.

              • 4. Re: lookups help!

                Hi phil:


                yes i read a lot but nothing work some one wrote this script

                create a calculation field with calc:


                Substitute ( List ( YourRepeatitionField ) ; Char ( 13 ) ; Char ( 13 ) & Char ( 10 )  )


                and export that field.


                but doesn´t work


                other wrote


                you can try something on the basis of this script:


                Go To Layout [ FirstTableLayout ]
                Set Variable [ $var ; List ( RepeatingField1 ) ]
                Go To Layout [ SecondTableLayout ]
                Set Varable [ $rep ; $rep + 1 ]
                Set Field [ RepeatingField2[ $rep ] ; GetValue ( $var ; $rep ) ]
                Exit Loop If [ ValueCount ( $var ) = $rep ]
                End Loop


                but doesn´t work to.


                I hope i can keep the the field repetitions, but if this imposible can you explaing to me how to do that with table or perhaps a portal?



                • 5. Re: lookups help!

                  The script you quote should work. What do you see that tells you it doesn't?


                  To use a related table with portal, I can't give you all the specifics as I don't have any clear idea as to the structure of your database and what the 34 repetitions represents other than that they are "descriptions" of some sort.


                  Basic steps to implement a portal displaying a list of records:

                  You need two tables, we'll call them "Main" and "List".

                  Define an auto-entered serial number field, PrimaryKey in Main.

                  Define a number field, ForeignKey, in List.

                  Define a text fied, description, in List


                  In Manage | Database | Relationships, drag from PrimaryKey to ForeignKey to link the two tables.

                  Double click this relationship line and select "Allow creation of records via this relationship" for List.

                  Click Ok to leave Manage | Database

                  Find your layout for Main and enter layout mode.

                  Use the portal tool to draw a rectangle on a blank portion of the layout.

                  A portal set up dialog appears, select List as the table occurrence for this portal

                  Select what other options you want in this dialog.

                  Select Description as the field to appear in the portal.

                  Save your layout changes and enter browse mode.

                  Click in your portal's single blank row and enter a description.

                  Click in the new blank row that appears in the new blank row that appears and enter a description.

                  Keep going until you've entered 34 different description values.


                  There are a variety of different portal settings that affect its look and function, experiment with them in portal setup... (double click the portal in layout mode)

                  There are many ways to change the relationship and/or values in the two match fields to control which records in list are visible in the portal for a given record.

                  • 6. Re: lookups help!

                    Hi the first one when I put in the " char" the fm doesn´t reconized


                    on the second script i send to you when i wrote i get this


                    Go To Layout [ "HOJA_MAESTRA_V2" (HOJA_MAESTRA_V2)]
                    Set Variable [ $var ; Value:List (HOJA_MAESTRA_V2::descripcion) ]
                    Go To Layout [ "10_PRESUPUESTO" (PRESUPUESTO)]
                    Set Varable [ $rep ; Value: $rep + 1 ]
                    Set Field [ PRESUPUESTOS::P_descripcion [ $rep + 34 ]]; GetValue ( $var ; $rep ) ]
                    Exit Loop If [ ValueCount ( $var ) = $rep ]
                    End Loop


                    where:  -HOJA_MAESTRA_V2 is the layout name where the table where i nned to get the data is

                                - descripcion is the name of the field with the repetitions

                                -PRESUPUESTO is the other layout where is the table in the i should paste de data

                                -P_descripcion is the filed with the repetitions.



                    • 7. Re: lookups help!
                         sorry i don´t know how the emoticons appears  there should be " P:"
                      • 8. Re: lookups help!
                           ups ":P"
                        • 9. Re: lookups help!

                          Hi the first one when I put in the " char" the fm doesn´t reconized

                          I can't figure out what you mean by that.


                          The script is designed to copy over one value at a time. I see one change that needs to be made


                          Go To Layout [ "HOJA_MAESTRA_V2" (HOJA_MAESTRA_V2)]
                          Set Variable [ $var ; Value:List (HOJA_MAESTRA_V2::descripcion) ]
                          Go To Layout [ "10_PRESUPUESTO" (PRESUPUESTO)]
                            Set Varable [ $rep ; Value: $rep + 1 ]
                            Set Field [ PRESUPUESTOS:: P_descripcion [ $rep + 34 ]; GetValue ( $var ; $rep ) ]
                            Exit Loop If [ ValueCount ( $var ) = $rep ]
                          End Loop


                          Take out the "+ 34" and it should work.

                          Note: "Value:" is added automatically by filemaker you simply put the variable name $var, or $rep in the first box and then click specify for the second box and enter all the text to the right of Value: in the specify calculation dialog.


                          Many new users get confused the first time they try to add Set Field to a script. See the following link for step by step instructions:

                          Selecting an "All" option with checkboxes


                          PS. to get rid of pesky emoticons, insert a space between the : and the next character. You can even edit your message after you've posted it by selecting "edit" from the options menu listed above the Kudos control--which is how I inserted this PS.