Performing A scripted find across several fields

[ktownson]

I have a database that holds records of seat reservations for theatre performances. After a reservation is made, seat names/numbers are assigned for each person.

For example, John Doe requests a reservation on Aug. 26th for four seats. He is assigned seats A01-A04. A01 is entered into a field called "seat01" A02 is entered into "seat02" and so on. There are 12 possible seat fields per reservation.

It would be useful for me at a later date to find out who has been assigned a particular seat. Since the seat name could be in any one of the twelve (seat01-seat12) fields, how can I write a script that would search all twelve fields of my found record set and find the reservation where that seat resides?

One other question--when I wrote this database, I was discouraged by users of this forum from using a repeating field instead of the twelve separate fields? Would this be easier with a repeating field?

Thanks for being gentle to a newbie...

[Rigsby]

With a simple calculation you could create a search field that contains all the seats reserved. So for example, if you have the following:

Seat1

Seat2

Seat3

Seat4 etc.

Create a calculating field called “allseats” for example, which would look something like this:

Case(Seat1 <> ""; Seat1 & Case(Seat2 <> ""; "" & Seat2 & Case( Seat3 <> ""; "" & Seat3 & Case( Seat4 <> ""; "" & Seat4;""))))

What you would then have is a field with all the reserved seats together, but separated by a return. You could leave the return out, but its tidier with it. I would also include the TRIM option (Which I left out here so as not to confuse the calculation), which would prevent empty entries being entered into the field allseats. But here it is just incase you want to copy and past it to your solution:

Case( Trim(Seat1) <> ""; Trim(Seat1) & Case( Trim(Seat2) <> ""; "" & Trim(Seat2) & Case( Trim(Seat3) <> ""; "" & Trim(Seat3) & Case( Trim(Seat4) <> ""; "" & Trim(Seat4);""))))

Now you need a search layout and a script that goes to that layout, enters find mode, goes to field “allseats” and after the search returns to your original layout.

Hope this helps,

Rigsby

[ktownson]

Well, that was a novel approach and eventually, it worked! I say eventually because apparently Filemaker has different list separators for Europe and the U.S. I had to substitute a comma for your semicolon. Also, I think we might have different versions--I'm using 5.0 and the Trim function apparently does not work. I also had to insert a paragraph symbol to get the fields to separate properly.

Anyway, I fiddled around a good bit with what you sent and eventually came up with this:

Case(seat01 <> "", seat01 & Case(seat02 <> "", "

[Rigsby]

Glad I could help. Well, I did actually put paragraphs in my answer but they seem to have been formatted out of my text during posting – sorry for that. And yes you’re right – I should have remembered the thing about the separators, I use the English version of FMP 4.1_v3, but running on a German computer under German Windows, so it automatically uses semicolons, not commas as separators. Sorry for that, too. LOL.

What I’m interested in now is your comment about the trim function in FM5. Is that true? No trimming in 5? I can’t believe that – the function is a lifesaver for developing solutions for computer novices. I do a lot of development work for people who don’t know much about computers or who are not the best typists in the world. So for example they enter a name:

Field1 = Smith

Field2 = Bob

And they manage to get an empty space after Bob, so in field3 (Name full) they get:

Bob Smith instead of

Bob Smith…..

See my point? The trim function is essential. Maybe FM changed the name or something?

Best regards,

Rigsby

[Rigsby]

See! It did it again; it took the extra space out between Bob and Smith during posting! LOL

[Vaughan]

ktownson

To get back to the original thread... the reason for the difficulty is that instead of using twelve fields to store the seats, each one should be a separate record in a related database. Finding seats is then the simple task of searching the related db.

If you get the chance, try redesigning the system with a related file for seats and a portal to allow the easy addition of records. You'll find it so much easier.