3 Replies Latest reply on Aug 9, 2014 10:01 PM by philmodjunk

    Calculation between child and adults in Contact Database.

    LuisCovarrubias

      Title

      Calculation between child and adults in Contact Database.

      Post

           Hello, I need some help on making a form like(http://i.imgur.com/wCpcHmc.png?1). I have a database which includes the names and addresses of all the people in my community. I have a separate table just for this form and is linked to the "contacts" table by their address.

           Each record in the neighborhood watch table has the address of each house. Which then shows the name of the people living there by using a portal. However, here is my problem. The form has two separate fields for Adults and Children. Each person has their own record but is not organized well because not everyone wanted to include their age. So they were given these options:

           Infant/Toddler

           PreK-Grade 2

           Grade 3-7

           Adolescent

           Adult

           Senior Citizen

           Special Needs

           What kind of fields would I have to make to separate the Adults and Children? We can assume that Adult and Senior Citizen are adults. Anything that isn't that is not. So I think I can use some if/else calculations but I'm still not sure. Any help would be appreciated.

        • 1. Re: Calculation between child and adults in Contact Database.
          philmodjunk

               There are several ways to selectively list the related records for your adults and children.

               A filtered portal could list them using a portal filter expression. One portal for adults, one for children, each with different filter expressions.

               contacts::age = "Adult" or contacts::age = "Senior Citizen"

               Would be the filter expression for the adults portal.

               Or you can use two table occurrences of contacts and add a extra match field that filters for one age group or another.

               You might define cAdultAges as:

               List ( "Adult" ; "Senior Citizen" )

               With Text as the result type. Then your relationship would be:

               NeighborhoodWatchForm::Address = Contacts|Adults::Address AND
               NeighborhoodWatchForm::cAdultAges = Contacts|Adults::Age

               Then List ( Contacts|Adults::FullName )

               will return a list of all adults with a return between each value.

               Substitute ( List ( Contacts|Adults::FullName ) ; ¶ ; ", " )

               Lists the same names but with commas instead of returns between each name.

          • 2. Re: Calculation between child and adults in Contact Database.
            LuisCovarrubias

                 Thank you Phil for the detailed response. I understood the filter portal idea and don't know why I didn't think of that before. 

                 What someone else suggested was have a calculation field to return 1 if option is either Adult or Senior Citizen and return 0 otherwise.

                 I then made two other calculation fields. One that only returns zero and the other only returns 1 and thats how I split up the portals.

                  

                 However, I'm a little confused about the 2nd option since I have not used Lists before. I'm more confused by what you mean by the Relationship. You mean going to Manage Database then relationship and linking those fields together? 

            • 3. Re: Calculation between child and adults in Contact Database.
              philmodjunk

                   The lists might better fit the layout of your form example--especially a list of names separated by commas which can then be a horizontal row of information.

                   You are welcome to ask follow up questions about that option if you want to try to get that to work for you.