14 Replies Latest reply on Apr 13, 2012 11:09 AM by SeamusDoone

    Createing a Usable List

    SeamusDoone

      Title

      Createing a Usable List

      Post

      Hello,  I have a problem.  I have a large table with 2500 records in it.  I have a list of names that I want to extract from my table and create a report.  These names have few common parameters and I have to search for them by name.  The table I have calculates and records the number of invoices associated with each client.  I want to create a custom list of the clients from the list I was given.  Is there a way of adding records one by on to another table that keeps all the information for each client.  Any ideas?

        • 1. Re: Createing a Usable List
          philmodjunk

          What tables do you have now? The typical invoicing setup usually looks like this:

          customers----<Invoices---<LineItems>----ProductsAndOrServices

          Do you have a customers table or is it that you need to create one from your existing data in invoices?

          It's possible to import records from Invoices into a new Customers table and use a unique values/validate always setting on a field to filter out the duplicates during import. But be careful of using names for this purpose. You might have two or more customers with the same name.

          • 2. Re: Createing a Usable List
            SeamusDoone

            I have one table that contains contact info, invoice count, product use, etc.

            I have a list of names on a piece of paper.  I want to make a table that contains only those names and their info from the table above.  Is there a way of doing this?

            • 3. Re: Createing a Usable List
              philmodjunk

              No electronic copy of that list? If you had even an Ms Word copy of that list, you can import the names into a new table and not have to type them a second time.

              The main problem with your list is that you may have two customers with the same name. If so, what I am about to describe will fail for those customers.

              Define a table with fields for each item that you need to document that is specific to a given customer. Add a field: __pk_CustomerID and define it as an auto-entered serial number field. Define a temporary relationship like this (only because it's the only option given a list of names):

              Customers::Name = YOurTable::Name

              For each field in customers that has a corresponding field in your current table, use the auto-enter tab in field options to set it up as a "looked up value" field. Specify that it copy data from the corresponding field in your current table.

              Now you can create new records and type in the names from your list and the "looked up value" fields will automatically copy data from the first matching record in your current table.

              If you want to produce aggregate values--such as a total, average, maximum, etc. from a set of all records in your original table for a given customer on your list, this can be done as well.

              • 4. Re: Createing a Usable List
                SeamusDoone

                Hi, sorry for the late response I have attempted this and failed.  I think that you are giving me the solution here but I am having a hard time implementing it.  I do have an electronic copy of this in excel and I have the names seperating into their own column.  I have lookup values attached with different fields but this is not working.  Do the names have to be exactly the same, in other words the name list I am importing has a middle initial for nearly everyone.  Could this be my problem?

                • 5. Re: Createing a Usable List
                  SeamusDoone

                  Also, I have the look up values stored as text, does this need to be different?  I imported the list of names and my lookup values are simply not coming in.  Do I have to refresh the list somehow to get them to update?

                  • 6. Re: Createing a Usable List
                    philmodjunk

                    If you use a relationship based on the Names, they will need to exactly match, letter for letter.

                    • 7. Re: Createing a Usable List
                      SeamusDoone

                      Ok thank you for your help

                      • 8. Re: Createing a Usable List
                        SeamusDoone

                        Ok so I havemultiple customers with exactly the same name and none of the look up values are working.  What am I doing wrong here?

                        • 9. Re: Createing a Usable List
                          philmodjunk

                          "look up values" can refer to more than one type of thing in FileMaker. Please describe what you have set up here.

                          Also, even the smallest difference in your name field can keep the records from linking to the records in your new table.

                          Edward R. Murrow

                          will not link to:

                          Edward R Murrow

                          due to a missing period after the middle initial.

                          • 10. Re: Createing a Usable List
                            SeamusDoone

                            Yeah the names are exactly correct.  In my relationships with tables I have the tables linked by name ie Name=Name.  In the lookup value I am pulling a field from the first table that is not working for any records even though the names are exactly the same.

                            • 11. Re: Createing a Usable List
                              philmodjunk

                              Yes, but how are you "pulling that value"? there's more than one way and not all may work in your situation.

                              Try putting a portal to the original table on your layout and see if any records appear in it.

                              • 12. Re: Createing a Usable List
                                SeamusDoone

                                Ok when I type in the name manually instead of importing it works.  Is there any way of doing this with importing instead of having to manually type every one in, too many names to do this with

                                • 13. Re: Createing a Usable List
                                  philmodjunk

                                  To repeat: how are you "pulling that value"?

                                  It sounds like you've defined fields with looked up value options. If you had enabled auto-enter options, they should have looked up data from matching records during the import.

                                  But there's a much simpler way. Use the portal I mentioned or just put fields from the related table on your layout and they will display data from the matching table.

                                  • 14. Re: Createing a Usable List
                                    SeamusDoone

                                    I figured it out thanks for all your help