Go Premium for a chance to win a PS4. Enter to Win


Updating multiple indexes in one database?

Posted on 2002-03-05
Medium Priority
Last Modified: 2010-05-18
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.
Question by:taclarke
LVL 30

Expert Comment

ID: 6846008
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.

Expert Comment

ID: 6881380
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
LVL 30

Expert Comment

ID: 6881392
Naubrey: please familiarize yourself with the eqiquette of EE before posting answers and locking up questions.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 6881597
I see you are not new here.  Please refer to the tips below.

Proposed answer rejected.

Thank you
E-E Moderator

Author Comment

ID: 6883992
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.
LVL 30

Accepted Solution

weed earned 200 total points
ID: 6884040
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.

Expert Comment

ID: 6884630
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

Expert Comment

ID: 7391760
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.

Expert Comment

ID: 7465620
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.

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question