Something like this?
Case ( Staff::RateBartender > 0; "BAR" & "-" & Staff::RateTeamBar );
Case ( Staff::RateCapt > 0;"CAPT" & "-" & Staff::RateTeamCapt );
Case ( Staff::RateCulinary > 0; "CUL" & "-" & Staff::RateTeamCulinary);
Case ( Staff::RateLiaison> 0;"LIAI" & "-" & Staff::RateTeamLiaison );
Case ( Staff::RateMgr > 0; "MGR" & "-" & Staff::RateTeamMgr );
Case ( Staff::RatePromo> 0; "PROMO" & "-" & Staff::RateTeamPromo );
Case ( Staff::RateServer > 0; "SER" & "-" & Staff::RateTeamServer );
Case ( Staff::RateSpecialty > 0; "SPEC" & "-" & Staff::RateTeamSpecial )
); "¶"; ", " )
That worked perfectly! I can't believe I was making it so much harder on myself than it needed to be. Thank you so very much.
Thank you for your post!
It looks like hschlossberg has offered some great advice that has you started. I'll also move this thread from the FileMaker Community Feedback Space, which is specifically for input on the Community itself, to the Discussions Space where you should receive more views and potentially more feedback on this topic!
I suggest rethinking your basic data model (fields, records, tables, relationships).
Staff::RateBartender, RateTeamBar, Staff::RateCapt, Staff::RateTeamCulinary, Staff::RateTeamLiaison
this is data to enter in a single rate field of a related table rather than in multiple fields of the same record.
They are single fields. I'm trying to take what's on a staff layout/table and combine it for an availibility layout/table. We need to see at a glance all staff members availability and what their skills are so we can see if Jane is available Tuesday and is also a server, etc.
Which does not change my observation that these should be entered into records of a related table. The data can still be available "at a glance".
I'm sorry you've lost me. Do you mean create another table? The information exists on the staff table. I need to condense it for the availability table. If I create another table I'd still need to condense the information but then create a portal to view it? So I'm not sure what you are indicating I do.
I'd need to know a lot more about your data model and what you need to do before I could give a specific suggestion as to tables and relationships, but it would be highly unlikely that I would have all of those rates as different fields in the same record.
While I don't necessarily disagree with Phil, there's not necessarily a reason to change it up this time if what you have works for you. The point at which I might decide to change to what Phil suggests is at a point when I see that we are adding more rates or rate groups and it at that point makes sense to add them as related fields rather than adding yet more fields to my main table.
You original question re the calc, however, is going to be a problem regardless. Your calc example above isn't just missing some of the groups that have rates, but it also includes the CUL-A when the culinary amount is blank. Are you sure all rate amount fields are defined as number type fields? Is the calc field defined in the STAFF table and with its base table (above the calculation definition) also defined as STAFF?
Okay. I don't know why I'd combine them. We're a event staffing agency and employ staff with up to 8 individual skill types/rates. One staff member could be a bartender and a server and a manager with different rates for each skill type. There are also additional skill sets for each skill. Not every staff member is all 8 skills/rates (in fact I'm pretty sure no one staff member is all) and very rarely are they just one (ex. only a bartender). They also can be Team A bartender but a Team B server and a Team A culinary. So rates, teams, and individual skill sets are all different per skill/rate. It's important for the rates to be kept separate. Also, it's important for us to be able to see a list of current staff members and a list of their skill types and their team designation so we can place them easily on events. If an event needs 20 servers we have a table/layout called Availability that allows us to see all of our staff in a list searched by date, their availability notes for each day, their skill type and team designation. Rates aren't important but we need a concise way to see if they are a server, bartender, manager and if they are our A Team, B Team, etc. I don't know if this helped or made things more confusing for you.