Multiple Record Find

[_Dan_]

I've been racking my brain trying to figure out this script for days and I'm at a loss.

I have a table that has episodes entered into a field with episode numbers, dates, descriptions, etc. The episode numbers are entered as follows:

080609T

080610T

080611T

....and so on.

We use this information to print labels for our media. Normally we'll print a single episode's worth of labels for multiple stations. I need a script where I can print multiple episodes for one station.

So far this is what I have for my script...

Go to Layout ["Episodes:Episode List" (Episodes)]

Enter Find Mode []

Show Custom Dialog ["Enter the Episode that you want to print..."; Episodes::Episode Number]

If [Get (LastMessageChoice)]=1

Perform Find[]

End If

This however only returns one episode - the episode that I enter in the custom dialog. I want to be able to find multiple episode records in a series (e.g. 09T, 10T, 11T) so that I can print labels for a single station at the click on a button.

Manually we will go to our episode table, select the beginning episode in the series and then omit multiple records. (Say we start with 09T and then end with 13T, omitting those 5 records). Then I will show omitted records which gives me the 5 that I omitted and I am then able to go to the layout with our labels, enter the correct station and print them off.

Any help or direction would be greatly appreciated.

Thanks!

[Fitch]

Have you tried searching for 080609T...080613T ?

[_Dan_]

Had not thought about entering that in the dialog, however we also have different versions of the episode with episode numbers ending in 09D, 10D, 11D, etc.

So when I enter "080609T...080613T" I also get episodes with 080610D through 080613D.

Is there a way I can constrain the found set? Perhaps by eliminating records that end in "D?" How would that look?

I'm new to FileMaker and it's scripting so thanks for the help and patience!

[mr_vodka]

I think that you need to have another field or records in another table for each station. Your data doesnt seem to be properly normalized.

[_Dan_]

Can you elaborate on "normalized?"

What exactly makes data normalized?

Thanks!

[LaRetta]

Well I totally agree with John that the data could use further normalization, ie, Station should be a separate field (if I understand that the letter at the end is the station) and probably the last two characters at the end means something different as well. If these were records or at least separate fields then it would be much simpler.

However, I felt like playing with the find criteria and came up with this. It accepts the User request in Custom Dialog, stores the criteria in variables, and loop/sets multiple find requests. Do you think your Users could do something like this?

I wish I had more specifics about your numbers. If you entered T 09 11 ... indicating that you want Station T and range 09 through 13 it will find any that end with:

09T

10T

11T

... but might there be a T in the MIDDLE or BEGINNING of the field? It might be safest to search for #####09T if you can guarantee the exact number of characters. This script can be easily modified - just change the * to exact number of #'s. But at least this gives you something to play with if you do not wish to normalize your data further.

LaRetta

episode.zip

[Fitch]

This might work: after the Find request is entered in the dialog, your script could grab the last character and constrain the found set. E.g.

Enter Find Mode

Show Custom Dialog( "Enter the Episode"; Episodes::Episode Number )

If( Get(LastMessageChoice)=1 )

  Set Variable( $letter ; Right( Episodes::Episode Number ; 1 ) )

  Perform Find

End If



Enter Find Mode

Set Field( Episodes::Episode Number ; "*" & $letter )

Constrain Found Set

[comment]

You can read more about normalization here:

http://en.wikipedia.org/wiki/Database_normalization

There are several normalization rules (or norms), but the one that applies here is the first normal form, and more specifically the part that's often expressed informally as the "one fact per field" rule.

[Mike J]

Why couldn't Go to related record work here? Isolated the related records by selecting see only those that relate.

I agree that the ID's seem a bit difficult to handle and should be (maybe they are) split.

If it's an important standard of practice and others need to see this, then a calc that concatenates the information to give you your "ID". But it should be split into separate fields.

We had a similar issue, the users required me to show them M2L100-1...M2L100-10. This showed three layers of relationships and used this as a unique ID. Behind the scenes it was just a calc and not the system unique ID.

Just thought I'd through that out there for discussion.

Mike

Edited June 4, 2008 by Guest

[LaRetta]

If the data doesn't have a Station field and a Version field then it truly should be normalized as John suggested and Michael explained. If that is not possible, I like Tom's suggestion the best. But I'm unsure if the User wants to enter the entire Episode Number as 080609T...080613T. I thought only the last three characters should be considered in the find.

A relationship to resolve this issue is like attaching a band aide instead of performing the needed surgery; it should be split to begin with. There is no sense in adding to graph clutter just to pull out the necessary pieces from a single field. John nailed it, Michael clarified it and Tom provided a contingency plan if it can't be split. :wink2:

[_Dan_]

Well I totally agree with John that the data could use further normalization, ie, Station should be a separate field (if I understand that the letter at the end is the station) and probably the last two characters at the end means something different as well. If these were records or at least separate fields then it would be much simpler.

I wish I had more specifics about your numbers.

The letter at the end of the episode number is actually not the station, but the version that is sent out. We have 3 different versions that are sent out with the first 6 digits indicating the date (i.e. 08 = year, 06 = month 11 = day) and the final letter being an indicator of what version that is. (T, A, or D).

The station is actually on another table within the database. I have an episodes table with episode information and a stations table with station information. Station ID and Episode number are both related between the episode and station table.

Not sure if that sheds any light on the structure of my DB.

[mr_vodka]

Station ID and Episode number are both related between the episode and station table.

Can you elaborate on this? How are they joined? What keys are you using exactly in your relationship.

Key fields should really be meaningless numbers. You probably should use a date field, version field, and a foreign key field for the station ID.

[LaRetta]

Then why do you use this episode number? Why do you split the data properly into different fields/tables and then put them back together so you have to deal with attempting to search on a conglomeration by breaking it into pieces again?

You may be normalized but you've placed everything BACK into one field ...

There are several normalization rules (or norms), but the one that applies here is the first normal form, and more specifically the part that's often expressed informally as the "one fact per field" rule.

You should be searching each field directly and not conglomerations.

LaRetta

[_Dan_]

Then why do you use this episode number? Why do you split the data properly into different fields/tables and then put them back together so you have to deal with attempting to search on a conglomeration by breaking it into pieces again?

You may be normalized but you've placed everything BACK into one field ...

You should be searching each field directly and not conglomerations.

LaRetta

I'm having a little trouble following, but I'll try my best here. It could that I'm not communicating this very well. This DB was built by someone else and I'm just trying to work with what I have.

The data is split so that we don't have to enter duplicate data. We have too many stations within our stations table to enter episode numbers for each. When we bring the data together we do so in order that we may create a label using a layout that brings the requested station and episode data together.

I really only need to directly search the episode number field within the episode table, but I need multiple episode numbers to appear within a script setting.

I appreciate all the help and I apologize for not following. Like I said, I'm newer to Filemaker and it's scripting processes so I'm just trying to get by with what I have and know.

[comment]

The problem here is that the episode number field is also the episode version field. It's possible to dance around that (for example as suggested by Tom), but the real cure is to split the two pieces of information into separate fields.

[_Dan_]

Ok I see, that makes sense now! So would I have to then have 3 different fields, one for each episode version? Otherwise in the episode number field, I'm going to end up having duplicate records. Every episode number is going to have all 3 versions. On our Stations table we have the individual stations broken up into the different versions. Is there any way to work with that potentially in the search? Just thinking out loud.

I have been able to create something using Tom's suggestion. So far it seems to do what I need it to do.

[comment]

I think ONE field to indicate the version (either A or D or T) should be quite sufficient. The episode number is not going to be unique, and neither will be the version. That's what EpisodeID (number, auto-enter serial number) is for. You should have such field in every table, and use only them for your core relationships. However, you can definitely use the version field to filter a relationship (or constrain a find).

Earlier, John asked how do you assign episodes to stations. This seems to be a many-to-many relationship that would require a join table. Perhaps it can be be simpler, but a lot depends on things we don't know, for example: does the description of an episode vary from version to version? Can stations get selected episodes, or is it always a package deal for the entire series? Are there multiple series, or do you have just one program with many episodes?