Updating multiple indexes in one database?

I am keeping track of tape stock broken down into types - Master Tapes, Stock footage, etc.  Right now they are in separate databases so they can have separate indexes and I can number the tapes by their type and index number. I want to be able to have them all in one database so that I can search for tapes using one query, instead of doing the same search in each database, and also add records for any tape type wher the proper index will automatically be incremented.

The index is just the number of tapes (hence the number of records) of any particular type of tape.
Who is Participating?
The easiest way is to keep them in separate databases but create a new database that uses the fields from the other databases. This new database wouldnt actually contain any data, just reference the others. There is also a field type you can use that automatically assigns serial numbers so youre not relying on the number of records.
Why not just use referenced fields? Define fields in your master database that mirror the contents in your other databases. Then do your searches from the master.
1) You have to build a file which includes all the fields you want to use:
File > define relationships. Providing you have created a common primary key or common code in all your files. usually an ID field.
2) You can then build a file/layout/table which includes any of the fields in the other files:
Layout mode. Drag the field icon to your layout (tool bar) and you are given a choice in the top pop up field to choose what related file and field you want to use.
In filemaker you move from layout mode to make structural changes and browse mode to test.
3) you can then choose Find mode, type in your criteria
to get your found set.
4) Store this find in a script for later use.
perform your find once then select scriptmaker in the menu.
type in a name for your script and select the create button. All you require is the word "perform find (restore)"
If you have any further questions please contact
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Naubrey: please familiarize yourself with the eqiquette of EE before posting answers and locking up questions.
I see you are not new here.  Please refer to the tips below.

Proposed answer rejected.

Thank you
E-E Moderator
taclarkeAuthor Commented:
Weed, I have been trying your suggestions.  I think I understand what you mean, but I am no sure how to accomplish this.  If you caould elaborate please.  The really hard part is having everything in one database, but different tape types need to have different index numbers. So If I enter data for a Master tape, the number of that tape (let's say this is Master Tape number 32) needs to change update to 32 since the last Master tape was number 31.  It has to be able to do this without using the serial number of the number of total records because each tape tape will have it's own number based on the number of that type in the database.  Perhaps the only way is to keep them in separate databases after all.
It sounds like you should be getting the database to assign and auto unique number for each record.
From the menu. Define fields > Number > Options > Auto-enter (tab) > check serial number and nominate your starting number.
You can precede the number with any group of letters such as master1 and the next value will be assigned master2 etc
weed is right - yuo do have to create a bridge file between the two files and use this for a combined search. This is common when you have multiple entities across the database (like a clients file, a contacts file, and a vendors file) and want to find out which file an entity is in. This bridge file is oftem called something like registry.fp5 and has one record for each record in the oter files. In the registry records, there is usually at least two fields: the foreign id and an auxiliary piece of information like tape name, say.

When you do a search of [registry], you will get a set of matching records from many files (say [masters] and [stock]). You can then use a relationship that maps from the foreign id in [registry] to the unique id in [masters] to show that record in [masters], similarly for [stock]. You use a button and the script step "Go To Related Record".

However there are two main problems: keeping the file in synch, and optimising the search process. The first one means that you must get to get [registry] to check its integrity sometimes, etc.

The second one is harder, but related to the first. Strictly speaking, you should try to do searches only on indexed fields - this improves speed and stability. FMP will often crash when searches are done on unindexable fields when the record count gets too big (like a field that gets its value via a relationship). You need to set up a static mirror of the fields you want to search on and regularly refresh them with the current data.
Here's another potential option:
1. Combine the db's and create a field that designates what type of tape it is; Master Tape, Stock Footage, etc.
2. Create a self relationship based on type to type
3. Create an auto-enter calculation that goes something like this:

Case(type="Master Tape", "MST",type="Stock Footage", "STF",...)& count(selfrelationship::type)+1

Result as text

What this will do is keep your tapes sequenced within their respective types as well as give you the ability to do the same search regardless of tape type.

Also, you can add zero padding to make the number look better too.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.