searching within a range

[errorist]

I have to make the following database, in this example:

there's a box with tickets. The box number is 1418.

in the box are 1000 tickets, all numbered unique.

The range of tickets in this box is AB2000 to AB3000

Now, one should be able to find in what boxnumber a certain ticket is.

So, when looking for ticket ab2512 the database should show box 1418.

So far, i cannot figure out who to to this, other than generate 1000 individual records, one for every ticket in the box. But i'm sure there's another way

Thanx in advance !

[harryk]

You suggest something difficult, but if you mean that attached to a box is always a fixed range of tickets then:

perhaps use the case function:

boxnumber is a calculation like

Case (ticket >=ab2000 and ticket<=ab3000,1418, ticket >= ?? and ticket<=??,??, etcetera)

The calculation me be a long list, probably too long. If there is a certain logic in the way the ticketnumbers are organised and they are attached to boxnumbers which increment by 1 with the next range of ticketnumbers, then you might use something like this (I write down the intention, not the correct FM syntax..):

define an offset value, perhaps related to the letters in the ticket number (I do not know)

get the real number out of the ticketnumber (which is in fact text) with

TextToNum(right(ticketnumber,length(ticketnumber)-2))

and perform an integerdivision on the output, adding the output to the offsetvalue. When there are 1000 tickets in a box you divide by 1000.

Perhaps this is enough to generate your brain, if not, tell a bit more about the way the tickets are organised, so that we can think a littlebit more specific.

Harry

[BobWeaver]

You haven't really explained what you have for fields in the database, but if you have one record per box, and the following fields:

BoxNumber

StartingTicketNumber

gSearchTicket (global field containing the ticket you're looking for)

..then you can do the following script:

enter find mode

set field StartingTicketNumber="<=" & gSearchTicket

perform find [no restore]

sort [startingTicketNumber increasing]

Go to Record/request [last]

Show all records

omit record

Show omitted

This will take you to the record for the box you're looking for.

[errorist]

Thanx alot for your kind replies guys. It was indeed quite simple, just useing one search with <= and >= before the searched number did fine.

Another problem now is, FM doesnt make difference in ticket AA12345 or BZ12345.

That means i'm probably will have to split the letters and the numbers, as soon as the records are imported from disk. Still trying to figure this one out.

[BobWeaver]

Sounds like you might have the field defined as number rather than text.