Well I've gone through a lot of the topics in the Find subforum and can't seem to find what I'm looking for.

My problem is in 2 parts. The first is the locating of the right records:

I come from an Access background so excuss some of my terms if they're wrong. I'm still trying to get my head around how things wrong on the "greener" side :

I have a layout for staff which is just simply their ID and their name. Under this I have a portal based on a Shift table (fields are Shift ID, client name, staff name, start etc)

What I would like to do is a staff member tells us what shifts they would like to do based on the shift ID. We then go to that staff member, type in the ID numbers they have given us and it will show the shifts.

The second part is a fast way of selecting these shifts. We need to add the staff members name under the staff field and I wanted to have a button that would complete all the shifts just found with their name.

I hope that's clear :

So the question is, how would I go about filtering all the shifts I need simply?

Then can any one point me in the direction to help me work out the script for the "select all" button to add in the staff members name.

Thanks.

Warning: I'm pretty fresh to filemaker so I'm still working out where scripts go and their syntax :(

Welcome to FM Forums!

Can more than one staff member be assigned the same Shift?

I image so. So, you need a join table btw Stf and Shifts, "Assignments". This table would have StfID, ShiftID, ClientID, StartDate, EndDate and other fields pertinent to this assignment.

On the Staff Form you would create a portal to the Assignment table, related by StaffID. By turning Allow Creation of Related records in the relationship, you can easily create Assignment records. The StaffID is populated as the child record is created. The ShiftID can be assigned a value list of Shifts based on the Shift table (ID and Desc). The ClientID would be a value list of Clients (ID Company).

hth,

Barbara

Edited October 31, 200916 yr by Guest

Thanks for the welcome Barbara.

To answer your question, no, 1 shift = 1 staff. Basically if a client orders 5 shifts, they need 5 people to fill those shifts (although 1 person can do more than 1 of those shifts). We are a temp agency, covering client's staff shortages.

I do have a similar structure set up as you've described though.

Client table,

staff table

shifts table.

How it works is when a client orders shifts we go to their form and enter in the shifts (linked via clientID). The staff field will be left blank since it has only been ordered.

Then a staff member tells us they'll do shift x,y,z (telling us the shift ID number). We go to the staff member form which has a portal showing the shifts. We type in the ID numbers and these shifts show in the portal. Then we click on a button and the staff members name gets filled in for all the shifts showing.

This is the ideal solution that I'm aiming for. Quick and easy.

If shifts aren't shared by more than one staff person, then you don't need a join table.

Here's a quick and dirty demo. hth, Barbara

PS: You could filter available shifts by Client, and/or show the Client Name in the Available Shifts portal.

Shifts.fp7.zip

WOW!!! Unbelievable. That is better than what I had in mind. Absolutely brilliant. Thanks heaps. I'll dabble around with it and add the fields we need.

I'm obviously still in Access mode because I'm still at a bit of a loss with some things you've done. I had a look at your relationships and just had to stare...nothing like I would have done.

I'm going to really have a look to try and understand how you've done things.

To tell you the truth, I was just about to put up a post about not needing a portal for the staff form but unsure how to go about things. This answers a lot (if not all) of the questions I had. Looking at a few things you've done, it's good to see I was along the right track.

Now I'll see what I can do (should be interesting). Thanks again Barbara.

You're welcome. Yes, it's nice to know where you'll end up. I learned FM taking apart demos such as this.

I've used the Anchor-Buoy methodology for the Relationship Graph. Also, my naming conventions are such that each primary key begins with __kP (that's two underscores, so that it sorts to the top) and each foreign key _kF.

Well I'm starting to slowly come to grips with things. I'm having to simplify my logic just like when I went from PC to Mac.

Anyway, Barbara, I have a question in which I've been trying to figure out for a while now. Hopefully you can remember how you put things together.

There is a field flag_IsAvailable_c which runs off an IsEmpty calc, If ( IsEmpty ( _kF_StaffID); 1; "") .

Now I've entered another staff member and instead of a number ID we have 5 character text ID's. I was wondering why available shifts weren't showing up and figured out that if the staffID was all text flag_IsAvailable_c would be left blank, but if I added in even just one number, it would = 1 like it should.

I've changed all the staffID fields to Text. I know I've missed something since I wouldn't think this would be an issue with IsEmpty.

Any ideas?

Thanks