Show only one instance of particular field

[ckai]

Well that title wouldn't help much! haha

 

I'll try and explain this with exactly what I'm aiming to do.

 

I'm trying to create a layout for when I do our wages. The plan is to have 3 portals: 

 

1. staff_LIST: a list of all the staff members that have completed work for clients within the wage period

2. staff_SHIFTS: a list of all the work (shifts) the selected staff member (from staff_LIST) has done within the wage period

3. client_SHIFTS: a list of all the work done for the client within the period which is selected from the staff_SHIFTS portal.

 

All these portals would be based on a table called Shifts - it contains all the work done for each client. Kind of like a line items table for invoicing.

 

What I planned on doing was have 2 global fields in my global table gStaffID and gClientID. Both the SHIFTS portals will be based on this field. The fields get populated by a "set field" calculation when selecting the particular portal row.

 

This I'm cool with. I've done is throughout the database.

 

The problem is the staff_LIST portal. I can't work out, for the life of me, how to only show 1 instance of each staff member only. My idea was to filter this portal for the time period then work out a way to only show each staff member once.

 

I've tried all sorts. I've tried to work out a relationship to only show the unique records. I've tried filtering based on an unstored calculation that only shows the staff id if it hasn't appeared previously. This is maybe an issue since the calculation is reliant on the sort order.

 

I'm thinking a relationship is the way to go, but just can't get my head around it. I've searched a massive amount but still at a lose.

 

I'm more than likely over thinking things.

 

Any fresh ideas would be a great help. All criticism welcomed  

 

It's annoying because I'm basically trying to show all the information I need on one layout. Currently I have my staff list is on a pop up window, which opens the staff shifts in the main window and I have to leave the layout if I want to check all the client shifts.

 

I populate my staff list currently by doing a find on the shifts for the required period and use a subsummary layout.

[eos]

Add another TO of Staff "behind" Shifts --> client__SHIFTS, using the predicate shift_staffFK = staff_staffPK. Base your portal on that TO.

[ckai]

Add another TO of Staff "behind" Shifts --> client__SHIFTS, using the predicate shift_staffFK = staff_staffPK. Base your portal on that TO.

 

Thanks, although I've been looking at this and trying to work out exactly what you mean.

 

You're meaning the relationship would look like this:

 

staff --> shifts --> client_SHIFTS

 

?

 

My idea for the relationships was this:

 

globals --> staff_SHIFTS (shift table with predicate is gStaffID = _kstaffID) - takes care of the staff shifts

            --> client_SHIFTS (shift table with predicate is gClientID = _kclientID) - takes care of the client shifts

 

plus, of course whatever relationships to get the staff list showing.

 

The entire layout would be based on the global table. That was the idea at least. This is probably why I can't quite get my head around your suggestion.

[eos]

You're meaning the relationship would look like this:

 

staff --> shifts --> client_SHIFTS

 

?

 

Your nomenclature is giving me a headache,  but I'm pretty sure that wasn't is  … what I actually meant is along the lines of this example:

 

AnalyzeThis_eos.fp7.zip

 

which also shows how you can summarize data for the staff members for the date period (in case you didn't already know …) 

 

Hope this helps.

[ckai]

eos, on 25 Sept 2013 - 12:17 PM, said:

Your nomenclature is giving me a headache, but I'm pretty sure that wasn't is … what I actually meant is along the lines of this example:

AnalyzeThis_eos.fp7.zip

which also shows how you can summarize data for the staff members for the date period (in case you didn't already know …)

Hope this helps.

It gave me a headache as well! Thanks heaps for the example. I'm still digging around with it trying to figure out how it works, looking closly at the "Staff for filterered shifts" portal. That's exactly what I'm looking for my staff list but need it based on the "all shifts" portal as opposed to the "shifts filtered for client portal". So I'm digging around to see how you achieved it.

Regarding the date filtering, yip got that all ready to go just waiting for my brain to work... any day now haha.

I was going to do the date filtering from a portal filter but I realised that was stupid when I was applying the same criteria to 3 portals. Much better to use relationships for that.

EDIT: I think I've worked it out. I was wondering what the TO "control_Shifts_forStaffforShiftsbyClientAndDate" was for because I couldn't see how it related to filtering any of the portals. Then I noticed you used it to calculate the total hours worked. That's simple and clever. I was trying to group the shift results for the list as opposed to just using the staff table I already had and filter based on a relationship/portal filter.

Now to work out how best to incorporate the other factors into the the calculation such as shift cancelled date.

Would not have worked this out without your example. Very much appreciated.

[ckai]

Your nomenclature is giving me a headache,  but I'm pretty sure that wasn't is  … what I actually meant is along the lines of this example:

 

AnalyzeThis_eos.fp7.zip

 

which also shows how you can summarize data for the staff members for the date period (in case you didn't already know …) 

 

Hope this helps.

 

Maybe I was a little to hasty. The problem I have now is I can't get the total hours calculation in the staff table to incorporate two date fields - Cancelled and Transferred. Both of these fields need to be empty to be included in the wages but I can't seem to work out how to allow for empty values to be included in a relationship.

 

I tried creating a calculation that converted the empty values to 1 then including this in a relationship by using a constant one global field but that didn't work. The total hours calculation returns nothing.

 

I thinking I'm over thinking   

 

EDIT AGAIN: Yip I was over thinking it. Instead of trying to create a relationship with the Cancelled and Transferred date fields, I ended up creating another calculation in the Shifts table that showed the hrs figure only if Cancelled and Transferred was empty. I then just totalled this new field instead of the normal hrs field.

 

Easy once I stopped thinking