Working with subfiles

[pvoc3000]

I developed a database for my costume designer girlfriend to use when she breaks down scripts. It worked beautifully for the last show she did, but its time for her to start breaking down another script, and currently the database doesn't handle multiple shows.

What I've had to resort to is making a copy of my database, then use a script to delete all the records in each table in the new file. Needless to say, this isn't very elegant.

I've thought about it, and the cleanest way I can imagine handling multiple shows is to use a separate file for each one. My main file would contain a link to the individual show files which would actually contain all the data relevant to that particular show. The main file would also contain all the scripts, logic, reports, etc.

For example, my main file would contain 1 table that somehow stored links to external files. My show files would contain multiple tables (Character, Scene, Costume, etc). All the layouts, scripts, reports would reside in the Main file. The show files would contain all the user changeable data.

I hope I'm explaining this clearly. Is something like this possible with FMP? It wouldn't appear to be, but I'm hoping that I'm missing something. Or maybe theres a more intelligent way to handle this. However, keeping all the data inside one huge file and having to do a find/sort everytime the data is manipulated seems like a workaround and not the best approach.

Thanks in advance!

 

[JeanW]

Which version of FileMaker do you use ?

 

[pvoc3000]

Oh yeah that would help wouldn't it... 8.5 advanced.

 

[JeanW]

Making a FileMaker application starts…..without FileMaker.

First look at your requirements and at what you want.

You will have ‘entities’ and ‘attributes’

An entity is an item and an attribute is every possible detail from the entity.

Make a list from all the possible entities (I don’t know much about what you call scripts, script is for me something in FileMaker). If you could be more specific about the requirements, that could help a lot:

From your post, I see one: shows

If you could make a list from everything you need to ‘make’ a show, we could step together trough that list to see which item can be an entity and which one an attribute.
At the end you will see what belongs where.

I would not go in the direction of a multifile application, stay in one file with multi tables.

At what is your final goal to have all those data in a database?
Reports ? Materiallog ? Peoplelog ?

HTH

 

[pvoc3000]

Thanks for the response JeanW.

The main table in my solution is Shows. Shows are made up of Characters (of which there are many per Show), Scenes (of which there are also many per show), and Costumes (of which there are many per Character). If each one of these elements is a table in my solution, and I relate them as follows:

Show -> Scenes (one to many relationship)
Show -> Characters (one to many relationship)
Characters -> Costumes (one to many relationship)
Scenes <-> Characters (many to many relationship - although to be honest these table are not directly related, except via an intermediary table called SceneChars)

The final goal of my solution is for what I guess you're calling a reportlog. I am able to see what characters are in each scene, and what they are wearing. Likewise, I can see what scenes each characters are in and what they wear in each scene. The end result are printed reports, however.

Like I said in my first post, this all works beautifully currently. What doesn't work, however, is switching shows. If I switch shows, all the data in the other three tables remains the same. I know that I can perform a find to only show data related to the current Show, but I imagine that I would have to do this after just about any action the user takes.

What I'd rather do is have the Show table in my main file, and then have Characters, Scenes, and Costumes in an external file. The main file would contain Filemaker scripts and report layouts I have made. The current Show record would store the location of the related file. When you switch Show records, the application should close the old related file, and then load the new one. That way, the only data the user ever sees is related to the current Show record and the application does not have to perform a find operation after every action the user makes.

Does this make it any more clear? I think now I'm even getting confused!

 

[JeanW]

>> I think now I'm even getting confused!

…and that’s a good place to start.
Looking at the requirements, I would go for a multi-table solution, no doubt.
This way you have everything in one place, while you can make every possible or needed combination.

>> Show -> Scenes (one to many relationship)
Show -> Characters (one to many relationship)
Characters -> Costumes (one to many relationship)
Scenes <-> Characters (many to many relationship - although to be honest these table are not directly related, except via an intermediary table called SceneChars)

Not bad at all…

Keep those entities, but in a slightly different way:

1 show is made up of many scenes, with many characters, who have many costumes.

Your entities are show, scene, character and costume.

The problem you have, and that’s why you’re thinking in the direction of multi-file (and delete records !! ) , is how to relate all those single items to make 1 show.
The ‘secret’ is keys and portals. Every single item needs an ID key.
With this key you can make every combination you like/need and show those in a portal.
With these keys you can make combinations to have yet other (combined) keys, which will result in yet other combinations…..

I don’t know your level in FM, but look first at the relationship structure and portal techniques.

With all your items (attributes) in tables, with valuelists based upon the Id’s and fieldvalues, you can put together every single show, with every single scene, with each character dressed in whatever costume you have in the solution.
Avantage will be that you can look back at shows, like who did what when where dressed in/as what. You will have your history and the user will look at every single show with all the details of that show.

Making a new show will end up in creating a new record and start making combinations based upon the valuelist from fields in the different tables.

 

[pvoc3000]

Thanks again JeanW

My only concern with keeping everything in 1 file is having to make sure that the user is only seeing the Scenes, Characters, Costumes, etc. that pertain to the current show. I can imagine the user performing a find, and getting Scenes from several different shows unless I explicity dealt with it myself by writing my own find routines. I can do this, but I think it would be cumbersome to code and to use and I would still fear that there would always be the possiblity of data from one show corrupting another show.

Another reason I would rather use multiple files is that I have a lot of value lists in my database, and for the life of me I can not figure out how to have these value lists only show data related to the current show. I either get every record, or no records. Do you have any tricks or tips with dealing with value lists in the kind of database I'm attempting to develop?

I like multiple files because when you switch shows, you no longer have immediate access to the old show's data. It doesn't effect value lists, you can hop around layouts for different views and not have to always double check the data being displayed pertains to the current show because it always is. Stuff like that.

However, I'm still unclear even if Filemaker Pro can handle multiple files in the manner I would like.

Again, thanks so much for taking the time to try and help. It is much appreciated!

 

[JeanW]

>>My only concern with keeping everything in 1 file is having to make sure that the user is only seeing the Scenes, Characters, Costumes, etc. that pertain to the current show.

Is a sound concern, you can use the build in restrictions from FM for this, where you can prevent access.

>>I can imagine the user performing a find, and getting Scenes from several different shows unless I explicity dealt with it myself by writing my own find routines. I can do this, but I think it would be cumbersome to code and to use and I would still fear that there would always be the possiblity of data from one show corrupting another show.

I don't think you have to worry about that. One Show will have all the items linked, based upon one key.
Even within the search routine, with a simple set field behind the screen, it will be possible to ony see the latest Show....
On the other hand, if you go for a multi file, how do you prevent a user to open the wrong file ?
Moving files around ? And how you will prevent for 100 % the access to those files ? Remove them ? Store them elswhere ? What about your history ?
Sooner or later you will need some information about a previous show, and then ?

Although, I have some solutions on multi file level and others complete on separations level, for your solutions I still believe a 1 file solution is sound and safe.

>>Another reason I would rather use multiple files is that I have a lot of value lists in my database, and for the life of me I can not figure out how to have these value lists only show data related to the current show. I either get every record, or no records. Do you have any tricks or tips with dealing with value lists in the kind of database I'm attempting to develop?

Yes, the 'secret' is hierarchical valuelists.
You can make it in such a way the valuelists will show only related values.

>>I like multiple files because when you switch shows, you no longer have immediate access to the old show's data. It doesn't effect value lists, you can hop around layouts for different views and not have to always double check the data being displayed pertains to the current show because it always is. Stuff like that.

The basic has to be a strong relational design.
Even if you go for multi file you will need even more relational power to steer everything.
Single file will give you less problems.
You still can hop around in a restricted environment, only linked to the actual show, or not, depend of the acces privileges, one for the solution, another for the user a 3th one for the developer and every possible mix of these.

>>However, I'm still unclear even if Filemaker Pro can handle multiple files in the manner I would like.

It can, we did it during all the yeas before the multi table.
But here I think you could use the full power of the new technique.
Of cause, it's up to you as developer and user...

 

[pvoc3000]

I've gone ahead and rewritten all my search routines to add a request for the current show. I've had to make things a bit more restrictive, which in the long run is probably better. Everything seems to be working alright except for one thing that I can not seem to get past...

Value Lists.

I have set up a value list that contains characters for the current show only. This works well. I can not, however, make a value list that displays the costumes for a particular character. It either displays all costumes, or no costumes (says No Values Defined).

I have a value list called "Costumes" that is set up to display 2 fields: Costume:CostumeID and Costumeescription. I have it set to include related records starting from Character. I have tried related records starting from Show as well. Neither one is returning any results.

I should mention that Character is related to Costume by 2 fields, CharacterID and ShowID. Both have to be equal for them to be related.

What's the trick here? I'm obviously doing something bone-headed here

 

[JeanW]

No trick...

You need a relationship to have your related Costumes.

'Include only related values starting from....' needs the name of the relationship where it can find the Costumes.
But not only the Costumes, only the Costumes related to a Character...
And it is this relationship that is probably missing.

If the record Character does not have a Costume ID linked to it, you will never find the Costumes for this given Character.
Assign a costume to each character, at record level.

 

[JeanW]

I don't know your structure nor the way you use the Costumes or every other item, but I think you could use the technique known as 'dwindling' list.

The technique is one which enables the presentation of a value list which reduces in size as items are selected on related records, showing only those items which remain available.

For you it could be that when you show your Costumes in a valuelist, link a costume to a Character, this value will not be available for every possible next record. No way to attribute one costume to two Characters at the same time...

If you combine this with a filtering of the values (like with a conditional valuelist), you can extend this to every way of working in your shows.

Just an idea, this technique requires an advanced knowledge of relationship.