11 Replies Latest reply on Feb 20, 2017 7:07 PM by LeiReg

    Calculation Field and Case Function

    LeiReg

      Hello,

      I have to condense information into a list based on conditional information and I'm having issues with Case and List functions. I have 8 skills: Bartender, Captain, Culinary, Liaison, Manager, Promotional, Server, Specialty

      These skills all have a rate and Team (A, B, C, D, New, Promo) assigned to them.

      I need a calculation that identifies if the skills have an associated Rate and only if they have a rate then the result of "Abbreviated Skills-SkillTeam" is generated for all not empty rates..

      I tried using every combination of skills:

      Case (

      Staff::RateBartender  > 0  and Staff::RateCapt > 0 and Staff::RateCulinary  > 0 and Staff::RateLiaison > 0 and Staff::RateMgr  > 0 and Staff::RatePromo > 0 and Staff::RateServer  > 0 and Staff::RateSpecialty > 0;

      "BAR" & "-" & Staff::RateTeamBar & ", " & "CAPT" & "-" & Staff::RateTeamCapt & ", " & "CUL" & "-" & Staff::RateTeamCulinary & ", " & "LIAI" & "-" & Staff::RateTeamLiaison & ", " & "MGR" & "-" & Staff::RateTeamMgr & ", " & "PROMO" & "-" & Staff::RateTeamPromo & ", " & "SER" & "-" & Staff::RateTeamServer & ", " & "SPEC" & "-" & Staff::RateTeamSpecial;

       

      Staff::RateBartender  > 0  and Staff::RateCapt > 0 and Staff::RateCulinary  > 0 and Staff::RateMgr  > 0 and Staff::RateLiaison > 0 and Staff::RatePromo > 0 and Staff::RateServer  > 0;

      "BAR" & "-" & Staff::RateTeamBar & ", " & "CAPT" & "-" & Staff::RateTeamCapt & ", " & "CUL" & "-" & Staff::RateTeamCulinary & ", " & "LIAI" & "-" & Staff::RateTeamLiaison & ", " & "MGR" & "-" & Staff::RateTeamMgr & ", " & "PROMO" & "-" & Staff::RateTeamPromo & ", " & "SER" & "-" & Staff::RateTeamServer;

       

      ....

      But actually ran out of room, which I didn't know was possible. Is there a better way to do this? This worked fine but it was a lot of work and as I said I actually ran out of room in the calculation writing in FileMaker. Any help would be greatly appreciated.

        • 1. Re: Calculation Field and Case Function
          hschlossberg

          Something like this?

           

          substitute( List(

          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 )

          ); "¶"; ", " )

           

          Howard

          • 2. Re: Calculation Field and Case Function
            LeiReg

            Howard,

             

            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.

            • 3. Re: Calculation Field and Case Function
              LeiReg

              I thought it was working beautifully but I came across this record where their list should be much longer but isn't. Skills-Rate:

              Rate.JPG

               

              Result:

              Skills.JPG

              • 4. Re: Calculation Field and Case Function
                TSPigeon

                LeiReg:

                 

                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!

                 

                TSPigeon

                FileMaker, Inc.

                • 5. Re: Calculation Field and Case Function
                  philmodjunk

                  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.

                  • 6. Re: Calculation Field and Case Function
                    LeiReg

                    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.

                    • 7. Re: Calculation Field and Case Function
                      philmodjunk

                      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".

                      • 8. Re: Calculation Field and Case Function
                        LeiReg

                        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.

                        • 9. Re: Calculation Field and Case Function
                          philmodjunk

                          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.

                          • 10. Re: Calculation Field and Case Function
                            hschlossberg

                            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?

                            • 11. Re: Calculation Field and Case Function
                              LeiReg

                              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.