5 Replies Latest reply on May 22, 2012 2:43 PM by philmodjunk

    Conditional Numerical Sequence



      Conditional Numerical Sequence


      I'm creating a layout for driving routes. We have numerous routes (ie Lakewood, Long Beach, Cerritos, etc...) in our Routes field and need to assign each record a different sequenced number according to which route it's in. I'm hoping we can also set it to update any following numbers if we have to add a new record at a later date. I've been working with FM 11 for a few months, but this one is beyond me so far.


      Name     Route     Route Order

      Smith     Lakewood   1

      Black      Lakewood   2

      Osbourn  Cerritos      1

      Frank     Cerritos      2

      Blake     Lakewood   3

      Thanks for any advice!

        • 1. Re: Conditional Numerical Sequence

          I'm not sure what your example is supposed to show. Why is there data from two routes (lakewood and Cerritos) in the same list? Why does "Blake Lakewood" appear twice?

          I'd have one table for Routes and a related table for stops on those routes. An "order" number field in the stops table could serve as the sort key to get the stops in the order needed for that route--either in a list view layout or in a portal. But instead of that "order" field, I'd put @@ on the layout or in the portal row as layout text to get a gap free numerical sequence starting with 1 for a given route. That allows you to use values such as 10.5 to get stops into the correct order and still get a perfect sequence when veiwing or printing the data.

          • 2. Re: Conditional Numerical Sequence

            I'm trying to show the way we'd like the layout to look. Unfortunately we need to be able to view our entire client list with their route name and order within that route all at the same time, so having separate layouts for each Route won't work for us.

            "Blake" is only once. "Black" is also once. I chose bad name examples, I guess.


            I basically just want to know if it's possible to have the number field (ie Route Order) formatted so that it will place it next in the sequence of each route.

            IE: IF route is Lakewood THEN = value of last Lakewood record with a value +1.  Or if there's a simple way to number each row in Table view (the way you can easily number rows in Excel).

            • 3. Re: Conditional Numerical Sequence

              If this is how you want your report to look, why are there two routes mixed together and why aren't they sorted by the route order field.

              I would think that it would make better sense to have your layout look like this:

              Route: Lakewood

              Stop      Order

              Smith     1
              Black      2
              Blake     3

              Route: Cerritos

              Stop      Order

              Osbourn  1
              Frank     2

              Using a summary field, it's quite possible to number the stops in order and to restart the sequence with each route, but I'm still trying to understand your example before I suggest a solution so I can be sure that what I suggest actually works for you.


              • 4. Re: Conditional Numerical Sequence

                Alright, I gave myself and my coworker a few days to think your suggestions over. We think the separate layouts could work. I'm not a programmer though, so honestly your first comment is mostly over my head at the moment, so I'll have to do some learning before implementing it. I have only the vaguest notion of what a "table" is and no clue what a "portal" might be. Not what you want to hear, I know.

                I'm just gonna clarify what we need so you can confirm whether that's the best method (per your last comment). Sorry for being so annoying. I appreciate the help.


                We have a client contacts layout. When we create a record, we input which Route they're going to be in via a Route field (it's a drop down ValueList).

                We need to be able to create a master route list that includes everyone on that route in the order we'd like to drive.

                We need to be able to add new records to that route list without having to update every record's order number.

                If possible, we'd like to include what is being delivered (ie cookies, wine, or both) and a summary count of each.

                We need to make the layout simple enough to export to Excel.


                Thank you!

                • 5. Re: Conditional Numerical Sequence

                  Careful, I haven't made any layout suggestions. Tables and Layouts are two different things--even though FileMaker automatically creates a layout for every table with exactly the same name.

                  You'll need more tables yet before you are done:

                  Clients--the list of recipients (These would be the "stops" I mentioned earlier, I was thinking of a bus route rather than a delivery route)

                  Routes--each delivery route gets one record

                  Products--one record for each type of Product delivered (I'm assuming you have an inventory of specific products to deliver, not one of a kind items.)

                  Product_Client--one record for each product delivered to a client



                  Products::ProductID = Product_Client::ProductID

                  Clients::ClientID = Product_Client::ClientID

                  Clients::RouteID = Routes::RouteID

                  And if clients are billed for products delivered (It's not a meals on wheels charity or something...), you'll probably need an Invoices table between Clients and Product_Client so that you can generate bills for each delivery. You may need that anyway depending on your procedures so you have a single record for each date that you deliver one or more items to a given client.

                  Here's how to Number your clients in delivery order starting with one for each route:

                  Define a summary field in Clients as the "Count Of" ClientID (any field that is never empty will work)

                  Select the Running Total and the restart totals when grouped by options. Select RouteID as the "group by" field.

                  As I described earlier, you'd use a separate number field to control the delivery order for a given client. Unlike this summary field, it does not have to be a perfect sequence of integers starting with 1. If you need to insert Client A between client's B and C in the delivery order, you can enter a decimal value such as 2.5.

                  Now you can set up a list view layout based on Clients. You can put the RouteID and fields from the related Routes table in a sub Summary layout part "when sorted by RouteID". Place Client names, etc in the body of this layout along with the summary field to display the delivery order. Sort your records by RouteID and then by the delivery order field and you'll get your list of clients grouped by delivery route then correctly numbered in order of delivery.