1 2 Previous Next 15 Replies Latest reply on Apr 24, 2015 2:16 PM by danlee

    Need help with counting field name

    danlee

      Title

      Need help with counting field name

      Post

      I have Table Trainers, Table Active and Table Clients.  Relationship is from Table Trainers (_pkTrainerID) and Table Active (_pkClientStatusID)  to Table Clients (_pkClientStatusID & _pkTrainerID).  Table Active has field name, ClientStatus (Active & Non Active)

      I would like to know how many Client Status Active are for each trainer.  So for example it should show me the active clients in my layout for each trainer like so:

      TrainerA - 30 Active

      TrainerB - 15 Active

      TrainerC - 22 Active

        • 1. Re: Need help with counting field name
          philmodjunk

          There may be other reasons for your active table, but just from the description here, it would seem a much simpler design to put a field in Clients with a field where you use a value list to designate that client record as "active" or "inactive".

          But using your current design, Count ( Active::_pkTrainierID ) , if defined in a calculation field in Trainers would give you the total number of active clients for each trainer.

          • 2. Re: Need help with counting field name
            danlee

            When I did what you suggested I get a 1 for all trainers.  I don't have _pkTrainerID in Active table so used _pkClientStatusID.

            What would the calculation field be for your first suggestion with the design being put into Clients table?

            • 3. Re: Need help with counting field name
              danlee

              So I set it up like you originally suggested and used this calculation field in Trainers table:

              Count ( Trainer; Active::Status = "Active" )

              Can't get it to work. :(

              • 4. Re: Need help with counting field name
                danlee

                Hoping for help on this...bumping

                • 5. Re: Need help with counting field name
                  philmodjunk

                  I meant count ( Active::_fkTrainerID )

                  count any field in active that is never empty.

                  Be sure to define this as a calculation field in trainers. Make sure to select the correct "context" in the drop down of Specify Calculation for this field.

                  Make sure that this is not an auto-enter calculation either.

                  • 6. Re: Need help with counting field name
                    danlee

                    I am so sorry but I do not know what I am doing wrong and it is frustrating the crap out of me.  I did what you said and create a calculation field in Trainer table, count ( Active::_fkTrainerID ) , and the results comes back as 0

                    "Make sure to select the correct "context" in the drop down of Specify Calculation for this field.

                    Make sure that this is not an auto-enter calculation either."  I am not sure what that means but when I do this as Count ( Clients::fkTrainerID) I get all of the clients that are active and non active so the count function works but no for just Active.  I know that I am making this more complex then it needs to be.  Sorry and appreciated your help.

                    • 7. Re: Need help with counting field name
                      philmodjunk

                      There are two ways that a field can show a calculated result short of a script setting the field to a calculated result. You can define the field as a field of type calculation and define the calculation. You can also open field options for a number, date, text, etc type field and use the field options tab to specify an auto-enter calculation. Don't do the latter.

                      What you describe sounds like your relationships are not actually set up as you described in your original post. Can you upload a screen shot of the relevant portion of Manage | Database | Relationships?

                      • 8. Re: Need help with counting field name
                        danlee

                        Here you go.  Thanks

                        • 9. Re: Need help with counting field name
                          danlee

                          I am also trying another method.  In Table Clients I created text field, ClientStatus.  Then I create a Value List (Active, Non Active)  for field ClientStatus.  In Table Trainer I create a calculation field, ActiveClients and use Count ( Clients::ClientStatus ) and it gives me total for Active and Non Active for each trainer.  Instead of just total of Active clients for each trainer. sad

                          • 10. Re: Need help with counting field name
                            philmodjunk

                            These are not the relationships that you described at the beginning of this post and that's why the suggested solution that I posted is not working.

                            Count ( client::ClientStatus ) should give you a count of all active and nonactive clients as there is nothing there in place to distinguish between active and non active clients.

                            Define a summary field in clients. Set it up as the "count of" ClientStatus (any field in client that is never empty will do).

                            Put a one row portal to clients on your trainers layout. Define a portal filter : Clients::ClientStatus = "Active". Put the summary field in this portal row and it will show the number of active clients for this trainer.

                            • 11. Re: Need help with counting field name
                              danlee

                              Thank you so much!!  That worked!  Just have one more quick question.  How do I add a total of the Active clients at the bottom?

                              Thank you for your help and patience!

                              • 12. Re: Need help with counting field name
                                philmodjunk

                                You can't with the method described as it is a "display only" method. You can't access the total shown in a calculation. you'd need to use a different method for counting your active clients in the first place.

                                Here's another method:

                                Define a calculation field in trainers: constActive. Put "Active" as the only expression in the calculation and select "text" as the result type.

                                Now set up this relationship:

                                Trainers------<Clients|Active

                                Trainers::__pkTrainerID = Clients|Active::_fkTrainerID AND
                                Trainers::constActive = Clients|Active::Status

                                (I didn't flip back to the previous "page" to see your actual field names so substitute your names for mine in the above example. Clients|Active is just a name for a new Tutorial: What are Table Occurrences? of the Clients table.

                                • 13. Re: Need help with counting field name
                                  danlee

                                  Thank you!  So created it exactly was you described.  Not sure what I am suppose to do next.  I have the portal with summary_ActiveClients field in there so it shows me the active clients for each trainer.  Not sure where I am suppose to put the other field to show the total active clients.  Would like that to be underneath the last trainer listed that sums up the total active clients.

                                  • 14. Re: Need help with counting field name
                                    philmodjunk

                                    Count ( Clients|active::_fkTrainerID )

                                    Defined in Trainers will give you the count.

                                    A summary field that totals this calculation field will give you the grand total.

                                    No portals are needed with this method.

                                    1 2 Previous Next