I would be you, I would create a special file in Excel with all the
'designation fields' listed. Then, by hand I would put them in the right
order - as there are many items that cannot be logically sorted -. When
done, I would import that Excel file into a new FileMaker file, to which
you will add a ID field that you will fill up by a sequential number
using the Field / Replace menu.
For there, I would either include that ID field by relationship - with
the 'designation field' as the link - into your actual files, or leave
it there and address it with an inverse relationship to get the proper
Then you'll use in your data base only the ID for anything you'll need
Of course, you may use that technique with more sophistication : ie
adding brand, type and model into one field, etc.
Even with thousands of items, as there certainly much less brands, it
shouldn't take too long to do it.
"Lee Steffeck" <email@example.com> a �crit dans le message de news:
> I started working on a new database for our fire alarm shop. The
> idea was to keep track of all the devices (smoke detectors, pull
> stations, etc) as well as how many different makes and models of fire
> alarm panels that we maintain. It would also be there to answer the
> question that comes up at least once a year: "Exactly how many smokes
> (or pull stations) do we have in the field?" The boss has also
> requested that one of the forms to create would be an annual test
> This would allow the techs to find the building they need to test in
> the database, and then print up all the devices they need to test by
> listing the following: their type (i.e. photo smoke, ion smoke, pull
> station, duct detector, etc), their physical location in the building,
> and their designation on the fire alarm panel.
> This designation field is my trouble. The techs have told me it is
> much easier to follow the list for testing and checking off completed
> devices with a designation sort. The problem is that each panel brand
> has a different way of listing the designation. For example one fire
> alarm panel designates devices with this pattern: 6:M1-1, 6:M1-2,
> 6:M2-37, 6:M2-38 and so on. Each "M" stands for map. Each map card
> can have up 250 devices, some panels can have up to a dozen or more
> cards. But other panels use this: 1:4:001, 1:4:002, and so on.
> means panel 1, card 4, device 1. This system can have multiple panels
> with multiple cards.] Others use: L1S18, L1S19, etc to show Loop 1,
> Smoke 18 and Loop 1, Smoke 19. And still others (the final example)
> will use Z1-1, Z1-2, Z1-3, etc to show Zone 1 device 1, Zone 1 device
> 2, Zone 1 device 3.
> I created a database and imported around 400 records to experiment
> with and try this out. If I make the designation field a text field
> then I get that screwy text sort so my order becomes similar to M1-1,
> M1-11, M1-12, M1-2, M1-3, M1-4 and so on. When I change the
> designation field to a number field with that data all is well in the
> sort. But when I enter new data FMP tells me that it is a number
> and that letters are not allowed. It seems that FMP doesn't mind
> letters in a number field if the field was a text one first and
> to a number field later, but don't try to add any letters in a field
> after the change. So my question is now what do I do?
> Should I use sort somehow with the TextToNum function? And will
> that permanently change the data in the field? Do I create several
> fields for the data entry so that with a 6:M1-101 I would need three
> fields and enter data in each? Then create a script to sort by three
> fields based on the panel type? I am at a total loss as to how handle
> this. I have done some database design but not much.
> I am using FMP 5.5 on a Windows XP machine.
> The email address is good except for the first two letters in the
> second half.