Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

AnsweredAssumed Answered

Multiple find with omits

Question asked by MickeyHoyle on Jan 29, 2012
Latest reply on Feb 1, 2012 by Sorbsbuster

Like • Show 0 Likes0
Comment • 14

Title

Post

Hi guys,

Im fairly new to FileMaker and am trying to write a simple tool for my boss to see who he can call when someon calls in sick for a certain shift.

I have two databases : one with all the staff including the different functions they're allowed withint the company (some can do a and b but not c etc) and one with all the shifts.

Lets say that John calls in sick for his shift on Wednesday with function A. My boss now wants to find people allowed on function A who are not working on Wednesday afternoon and who are obviously not John.

I came up with a layout with three fields

Name

Day

Function

Obvviously I need a script tha searches the records of the shifts table for people and omits the name entered, the day entered and includes the function entered.

The name and day field are drop down value lists, the function field I'm not sure about since the functions are entered in the staff table in a checkbox value list that doesn't relate to a table (I entered the values for the checkbox manually since I thought it stupid to make a table with just a couple of records for each function).

8 hope anyone can help me

Sorbsbuster Jan 29, 2012 7:15 AM

Correct Answer

You don't need a script. (With one caveat: you may need to trigger a refresh with a script triggere, as I have here, but that depends on the way your real tables relate.)

This sample file shows how to relate the staff table to itself and then use portal filtering to get the list you want. Think of the boss going to the sick staff record (to see their phone number, check what they do, mark them as sick, whatever): while they are looking at that record they can pick a day and the function they need covered.

You could make it that when they click on the staff member's record in the portal it takes them directly there (to get their phone number, etc). Or just show them the phone number directly there in the portal.

http://www.4shared.com/file/yKkNHEly/CallInSick.html

See the reply in context

No one else had this question

Outcomes

14 Replies

Sorbsbuster Jan 29, 2012 7:15 AM
Unmark Correct
Correct Answer
You don't need a script. (With one caveat: you may need to trigger a refresh with a script triggere, as I have here, but that depends on the way your real tables relate.)

This sample file shows how to relate the staff table to itself and then use portal filtering to get the list you want. Think of the boss going to the sick staff record (to see their phone number, check what they do, mark them as sick, whatever): while they are looking at that record they can pick a day and the function they need covered.

You could make it that when they click on the staff member's record in the portal it takes them directly there (to get their phone number, etc). Or just show them the phone number directly there in the portal.

http://www.4shared.com/file/yKkNHEly/CallInSick.html
Like • Show 0 Likes0
Actions
MickeyHoyle Jan 30, 2012 12:05 AM
Mark Correct
Correct Answer
Thank you very much, it looks like what I need and is more than my humble self could've asked for ( I did add a 'Not' to the last line of the portal filter, because I want to show available people, and people who already work are of course not available..) I do have another question for you though, purely out of curiosity: is my initial idea very bad, or is it possible? Since I'm trying to learn filemaker and the planning and designing tat come with it I'm curious as to why some things might work and others won't.

Mickey
Like • Show 0 Likes0
Actions
Sorbsbuster Jan 30, 2012 12:25 AM
Mark Correct
Correct Answer
You are perfectly correct, of course - if you change the field labels I have used to be 'Days Available' it is correct! - otherwise the filter would be changed to exclude them if the PatternCount > 0.

You idea would work, but it is always a better idea to use a calculation rather than a script if you can - much less to go wrong, interrupt it, etc, and less time-consuming (usually).

You can't place a portal-in-a-portal, but you can show info in the portal that is another relationship downstream of the portal's relationship. I didn;t really want to go into that in the example, because I'm not completely sure you need to have a separate table for functions (although the advice to posters is often the exact opposite - "You need to have another table!")

You can create a value list for funstions (as per the sample), and you will alwys be able to find any staff member by the functions they do. Whether you need more tables of not depends on whether you intend to keep a rolling shift roster, for example.
Like • Show 0 Likes0
Actions
MickeyHoyle Jan 30, 2012 12:45 AM
Mark Correct
Correct Answer
The 'use a calculation instead of a script' seems to me to be a very good new mantra, thanks!

Now I just have to learn how to think like a programmer so I can come up with (simple) things like this on my own.
Like • Show 0 Likes0
Actions
MickeyHoyle Jan 30, 2012 10:07 AM
Mark Correct
Correct Answer
Dear Sorbsbuster,

I did some more experimenting but it isn't working the way I want it to.

I built this file, because it suits my needs better that the one you supplied (as you can see, my staffshifts table is more complex than the days checkbox you put in the stafftable)

I wanted to add a new layout with the three dropdownmenu fields: name, day and location. I used the portal and tweaked your calculation a bit but it didn't work, partly because I wasn't quite sure what kind of relationship i had to set up to make the tableless layout work. The other problem is that two fields, namely name and day correspond (or actually, shouldn't correspond if you catch my drift) with records in the Shifts table and the field in which I mark the function has to correspond with the record in the staff table.

So, here is my initial setup for the staff table and the shifts table. Could you (or someone else for that matter) help me make the third layout?

http://www.4shared.com/file/CBjtDO02/Staff.html
Like • Show 0 Likes0
Actions
Sorbsbuster Jan 30, 2012 3:20 PM
Mark Correct
Correct Answer
Having looked at the table I am assuming that you will mark the person as working on 'Wednesday', as you made the day a text field (rather than showing them as working on the 1st Feb 2012, which is a Wednesday). I think this will lead to confusion, but no matter at this point.

"make the tableless layout work" - you can't have a 'Tableless' Layout', so I will show it on the Staff Layout anyway. I know what you mean - you want something to work without any direct reference to a table, but you still must hang it on something.

For the dropdown fields I assume you mean to show anyone who is not 'Jim', anyone who can do 'Tickets', and is not working on 'Wednesday'.

But try this (StaffPicker Layout):

http://www.4shared.com/file/VqXTF_AN/Staff_Vs2.html
Like • Show 0 Likes0
Actions
MickeyHoyle Jan 30, 2012 4:07 PM
Mark Correct
Correct Answer
Thank you!

What you built is exactly what i needed. I can't believe you really built this for me.

But! Could you maybe explain what you did with those relations? I get the portalfilters but would never have though of all those relations.

Thanks!
Like • Show 0 Likes0
Actions
Sorbsbuster Feb 1, 2012 1:54 AM
Mark Correct
Correct Answer
To be honest I was a bit thrown by your StaffShift table. From what you said about my original sample I thought you didn't want the record to simply show 'Working' (or 'Not working') on 'Wednesday', but you wanted lots of dates, so you could pick not working 'next Wednesday'. But when I looked at it I thought you had simply the same set-up as in my sample. Anyway, I had starting to modify your file to use the Day field as a date, then create another calculation field showing that as 'Wednesday', then using that to match the'sick day'. But then I realised that you are also only offering a pop-up-picker for 'Wednesday', not 8/2/2012 (which happens to be a Wednesday) as opposed to Wednesday 1/2/2012, so I would have had to get into much more serious modification, and I couldn't be sure that the way I would have done it would have been your intention.

Bottom-line excuse: don't worry about any relationships except those used to draw the portal, and to calculate the filters.

(And I think you had used 'Nickname' as a field in your relationships - always a bad idea. Too much chance of duplication, changing by the user etc. By all means show the field wherever you like, but only use things like Serial IDs that are automatically generated and controlled by FM. Think of the disaster when someone decides they no longer want to be known as 'Prince'. You will have to change every instance of every record in every table that previously had 'Prince' as the nickname. (And then you'll have to figure out how to insert a squiggle...) Or what happens when the 152nd member of staff is also called 'Jim'. Even worse than the disaster that is obvious from the first scenario, the second may well look as if it's working. Until too late...)
Like • Show 0 Likes0
Actions
MickeyHoyle Feb 1, 2012 1:55 AM
Mark Correct
Correct Answer
I read into TO's much more the last couple of days and I finally managed to figure out on my own what you did with those TO's (we'll still have to see if I can make up that stuff on my own :) ).

The reason why i just want wednesday and not a date is because I only have the standard shifts everyone works, which is the same every week. When and if I build a real staffplanner there's obviously going to be dates and in that case the setup you had in mind, where you fill in the real date someone is sick and it spits out the people not working on that date, is of course much more convenient (especially since sometimes people will be working on other days than their normal days...)

But all that in time (unless you wanna philosophize with me on how to achieve a staff planner that shows a layout with a week (and arrows to go to a next week) and all the staff. (if you want to, you can have a look at how my flat spreadsheet works now https://spreadsheets.google.com/pub?key=tnrgu_9GBOGlL2CqWyHJ7Qg&output=html (It's not english, hope you can understand)).

But anyway, thanks, you really helped me out a lot!

M
Like • Show 0 Likes0
Actions
Sorbsbuster Feb 1, 2012 2:06 AM
Mark Correct
Correct Answer
By the way, I beg you to learn and use the Anchor -Buoy method of organising TOs. Do it now - before you create even one more TO. You will never regret the 20 mins it takes to follow the logic. Search for those terms in the web, or start here:

http://www.kevinfrank.com/anchor-buoy.html

http://forums.filemaker.com/posts/1c39ff199a

As Phil says, "There is no wrong and right", but I would suggest, "The Anchor Buoy method is 'right' until you convince me you are using a better way. And just dropping TOs all over the place is not a better way."
Like • Show 0 Likes0
Actions
philmodjunk Feb 1, 2012 10:12 AM
Mark Correct
Correct Answer
Hmmm, never said that exactly. I do believe that each developer is responsible for their own design choices and the consequences that go with them so I don't keep fighting when a person insists on a design that is less than optimum, but there are many "wrong" options that either fail totally or tie your database design in knots...
Like • Show 0 Likes0
Actions
Sorbsbuster Feb 1, 2012 10:41 AM
Mark Correct
Correct Answer
Perhaps I paraphrased your comments from the linked post too much! "Be careful about "wrong vs. right" when dealing with things like anchor Buoy and naming conventions. These are more of a suggested style..."

I meant only to reinforce that the Anchor/Buoy is good, even very good (and is my preference) but I did not wish to be prescriptive that it is by any means the only way.
Like • Show 0 Likes0
Actions
MickeyHoyle Feb 1, 2012 1:04 PM
Mark Correct
Correct Answer
@ Sorbsbuster,

I've been doing tons of homework the last couple of days. I've analyzed the Staff vs 2 some more and completely understand what you did there and how you thought. There is just one thing that I don't understand: Why did you relate staff_SelfByConstant to STAFF via a constand (STAFF::StaffID). The TO staff_SelfByConstant is only used in the sick staff Layout, and in that layout the field StaffID is not used. I've made the relationship STAFF::gStaffID (Which should actually be STAFF::gStaffNickname, right?) x staff_SelfByConstant::StaffID and it works just fine. Or is there something I'm not seeing?

Thanks,

Mickey

PS. The posts about the Anchor Buoy are really helpfull.
Like • Show 0 Likes0
Actions
Sorbsbuster Feb 1, 2012 2:20 PM
Mark Correct
Correct Answer
Warning - from memory!:

- I was looking to relate the Table to itself to show me every record; then I was intending to limit the portal from 'every record' down to 'the ones I want' by using a portal filter. The relationship I used was a Cartesian - displayed as an 'X' - meaning there really is no limiting factor in the relationship: every record matches every record. You would be amazed how useful that is some times. So as every record matches every record, it doesn't matter what fields I picked on either side... they all work. I just kind-a grabbed the top of the list.

"Which should actually be STAFF::gStaffNickname, right?) x staff_SelfByConstant::StaffID" - with the Cartesian relationship, it doesn't matter.
Like • Show 0 Likes0
Actions

Multiple find with omits

Title

Post

Outcomes

Related Content

Recommended Content