13 Replies Latest reply on May 2, 2014 9:59 AM by philmodjunk

    Has client had any minutes of care from head caretaker?

    Imre...

      Title

      Has client had any minutes of care from head caretaker?

      Post

           I have two Tables: Clients and Caretakers.

            

      Clients has two fields:

             
      1.           Clients::PID and
      2.      
      3.           Clients::Head_Caretaker_time 

            

      Caretakers has 4 fields:

             
      1.           Caretakers::PID (which is the client's ID),
      2.      
      3.           Caretakers::Head_Caretaker_ID,
      4.      
      5.           Caretakers::Caretaker_ID,
      6.      
      7.           Caretakers::Caretaker_time

            

           The Caretakers-table holds information about which client received how many minutes of time from every caretaker. This can be many lines for 1 client.

           Each client has only one Head_Caretaker.

            

           What I would like to be able to do is: 

           let the field Clients::Head_Caretaker_time show the time, if any, has been spent bij the Head_Caretaker for this client. 

           The result should look like this:

           PID 1, Head_Caretaker_time = 05

           PID 2, Head_Caretaker_time = 25

           PID 3, Head_Caretaker_time = 0

            

           I other words: for each client show: if Head_Caretaker_ID=Caretaker_ID, then show value of Caretakers::Caretaker_time

            

           Any help? I've tried my first steps with Valuelists but no luck so far. 

            

      Schermafbeelding_2014-05-01_om_21.06.57.jpg

        • 1. Re: Has client had any minutes of care from head caretaker?
          Imre...
          /files/dbca5b0209/Schermafbeelding_2014-05-01_om_21.12.26.png 254x112
          • 2. Re: Has client had any minutes of care from head caretaker?
            philmodjunk

                 Option 1:

                 Assuming this relationship between your two tables:

                 clients::PID = caretakers::PID

                 Define a summary field in Caretakers that computes the total of Caretaker_time.

                 Put a one row portal to Caretakers on your Clients layout.

                 Specify this portal filter:

                 Caretakers::caretaker_id = Caretakers::head_caretaker_id

                 Put the summary field you defined in the row of this one row portal.

                 Option 2:

                 ExecuteSQL (in FileMaker 12 or newer) can be used to compute the same total in a calculation field in your Clients table

                 Option 3:

                 Using a calculation field in Clients and an added table occurrence of caretakers, a relationship could be used that matches only to records in Caretakers records that are for the head caretaker. This can be necessary in some cases as 1) above is a "display only" way to get this sub total.

            • 3. Re: Has client had any minutes of care from head caretaker?
              Imre...

                   Hi Phil,

                   option one works nicely, whowever it is as you mentioned display-only

                   option two: allas I have FMP pro Advanced v.11

                   option three: can you define the calculation and relation? I don't understand yet, but I'd really like to see this option work for me.

              • 4. Re: Has client had any minutes of care from head caretaker?
                philmodjunk

                     Instead of a calculation, have you considered defining Head_Caretaker_ID in Clients instead of Caretakers? That would seem a less redundant place to put this field and would make it possible to set up a relationship using:

                     Clients::Head_caretakerID = Caretakers 2::PID

                     What I was going to suggest originally is to define a calculation field in Clients with CareTakers::Head_caretaker_ID as the sole term in that calculation field.

                     Either way, by matching to Caretakers by the head_caretaker_Id, you can define a calculation field as:

                     Sum ( Caretakers 2::Caretaker_time )

                     to compute the total head caretaker time in a field.

                • 5. Re: Has client had any minutes of care from head caretaker?
                  Imre...

                       Hi Phil,

                        

                       Unfortunately putting Head_Caretake_ID in the table Clients is not possible.

                       It comes as a column in an export on which I have based the Table Caretakers. 

                       PID referts to client_id so matching Head_Caretker_ID will not do the job. (or am I wrong?)

                        

                       Can you please help me with option 3? I really need a not-only-display-field for the number of minutes spent by the head_caretaker per client.

                        

                       Tnx

                  • 6. Re: Has client had any minutes of care from head caretaker?
                    philmodjunk

                         I described option 3 in my last post. Set up a calculation field in the clients table that copies the value of CareTakers::Head_caretaker_ID.

                         

                              define a calculation field in Clients with CareTakers::Head_caretaker_ID as the sole term in that calculation field.

                         Then you can set up the relationship:

                         Clients::Head_caretakerID = Caretakers 2::PID

                         But Clients::Head_caretakerID is now an unstored calculation field.

                         by matching to Caretakers by the head_caretaker_Id, you can define a calculation field as:

                         Sum ( Caretakers 2::Caretaker_time )

                         PS. what you describe in terms of your data import does not make it impossible to define a field in Clients to store this ID field. A script could update such a field after importing the data.

                    • 7. Re: Has client had any minutes of care from head caretaker?
                      Imre...

                           Thx Phil,

                            

                           I am afraid there is a misunderstanding. I believe your suggestion, much appreciated, results in a total of spent time by ANY caretaker.

                           I would only like to see the time spent by the designated head_caretaker, per client.

                            

                           So the layout clients should look like:

                           PID 1: caretaker_time: 100

                           PID 2: caretaker_time: 200

                           PID 3: caretaker_time: 0

                           PID 4: caretaker_time: 400

                            

                           I'll post my entire configuration. I'm a making a mistake? Or was my bad-english putting you on a wrong path?

                            

                            

                      • 8. Re: Has client had any minutes of care from head caretaker?
                        philmodjunk

                             The calculation for the fk field must refer to CareTakers, not Caretakers 2. It's using the relationship to caretakers to get the value needed to match to records in Caretakers 2.

                        • 9. Re: Has client had any minutes of care from head caretaker?
                          Imre...

                               Hi Phil,

                                

                               I am sorry if I'm waisting your time here, I must be missing one of your steps.

                               So I THINK all steps you described are followed, but the spenttime by the head_caretaker depicts the total per client.

                               One more tip, please?

                          • 10. Re: Has client had any minutes of care from head caretaker?
                            philmodjunk

                                 You aren't wasting my time. I should have spotted the second error the first time around.

                                 it should be sum ( CareTakers 2::Caretaker_time).

                                 your calculation refers to CareTakers instead of CareTakers 2 and thus gets a total from all caretakers instead of just the head caretaker.

                            • 11. Re: Has client had any minutes of care from head caretaker?
                              Imre...

                                   Thank you Phil,

                                    

                                   but that results in unexpected empty-ness..

                              • 12. Re: Has client had any minutes of care from head caretaker?
                                Imre...

                                     Thanks Phil,

                                      

                                     I tried that also, but it result in unexpected empty-ness..?

                                • 13. Re: Has client had any minutes of care from head caretaker?
                                  philmodjunk

                                       Did you perhaps select number as the result type for fk_head_caretaker_id instead of text?