Theatre Lighting database question

[Tom W]

Hi there!

This is my first post on this forum. Have heard great things about the greater Filemaker community and hope someone might be able to help me with this problem.

Before we start, I warn i'm a relative newbie to this software...

I am playing around with FileMaker to create a database to catelogue lighting fixtures used in a theatre rig. Am hoping that by using one main database, i can generate varied paperwork for a number of different uses.

One of the peices of paperwork I wish to produce is what we call a 'Fixture Schedule'. It's a list of all lights used on a show sorted by position (which i've created easily enough).

In theatre, when we rig several lights on a single position (ie, four spotlights on a single overhead lighting bar) we give them a UNIT number that corresponds to their position on that bar. Generally we work from right to left, so the unit on the far right of the bar would be Unit 1 and the unit on the far left would be Unit 4.

This number is unique to the position, but not unique to the total rig, as a total rig usually has many positions.

So, i've created a list layout that has all my lights sorted by position, and have created a UNIT field for the unit number. I have a script that sorts this layout into Position and Unit.

What I'm looking for is a calculation that I can apply to the unit field, that would do the following.

a) Allow me to enter in a number manually.

Use the data entered to adjust other records in that position.

For example. Say i have those four lights recorded in my database, and they're properly numbered 1-4 in the unit field. Now, say I want to add a new spotlight to the same bar between units 2 and 3. I add that light to the position and give it a unit number of 3. Is there some way of making the original units 3 and 4 update their values to 4 and 5 to accommodate the new spotlight?

Apologies for the verbose post! Would be greatful if anyone could point me in the right direction...

Cheers :-)

Tom

[Fenton]

Basically what you want to do is to renumber the lights within a Position (however you identify a "positon", it must be unique within the table), at least the ones after the one you've "inserted." The usually method to do something like this is to add the new one with a decimal entry. So a light between 2 and 3 would be entered as 2.5.

You can then go to all the lights for the Position, sort by the number field, and run a Replace command, using the [x] Serial numbers option, starting with 1, increment by 1. The result is that the numbers less than the insertion remain the same, the 2.5 becomes 3, the next one, 3, becomes 4, etc..

The problem is to isolate the records to only those of that Position. This is critical, as the Replace command runs on the Found Set (though you could get fancy, and be safer by using the [x] By calculation option to test during the operation).

There are 2 ways to isolate the records. The first method would be to use a Find. You would need to capture the Position; which, as I said, must be unique. If you need to use 2 fields to positively identify it, that is OK (like "location", etc.). But you have to be able to Find its, and only its Units.

You could set the Position into a script Variable, then Enter Find mode, set the $position variable into the Position field, Perform Find. You may want to trap for errors. You should not have any, since the position obviously exists. But I would be cautious at first.

The other method to isolate the Position's units is to create a self-relationship on Position (and whatever other field is needed to positively identify it). Then you could use Go To Related Record [ self-relationship ] to isolate them.

Then run the Replace. Or run a Loop with a counter.