f



how to sort with text and numbers in one field

   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 form. 
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 "M"
cards.  But other panels use this:  1:4:001, 1:4:002, and so on.  [This
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 field
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 changed
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.
0
final (8)
1/20/2006 8:34:15 PM
comp.databases.filemaker 11053 articles. 0 followers. amosw01 (46) is leader. Post Follow

2 Replies
784 Views

Similar Articles

[PageSpeed] 4

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 
ID.
Then you'll use in your data base only the ID for anything you'll need 
to do.
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.
Remi-Noel


"Lee Steffeck" <final@xdcomcast.net> a �crit dans le message de news: 
200120061334157317%final@xdcomcast.net...
>   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 
> form.
> 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 
> "M"
> cards.  But other panels use this:  1:4:001, 1:4:002, and so on. 
> [This
> 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 
> field
> 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 
> changed
> 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. 


0
Remi
1/20/2006 9:20:10 PM
Leave the field that you entered the data into as a text field.
Create a calc field of type number with formula:
YourTextFieldNameHere

Instead of sorting by the text field, sort by the number field.

0
FP
1/20/2006 10:07:01 PM
Reply: