Serial within serials?

[viennamac]

I'm sure this is basic, but I'm completely stumped and nothing I have found by searching has caused any lightbulbs to appear above my head, so please humour me!

I have a database of tapes, each of which may contain several ads. The tapes are numbered (from 00001) and the ads on them should be numbered, too, starting at 001 on each tape.

I've created a separate database for the ads and put a portal in the tape database. So far, so good. I just can't find a way to automatically number the ads on each tape. I've only been able to do it manually and the addition of an ad number creates the record in the ad database. But there must be a way to do this automatically...

While I'm asking: Users should be able to search for an ad across all tapes. My present solution returns a list of the tapes on which ads with the search term are, but shows simply the first ad on the tape, not necessarily the ad in which the search term is. Any suggestions?

Thanks for any help you can give me!

[CobaltSky]

Hello viennamac,

It's not clear from your description whether you have a single add record related to multiple tape records (and potentially in a different sequence/position - and therefore requiring a different serial depending on which protal it is being viewed in.

If not, you may find the technique shown in a demo which is available at:

http://www.nightwing.com.au/FileMaker/demos1.html#d7

to be applicable.

HTH

[transpower]

Make a repeating field in the tape file for the ads. No matter which repetition the ad is, a search in the repeating field will find it.

[viennamac]

Thank you both for your replies.

To CobaltSky: Any given ad (commercial, advertisement) will only be on one tape. This is an archive of commercials that the company has produced.

One demo you linked to (Custom Serial) looks promising, however the ClientID (which would be my tape number) must be chosen and entered manually, whereas I need both the next tape number (if a new tape is to be added) as well as the next ad number (if a new ad is added to a tape) to be assigned automatically. In addition, as far as I can tell, the system in the demo would not allow me to create a layout in which I see only one tape number (ClientID) and all the related ad numbers (Client Serial). Perhaps this can be done by some method that I am not familiar with (I am quite the novice).

To transpower: I'm not quite sure I understand you. I tried making the ad number a repeating field, but when I perform a search, it still only returns "001" (as that's the first ad number on any tape containing an ad numbered "003" for instance.) I've come to the conclusion that the search for ads must take place in the related file containing the ads as separate records (duh?), but then I need an easy and intuitive way to get users to that file to perform the search and then back again to the file containing the tapes as separate records. Unfortunately, my scripting is even worse than my filemaking. :-(

Thank you once again for all your help!

[transpower]

As I understand it, you have multiple ads per tape and multiple tapes per ad. So if you have a repeating field which lists the ads for each tape (in the tape file, with one record per tape), then when you do search for an ad it should pull up all tapes for that ad, regardless of where the ad is in the repeating field.

[CobaltSky]

Hi viennamac,

It's true the demo I referred to accepts manually entered serials. That is because it is designed to show a mechanism for generating the secondary serials, not the primary ones. But the machanism is applicable regardless of how the primary serials are generated.

I am attaching a slightly modified version of the demo (see above) which shows both sets of serials being generated automatically when a record is created. The technique I've used to auto-generate the first serial (the 'ClientID') is a random process based on an auto-enter calc - but the point is that the method is not important.

You would presumably be generating your related advertisement record via the relationship or via a script. In either case, the tape serial will be entered (by whatever means) as the key to link the add to its corresponding tape record. The method shown in the demo will allow you to automate the creation of the add serials so that they follow automatically from the placement of the key value into the tape serial field.

As regards searching for advertisements, yes you should script the search to occur in the related file if you want to isolate the specific advertisement(s) that match your criteria. A search within a portal will locate the parent record (as it would were you to use repeating fields, in fact), but will not identify which of the related advertisements matched your find. After conducting a search in the related (adverts) table, you should use the Go to Related Record[ ] command to return to the corresponding tape record.

And last but not least, I would stay *well away* from repeating fields in this case. Repeating fields do have their uses, but they also have their limitations - and this is a classic example of the kind of case where the limitations would far outweigh any benefits.

AutoSerial.zip

[viennamac]

Once again, thanks to everyone for all the help! I'll be a while processing and tinkering with all your suggestions and ideas, which is better than the shots in the dark I've been firing!

I'll report back and let you (and future readers) know how it went!