9 Replies Latest reply on Jul 24, 2013 10:22 AM by philmodjunk

    Help with creating a field



      Help with creating a field


           I could have 40 records in a particular database. Each of those records would have a name field and a number field. It is possible that a record's number field could be totally unique, for instance, it could have only a "1" in it's field. But it is also possible that 2, 3, 4 or more records could have the number field contain the number "1". The number field could also be a number and a letter ( 1A ).  I need another field, then, that would consider the number fields of the individual records, and if they are the same, combine those records (names) into one name field.

           In other words, if:

           Record1 =  (Name) John      (Number) = 1
           Record 2 = (Name) Jeff         (Number) = 1
           Record 3 = (Name) James   (Number) = 1
           Record 4 = (Name) Fred       (Number) = 1     then, there would be a field that combined the names John, Jeff, James, Fred.

           Record 5 =  (Name) Jack      (Number) = 2     then, the field would have only the name Jack in it.

           Record 6 = (Name) Harold   (Number) = 2A
           Record 7 = (Name) Jason    (Number) = 2A 
           Record 8 = (Name) Taylor    (Number) = 2A
           Record 9 = (Name) Chris     (Number) = 2A   then, there would be a field that combined the names Harold, Jason, Taylor, Chris

           If someone could help me figure this out, I would appreciate it.


        • 1. Re: Help with creating a field

               Hi Charlie,

               Create another table occurrence in your graph of this table (known as a self join).  Join the number field to itself using =

               Then in your table, create a calculation (result is text) with:

               Substitute ( List ( selfJoin::Name ) ; ¶ ; ", " )

          • 2. Re: Help with creating a field

                 This forum doesn't accomodate attachments and I'm currently not where I can post a sample (I'm on ipad only).  Here are very easy instructions:

                 1. Select File > Manage > Database and select the Relationships tab

                 2. Highlight your table then click the ++ (lower left) to duplicate it.

                 3. Select the number field in the first occurrence and drag it to the second occurrence number.  It will assume you want = which you do.

                 4. Name this second occurrence something like selfJoin

                 ... then create your calculation.  If you get stuck let us know and I can post an example in a few hours when I'm back home.

            • 3. Re: Help with creating a field

                   I fixed the issue of the 1s and the 1Bs being listed together by changing the number field to text. So, that seems to be working right now.

                   It seems the last thing that needs to happen now is to have 4 people listed as 1s, but then displaying them as two separate teams, instead of all 4 together. And, instead of displaying 4 records, there should only be 2 after I am finished.


                   Fred 1 
                   John 1 
                   James 1 
                   Harold 1

                   .....needs to end up being:

                   Fred, John     1

                   James, Harold      1

                   not, Fred, John, James, Harold     1

              • 4. Re: Help with creating a field

                     I believe that you are attempting to use a field value when a relationship is what is needed.  Consider:

                     If you have two people assigned to Team=1A and two people assigned to Team=1B, then a calculation of GetAsNumber (Team ) will combine them into the fourthsome.

                     If you had a table called Teams with auto-enter serial with 1, 2 , 3, 4 ...

                     Then your Players table where they are assigned to the Team with 1, 2, 3, or 4.  You can restrict to two players.  If relationally structured, this would be simple because you would just display your Players through the relationship on the Teams layout as I've suggested, using List() through the relationship.


                • 5. Re: Help with creating a field

                       From your data example

                       Fred 1 
                       John 1 
                       James 1 
                       Harold 1

                       How do you know which records to put on the same team?

                       And why use letters/numbers like this. If you are going to have to enter data to identify how the records should be grouped, why not use something more descriptive like some actual names?

                       And while a self join is definitely one option for this, another is to set up a table of groups and/or teams (you seem to have both here) with a primary key that you use to link individuals to that group or team table.

                       Also, please note that with each post, you are revealing additional details not previously stated. I suggest a more complete description of what you are trying to do so that those helping you do not waste time suggesting things that may fail simply because they don't have a complete picture of what you are trying to accomplish.

                  • 6. Re: Help with creating a field

                         The list of names is sorted prior to creating their groups, so when they are listed as shown above, the next step is to manually assign them their number. The numbers identify more than just their groups and they must remain as 1, 1A, 1B, 2, 2A, 2B, etc. The problem complicates ( at least in my mind ) because sometimes these groups are "groups" and sometimes they remain as individuals, but they are assigned to sit at the same table ( 1, 1A, 2, etc ).

                         I'm trying to create signs that indicate which table the individuals or groups are supposed to be sitting at. There could be 4 people sitting at the same table with two people on one side and two people on the other. For other tables, there could be only two people sitting at a table. There are only 2 chairs on each side of the table, that's why for some table numbers ( 1, 1A, 2, 2A, etc. ) there is only one group.


                    • 7. Re: Help with creating a field

                           Sorted by what criteria?

                           And why does this require such an abstract numbering system? (Don't want to argue the point, but if we know why, it can help us refine our suggestions.) Why both numbers and letters?

                           and this still does not explain how/why

                           Fred 1 
                           John 1 
                           James 1 
                           Harold 1

                           Would be divided into two teams of two individuals nor how you would pick which two make up a team.

                      • 8. Re: Help with creating a field

                             The sort criteria is irrelevant. The user looks at the records in front of them and adds Table numbers ( not tables as in FMPRO, but Tables as in sitting around eating on or playing cards, etc. )   The tables are labeled as Table 1, Table 1B, Table 2, Table 2B, Table 3, Table 3B, etc. This is a traditional practice that cannot be changed to something else.

                             Each person in the list is assigned to a sitting Table. There are two chairs on each side of the Table.

                             Fred        sits next to     John      on one side of Table 1.
                             James    sits next to     Harold  on the other side of Table 1.

                             They are all sitting at Table 1 and they need a sign that tells them which pair of them sit on which side of the Table.

                             The signs simply say      

                             Fred, John     1
                             James, Harold      1

                             As it is now, since Fred, John, James and Harold all have the number 1 assigned to them, the sign that is created says:

                             Fred, John, James, Harold    1, and there are four signs that would print. I need only one sign to print per group of two.


                        • 9. Re: Help with creating a field

                               Sorry, this is something that is likely very obvious to you, but it's not at all obvious to me.

                               Why would this produce:

                               Fred, John     1
                               James, Harold      1

                               and not

                               Fred, Harold     1
                               James, John      1


                               Fred, James      1
                               Harold, John      1

                               or ???


                                    This is a traditional practice that cannot be changed to something else.

                               I'm not suggesting that you should change this, but understanding the "traditional practice" might affect what implementation is most user friendly and most error resistant. What do the letters mean? Are all tables the same?