I think I'd have designed it the other way aeround. I'd have a table with one record is:
Then you can filter a portal to that table by date (or date range), and performer name. You wouldn't have 300 fields for the 300 potential positions; you'd have one field with potentially 300 different values.
If I've understood what you've done correctly.
Thanks for getting back to me.
Yeah I did consider that option but I would like the end user to be able to start a new record for each show and enter the names by drop down Value list into the fields in layout view.
Below is a link to a pic of what the end document needs to look like (it is printed and distributed to cast) is there a way to easily relate the data in this layout to the setup you were speaking of?
I think you need the classic Shows ---< Shows Performer >--- Performers table set-up. This link shows a crude file that lets you create a new show, choose performers for two positions, and lets you see that links work to display a list of all the performers and their positions for any show (from the ShowPerformers table, and what shows any perfromer is in (from the same tbale, of from the performers table.) For example...
Sorry it has taken me a few days to get back to you.
I am trying that method now.
Is there a way to stream line the process however? I only say that as the Layout I am using will eventually have 400 or so independant fields, do I need to create both the Shows_Showsperformer and a PerformerPostion tables and link them individually for each field in the relationships menu???
I am up to about 35 so far and I am having trouble keeping track.
Thanks heaps for your advice and support
Hi Sorbster if your still out there.
I have a question about this setup if you'd be so kind as to answer it for me.
I'd like to be able to start a new show based on the day before so I was going to duplicate the record and then make changes in the Shows layout.
The only issue I have is that when I create a duplicate record all the field data that is set to display records from the related table disapears.
Is there any way to populate all of my fields with data from a previous record using this Layout method?
Sorry for the delay - I was thinking about the previous question and I was concerned in case I had become tunnel-visioned and sent you down a road that was not scaleable. Unfortunately I can't see any other way of doing it, although there's a niggle in the back of my mind that in fact there is; - 'wood for trees' and all that. Maybe someone else can come at it from a different angle.
The reason all your related records disappear is because the duplication will have changed the ShowID. If you over-type the ShowID number to be the same as the previous you'll see all the data return (not a good idea, obviously!). BTW you could also add the Show Date, anyway - my sample file was only to show you the principle.
If we come to the conclusion that this is the way to go then yes you can have a script that will run thorugh the appropriate ShowPerformer table's records and duplicate them with the next ShowID, and therefore display a true 'duplicate' Show ready for modification.
I only worry that I have given you a bum steer and I'd welcome other's thoughts before you create 400-odd fields and relationships. When I looked at it at first I expected an easier solution, but the more I studied it the more convinced I became that this is the way. I don't know how you manage to keep the records now...
Thanks heaps for your reply and no worries abotu the delay, it's just nce to know that there are people out there like you willing to help out a newby in getting started.
I have started working though the document now and I am learning more and more each day which is great. I am about a quarter way through the document now and while it is a little tedious I am happy to do the ground work to make the database work in the long run. I have added a show date column as well, which is a nice addition and now I am getting a little hooked on making the layouts look nice, I think filemaker and I will get along nicely.
would you be able to point me in the direction of what kind of script I would be looking at? I have a feeling it will be a little more advanced than I have delt with before, but i'll start looking at that now.
Thanks again for all your help, I really appreciate it.
Don't worry about the script - I'll hunt out one that Phil posted a few weeks ago. It's really neat. I'll get back to you this evening with that, but it won't hold you up.
Meantime, for my peace of mind, I'd really appreciate you taking a pause where you are now in the 400-odd field and relationship creation and really checking that the method I suggested will work for you in all the functions, searches, print-outs you want. If it does everything you want with 20 performers and locations it will do it with 1000. But if it is going to fail to provide a function you want, let's find that out now.
Thanks for the script, that'd be awesome.
I know what you mean I have started to test the situations I have at the moment with a set of about 50 related positions and it seems to do everything I have been asking it too.
The only tricky thing that I can see coming is eventually we would like to assign a value of dager for each position performed in a night.
So for example to dive in position 5 is worth 4 points what we want to then be able to do is see what percentage of danger any performer is achiving for any given show, I guess this means a calculation field for each show position field? or is that option all ready in use providing text as in From shows "position A"
Everything else seems to work very well at the moment
Create a field in Shows called DangerScore. If 'Position A' will always have a DangerScore of 4, then you could auto-enter '4' in the field, for example.
You can create a calculation field in the Performers Table, using the relationship to Shows:
DangerScoreAverage = Average ( performers_Shows::DangerScore )
You may want to refine that later to only have an average in a date range, for example, but try that first.
Thanks for that.
I see what you mean, only issue I seem to have with that is that my Position A field is already a Calculation field referning to from Shows_Scuba="position A" so it won't let me auto enter any other values.
If I change this to a text field then then the occurance of the field name in the Shows_performers is blank and no longer relate correctly
"If 'Position A' will always have a DangerScore of 4" - sorry, I meant if the Position A will always have a DangerScorePositionA associated with it, then you can set DangerScorePositionA to auto-enter '4'.
Thanks again I see what you mean, and I will have a look at how that implements now. Cheers
If possible do you think you could put up another link to your original table so I can download it again, it appears I did'nt save it correctly the first time and I'd love it for refernce purposes again.
Thanks again for your help.
This is the script:
You'll need to use a script to loop through the item records, duplicating each in turn. It is for an Invoice Header --< Invoice details lines set-up, as the Parent - Child. Yours is Shows and ShowPerformers, from memory. The Shows table here is called Invoices and the related table is LineItems.
Set Variable [$Invoice; Value Invoices::InvoiceID]
Go To Record/Request/Page [Previous]
If [not IsEmpty (LineItems::InvoiceID) ]
Go To Related Record [Show only related records; From table: LineItems; Using layout: "LineItems" (LineItems)]
Go To Record/Request/Page [First]
Set Field [LineItems::InvoiceID ; $InvoiceID]
Go To Record/Request/Page [First]
Exit Loop If [Get (FoundCount) = 0]
Go To Layout [original layout]
Go To Record/Request/Page [next]
Thr original link should still work - here it is. I hope it still works, as I've mucked about with the original here, testing other answers..