Jump to content

Performing A scripted find across several fields

This topic is 7363 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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...

Link to comment
Share on other sites

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




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,


Link to comment
Share on other sites

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 <> "", "

Link to comment
Share on other sites

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,


Link to comment
Share on other sites


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.

Link to comment
Share on other sites

This topic is 7363 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Who Viewed the Topic

    1 member has viewed this topic:
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.