5 Replies Latest reply on Jun 28, 2015 6:48 AM by philmodjunk

    Selecting records that have a distinct field

    PeterArnold

      Title

      Selecting records that have a distinct field

      Post

      I am considering moving my databases into FileMaker, and I have searched the forum but I can't see an answer.... here is my problem:

      I have a file of orders, with each order having one or more items. The source of this which I import into FM presents the list as one record per order item, so one order number may appear on several records. I want to print out labels to send out these orders, so I need just one label per order. So I need to ask the list to present itself as a list of distinct order numbers.  My present database can do this by clicking one button, but I can't find a similar facility in FM.

      Can someone advise me please?

        • 1. Re: Selecting records that have a distinct field
          philmodjunk

          Ultimately, you may find it a better design option to add a table with one record per order that links to your table of imported orders line items. A fairly simple combination of scripting and a relationship can create such records immediately after importing new orders data.

          But an option that does not require such a table would be to set up a layout with the body changed to a sub summary layout part "when sorted by" order number. This will show one row of data for each record in your set of orders records. This would probably work just fine for a dedicated label printer that prints just one label at a time, but is problematic for "averay" type label sheets printed from a plain paper printer.

          • 2. Re: Selecting records that have a distinct field
            PeterArnold

            Thanks for that, and I am sure that both suggestions would work. But as I explained, I am totally new to FM and would be grateful for  advice on how to actually do it.

            For instance, I favour the first suggestion, but am flumoxed by "A fairly simple combination of scripting and a relationship ".... where do I find out how to do that?  Or can you give me an example?

            Whatever, it certainly seems a lot more complicated than the single click of a button with which I can achieve the result on my current, out-of-date database.

            • 3. Re: Selecting records that have a distinct field
              philmodjunk

              There are multiple training resources available to help you learn how to use FileMaker. Which is best will depend on your budget and what related skills you already have.

              Define two tables in your database using Manage | Database.

              Link them in a relationship like this:

              Orders-----<OrderLineItems

              Orders::OrderNumber = OrderLineItems::OrderNumber

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

              While in manage | Database | fields, double click OrderNumber on the Orders table to open field options. Select "Unique values" and "validate always" on the validation tab.

              Now import your data twice, once into each table. If doing a manual import, do each import on a layout based on the table into which you want to import data. When mapping data to fields, only select the fields appropriate for that table. For Orders, you'd select the order number and any values that are the same for the same order. For OrderLineITems, you'd select order number again and then the values that are specific to each item ordered.

              During the import to orders, the validation options specified will automatically omit duplicates. You'll get a message when you are done that not all records were imported. This is the expected result.

              Once you are done with the two imports, a portal to orderLineItems on the orders layout should list all the items ordered for a given order.

              • 4. Re: Selecting records that have a distinct field
                PeterArnold

                Thanks a lot for all that, Phil, all very useful.

                I have worked it all through and it certainly does the job, but I have to say it is a lot more cumbersome to use than my current database. However, it has all been of great help in assisting  me to appraise Filemaker, and all I have to do is to decide whether the pros outweigh the cons before making a purchase.

                Many thanks again

                • 5. Re: Selecting records that have a distinct field
                  philmodjunk

                  What I have described is the simple, manual method of doing this. It can all be scripted such that all the user does is click a button, select the file to be imported in a dialog and then the data appears where it needs to be.