add to field by entering another in a portal?

[cwcrogan]

Hello,

I apologize for the title - not very descriptive. I have a couple of questions, but first let explain what I'm trying to accomplish and what I've done thus far.

A company has 50 employees which work on different days. There are 20 positions that could be filled, but on an average day 10 to 12 are filled. Each employee could work at any position (and it changes frequently). What I'd like to do is track who works in what position for each shift (2 shifts per day).

So far, I have the following tables:

Employee

employee_ID

first_name

last_name

etc.

Positions

position_ID

position_desc

position_callsign

Shift

shift_ID

shift_date

etc.

Shift-Positions

shift_position_ID

shift_ID

position_ID

employee_ID

As you can see shift-positions is a join 3 way join table (this may be where my part of my problem lies).

My first question is this: does what I've done make sense (a 3 way join)? or because the positions are (for now) a finite number should they simply be fields in the shifts table?

My second question (assuming the 3 way is acceptable): is it possible to have a portal for each position (20 TOs) so that from shifts you could click into a portal and enter the employee, but have the position automatically entered? I'm guessing that eliminating the positions table and using fields my be the answer here, but I wanted your opinions.

Thanks

[comment]

The 3-way join table makes sense to me - but having 20 occurrences of it does not.

One possible way to handle this is to script the creation of a new shift, creating the necessary join records and populating the positions at the same time (this can be done rather simply by importing the Positions table records).

[cwcrogan]

comment, thanks for your input.

I'm not quite sure I follow you, however. If I were to script it and populate the positions wouldn't that create a record for each position in each shift? That's what I was trying to avoid because most of the time only 10-12 of the 20 positions are filled. So creating a record for each would be creating needless records. I could however create only 12 records each shift and populate those, but then I'm not sure how to approach adding additional records if needed.

I'm probably not explaining myself that well - I apologize.

What I was hoping to do was have a layout where the user could see (with labels, not necessarily the actual fields/records) all 20 positions and enter info into just the ones that were filled for that shift. Perhaps I'm looking at it incorrectly.

[comment]

most of the time only 10-12 of the 20 positions are filled. So creating a record for each would be creating needless records.

I see. Have a look at the demo posted here:

http://www.fmforums.com/forum/showpost.php?post/149069/

[cwcrogan]

I've been trying to wrap my head around that example and how it relates to what I'm trying to accomplish but I'm not having much luck.

Perhaps if I show what I have thus far:

And how I imagined the input layout:

Now positions 1,2,3,4,5,6,7,9,10, and 17 will always be filled. The others vary from day to day (11 and 12 are often filled, while 8, 13-16, and 18-20 are seldom filled). I'm trying to simplify this and for now record just the employee in each position. I was hoping there was a way to click on the button/field/portal next to the position label and enter the employee.

The way I have this mocked up would almost lend itself to having the positions as fields in the shift, but I was trying to eliminate all the empty positions. So, is it possible to click into the corresponding field and have the position auto filled and the end-user enter the employee?

Or am I approaching this wrong? How would you have the end-user enter the data?

edit// the positions do have logical groupings as portrayed in the layout.

Edited February 17, 2010 by Guest

[efen]

When you posted your original query about this problem

http://www.fmforums.com/forum/showtopic.php?tid/213171/post/349749/fromactivity/posts/#349749

I replied to it and, because I had an "idle" half hour with a copy of FileMaker 10, I started a very rough file of how I would approach your problem.

I attach that file (amended to add a globals table for a coloured display in the portals as it originally had conditional formatting on the portal rows) - which does not follow good practice guidelines. I don't know whether it will be of any help.

cwcrogan2.zip

[cwcrogan]

efen, I should have acknowledged and thanked you in the previous post. It was your comment that pushed me to the point I'm at now.

I'll have to dissect your file and see how to apply it.

Thanks again.

[efen]

There are probably many problems with the way I have structured the file - e.g. if I had to do this again I would probably not have a table of dates like this file.

I've just reread your post and as a suggestion you could "arrange" your portals so that the always filled positions are grouped together, and the same with the sometimes filled and the almost never filled ones.

See screenshot where there are 7 separate portals of one ... n rows starting on different portal rows.

[cwcrogan]

I think your 'dates' would equate to my 'shifts' and the main layout would be based on it. I agree that having the dates self join portal is probably not needed.

I think your first example would be just as easy for the end-users to choose person and position as the second. But you've strengthened my opinion that I may need multiple TOs for multiple portals on the main layout.

You see, ultimately, it's the end result (or layout) that is more important than the method of entering the data. I need the layout to resemble my mock-up above. A user needs to quickly see who is in which grouping (and the order in each is important as well). The only way I can see to accomplish this is to have a portal for each position and have it related through the shift_ID as well as to a global containing the position_ID.

That, or just simply stick with fields and use a value-list for easier entering. The reason I wanted to avoid this method is two-fold. I wanted to avoid all those empty fields (which probably doesn't matter in the long run) and I wanted to do a look-up for each persons radio number.

Now, if my thinking is correct (and I can easily be convinced it's not) using multiple portals and globals - is it possible to use a single repeating global and relate the position_ID to a single repetition of that global, or do I need a global for each relation? I've tried it with the repeating global and failed, but I may be overlooking something.

Again, all comments welcome.

[cwcrogan]

Okay, here's what I've come up with. It performs as I hoped, but I'm sure it's not the best way to do this. One thing I just noticed is that this method works even without corresponding records in the positions table.

I hope someone can take this apart and perhaps suggest a better way.

Shifts.fp7.zip

[cwcrogan]

comment, efen, and others:

I'm still working on this and appreciate your input.

comment, I've taken your words to heart and eliminated all but one TO of the join table. I now have a simple portal sorted by position. I will script the creation of the 10 minimum positions and any additional can be added simply in the portal.

So, basically I have it working as needed from the shifts point of view. Then I tried to take it another step and I'm looking for more input. What I would like is to view the data from the point of view of 'days' - each day has two shifts and a possible third special, or detail, shift. So I created another table 'days' with an ID pk and fk in 'shifts', and added a shift_type field in 'shifts'.

The problem I'm having is in displaying the data. I was hoping to use a 3 tabbed panel for each day to show the 3 types of shifts and the positions in each. I haven't figured out how to do this, though I have found a way to switch the shift that a single portal displays using a global in 'days' linked to 'shift_type' in 'shifts'. I just use a button to change the data in the global and the portal switches. So this somewhat accomplishes what I'm hoping for. Is there a more elegant way?

And finally, I need also to print all three shifts for each day on the same page, but grouped separately (not one portal). I'm having trouble creating the layout for this as well.

As always, thanks for your help

Edited March 5, 2010 by Guest